FoxPro Connection String

[Guest]

Hi,

I'm using MS Access 2003 on an Access 2000 database file and the FoxPro are
"Free" tables. ie. no dbc file, only dbf files.

Problem:
Relinking FoxPro tables generates an Access error number: 3011, description:
"The Microsoft Jet database engine could not find the object [table
name]...". It works when I use the Linked Table Manager.

Details:
I'm trying to relink the FoxPro linked tables to different folders so I can
import the data in to Access tables. I've renames all the FoxPro linked
tables to have a sufix of zFp[table name] so they are all grouped together
and also because the Access tables have the same name. The error says it
can't find the renamed table. eg. zFpDept. So is there a way to tell the
connection string the FoxPro table/file name? I tried adding the string
";Table=[Real Table Name]" to the end of the connection string, but that
didn't work.

Example code:
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs

For Each Tdf In Tdfs 'Loop through the tables collection
If (Len(Tdf.SourceTableName) > 0) Then 'If the table source is other
than a base table
sTable = Tdf.name
If (StrComp(VBA.Left$(sTable, 3), "zFp", vbTextCompare) = 0) Then
Call SysCmd(acSysCmdSetStatus, "Relinking Table " & sTable &
"...")
DoEvents
sConnect = Tdf.Connect
bRelink = False

iPos1 = InStr(1, sConnect, "SourceDB=", vbTextCompare)
iPos2 = InStr(iPos1 + 9, sConnect, ";", vbTextCompare)
If (iPos2 = 0) Then
sPathOld = VBA.Mid$(sConnect, iPos1 + 9)
Else
sPathOld = VBA.Mid$(sConnect, iPos1 + 9, iPos2 - iPos1 -
9)
End If

If (Len(Dir(sPathOld, vbDirectory)) = 0) Then
bRelink = True
If (Len(Dir(sPathNew, vbDirectory)) = 0) Then
sPathNew = GetFolder()
If (Len(sPathNew) = 0) Then GoTo Exit_RelinkTables
End If
sConnect = VBA.Replace(sConnect, sPathOld, sPathNew)
End If

If bRelink Then
Tdf.Connect = sConnect 'Set the new source
Tdf.RefreshLink 'Refresh the link
DoEvents
End If
End If
End If
Next 'Goto next table

 

[Cindy Winegarden]

Hi Cheval,

Connection strings for FoxPro free tables do not include a table name, only
the directory where the tables reside.

I'm not that great with VBA but have you stepped through your code and
examined the values of sTable, sConnect, sPathOld, and sPathNew at each
line?

Just to be sure, the latest FoxPro and Visual FoxPro ODBC driver is
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates/odbc .