Linking to FoxPro Free Tables from Access 2000 1

[AngelaD]

I am about to upgrade from Access 97 to Access 2000, and am therefore forced to use ODBC to link to my FoxPro Free Tables. I seem to be running into problems.

I create a link to a Free table in a particular directory using an ODBC datasource which specifies the Microsoft Visual FoxPro driver (but which does not have a directory specified in its configuration). From now on, if I use this datasource, Access assumes that I want to use the same directory. It therefore comes up with error 3011 (table not found) if I wish to access a Free Table in a different directory. If I close the database down, this assumption remains. It only disappears when I close Access down.

Note that while my Access database is open, I may wish to link to any number of FoxPro Tables in any number of directories. It will therefore not be practical to create a datasource for each directory/table using control panel.

Can anyone help?

 

[AngelaD]

If anyone is interested, I eventually found the answer myself.

If linking to FoxPro tables in the Jet Workspace, you need to use a different DSN (Data Source Name) for each directory in which FoxPro tables reside. This is because ODBC thinks of a directory as a FoxPro database.

However, this is only a partial solution, because I do not know in advance how many directories I may need to reference, and I do not wish to litter my PC with DSNs. The full solution is to create an ODBC Workspace, and to create one connection per directory within this workspace. I could then open tables directly instead of linking to them. In this way, only one DSN needs to be used. Unfortunately, this is still not ideal because you cannot use methods such as .FindFirst and .FindNext on tables accessed in this way.

I am still working on alternatives (see my Thread posted on 16 April).

Angela