FoxPro ODBC driver = unreliable linked tables in Access

[sqleptical]

All of my reports macros queries & forms are built in Access97, but all of my tables are in FoxPro DBFs. Everything was fantastic when my applications were based on FoxPro 6, but a software "upgrade" required me to switch to FoxPro 7 & I can no longer link to my DBFs in Access the way I used to, I must now use the Free Table directory in the FoxPro ODBC driver. I understand the problem to be related to the fact that Microsoft has not released a new FoxPro ODBC driver since FoxPro 6. The problem I'm running into is that my reports are returning slightly different results every time I run them for the same date range. On my last test I had to run a report 8 times before I got two reports that matched exactly. The differences are small, but not acceptable as I need to run accurate reports from the DBFs hourly.

I purchased SQL Server 2000 in hopes that it would help join, but I arrived back at the same problem of having to use the FoxPro ODBC driver. I also purchased Access 2003 & found no problem

My knowledge of FoxPro is very limited (as is my knowledge of SQL Server). I have dozens of DBFs, but I've never used a DBC. I researched the forum archives & found info on "upsizing", Is that were my answer lies, or is there a simpler solution through SQL Server? The ODBC driver for SQL in Access has tested successfully for me, if I can get the DBFs to correctly link through SQL.

Any insight is appreciated!

 

[Mike Lewis]

I understand the problem to be related to the fact that Microsoft has not released a new FoxPro ODBC driver since FoxPro 6.

That is the root of your problem. If you created any of the tables (or modified their structure) in 7.0, the ODBC driver will no longer work properly.

The best solution is to use the Visual FoxPro OLE DB provider instead. This is completely up to date, and works reliably. I believe Acces is an OLE DB client, so you should have no problem using the provider to connect to DBF files.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TamarGranor]

Actually, Mike, isn't it only a problem if you use any of the new database features in VFP 7?

Tamar

 

[sqleptical]

Thank you for the information. I have installed the FoxPro OLE DB provider, but when I try to link to a DBF in Access (or SQL Server), I do not see any differences in options for which process the table will be linked through (the word OLE appears nowhere - not under "Files of type" or ODBC Databases). Is there an additional process I am missing? If anyone knows how to link to a DBF in Access 97 using the FoxPro OLE DB provider, I would love to learn.

 

[Mike Lewis]

Tamar,

isn't it only a problem if you use any of the new database features in VFP 7?

No you mention it, I think that's right. I must remember to check it sometime.

Sqleptical,

If anyone knows how to link to a DBF in Access 97 using the FoxPro OLE DB provider, I would love to learn.

I'm not an Access expert, but I assume you do it via ADO. If no-one else here can tell you, you could try asking in an Access-related forum (just ask how to do OLE DB in general; the question needn't be Foxpro-specific).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[sqleptical]

Thank you, I will try.

What about SQL Server 2000? Does anyone know how to link a Foxpro DBF in SQL Server using the OLE DB provider?

 

[Mike Lewis]

Sqleptical,

Does anyone know how to link a Foxpro DBF in SQL Server using the OLE DB provider?

You do it via linked servers. Read the Books On Line topic entitled "Distributed Queries and Distributed Transactions".

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)