Create DSN-Less link to Foxpro table

[Kevin]

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

 

[Douglas J. Steele]

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"

 

[Kevin]

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]

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.

 

[Cindy Winegarden]

Hi Doug,

Today you get to learn something new.

FoxPro tables are DBFs, with CDX (index) and FPT (contents of memo fields)
being optional. They can either be in the older dBase IV/FoxPro 2.6 format
or in the newer Visual FoxPro format.

Visual FoxPro tables can optionally be part of a "Database Container" or
DBC, which houses stored procedures, triggers, and other metadata. If the
DBFs are not associated with a DBC file they are called "free" tables.

When accessing FoxPro free tables, either via ODBC or OLE DB, the connection
string should point to the directory where the DBFs reside. If the tables
are part of a database container then the connection string should point to
the DBC. Of course it's possible that free tables and a DBC reside in the
same directory; in that case you would need two connections.

Finally, new features were added in VFP7 and later that are not compatible
with ODBC; tables with these features can only be accessed via OLE DB.
However, tables created with VFP7+ that don't have any of the new features
remain ODBC compatible.

 

[Cindy Winegarden]

Hi Kevin,

If you link directly (using the dBase IV option) then its a FoxPro 2.x
table. You can also use the FoxPro and Visual FoxPro ODBC driver since it's
better with Memo fields. You can verify that the table is ODBC compatible by
trying to link to it via File > Get External Data > Link Tables > ODBC
Databases > and set up a DSN on your machine using the FoxPro and Visual
FoxPro ODBC driver. (Just in case you (or anyone else reading later) need
it, the latest version is downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates/odbc.)

I'm sorry I can't help much with VBA code for a DSN-less connection.

 

[Douglas J. Steele]

Thanks, Cindy. Does this mean I'm going to have to wear a Foxpro shirt in
March? <g>

 

[Douglas J. Steele]

Cindy seems to be agreeing with my observation that you should only have the
folder for SourceDb.

In other words, try:

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

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