Re: Create DSN-Less link to Foxpro table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I'm afraid I have absolutely no experience using FoxPro, so I'm not sure I
can help. However, the .dbc extension looks suspect (I though FoxPro was
..dbf)

Looking at that KB article, the article says that SourceDB shoud be only a
folder, not a file.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Kevin" <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:65B6C2E6-E044-4EC0-9B46-E0C1735DBE10@xxxxxxxxxxxxxxxx
Douglas,

Thanks for the response. I tried modifying my connection string as you
suggested but still no luck. I also tried using the resolution shown here
http://support.microsoft.com/kb/285345/en-us but I don't know if I'm
going
about it the right way. I don't want to open the Foxpro database, just
link
to one of it's tables.

Here is the modified code.

Note that it fails on the line where I try to append to TableDefs -
db.TableDefs.Append td 'Fails here


Public Sub DSNLessFP()

Dim wk As DAO.Workspace
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim ConnectString As String

' Create an ODBCDirect workspace. Until you create
' Microsoft Jet workspace, the Microsoft Jet database
' engine will not be loaded into memory.
Set wk = DBEngine.CreateWorkspace("ODBCWorkspace", "admin", _
"", dbUseODBC)

Set db = CurrentDb

ConnectString = "ODBC;Driver={Microsoft Visual Foxpro
Driver};SourceDB=E:\admin.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;"

Set td = db.CreateTableDef("dept")
td.Connect = ConnectString
td.SourceTableName = "dept"
db.TableDefs.Append td 'Fails here

Set td = db.CreateTableDef("personne")
td.Connect = ConnectString
td.SourceTableName = "personne"

db.TableDefs.Append td

'Following code is required because no primary key is defined on
personne.
'Without the following code, personne is read only.
Dim strSQL As String
strSQL = "CREATE INDEX pk_emp_no ON personne(emp_no) WITH PRIMARY"

db.Execute strSQL, dbFailOnError

db.Close
wk.Close

End Sub

Kevin

"Douglas J. Steele" wrote:

While I haven't used Foxpro, every reference I've seen to DSN-less
connection strings to it have had { } around the driver name:

ConnectString = "ODBC;Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBC;SourceDB=E:\admin.dbc;Exclusive=No"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Kevin" <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EAB9A947-5003-45F5-AE0E-FB941284F3C6@xxxxxxxxxxxxxxxx
I'm so close but I just can't find exactly what I need. I'm trying to
create
a dsn-less connection to a Foxpro table (through the dbc). The
following
code
works great only it requires the DSN FPadmin to be setup. How would I
modify
this so that the DSN is not required on the user's computer?

Public Sub DSNFP()

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim ConnectString As String
Set db = CurrentDb

ConnectString =
"ODBC;DSN=FPadmin;SourceDB=E:\admin.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;"

Set td = db.CreateTableDef("personne")
td.Connect = ConnectString
td.SourceTableName = "personne"

db.TableDefs.Append td

'Following code is required because no primary key is defined on
personne.
'Without the following code, personne is read only.
Dim strSQL As String
strSQL = "CREATE INDEX pk_emp_no ON personne(emp_no) WITH PRIMARY"

Set dbs = DBEngine(0)(0)
dbs.Execute strSQL, dbFailOnError

End Sub

I have tried using changing up the connection string to something like
the
following.

ConnectString = "ODBC;Driver=Microsoft Visual FoxPro
Driver;SourceType=DBC;SourceDB=E:\admin.dbc;Exclusive=No"

But then I get an error -
Run-time error '3000';
Reserved error (-7778); there is no message for this error

That took me to this article
http://support.microsoft.com/kb/285345/en-us

But that shows opening the database. I want to create a link to the
table.

I can't believe I'm the only one to try this but I can't find anything.
Can
anyone offer a solution?

Thanks,

Kevin





.

Relevant Pages