MS Access no longer ablle to link to FoxPro tables

What version of Access are you using?

Version 2003 SP3

sorry, missed that.  So, are you saying that this process has been working, but now isn't?

Dale

Yes, it was working for years and suddenly I have the reported problem. No updates have been applied for months.

Could be corruption in the drivers, or troubles with the Windows/Office installation. I'd suggest that you fully update Windows/Office, and reinstall/repair the Foxpro driver that you're using.

Could you please look at the VFP ODBC driver version? It should be visible in ODBC administrator.

Before I posted my call for help I performed "repair" on the driver and on MS Office but the problem remained. The version of VFPODBC is 6.01.8629.01 dated 12/2/99.

Windows (XP Prof SP3) and MS Office (2003 SP3) have been routinely updated but support for both ceased. The reason this application is still in XP because I believe I can't port (FoxPro) into newer environments.

Both VFP ODBC driver and Access 2003 should work in Windows 7 but XP is still OK for this purpose.

The "reapir" action does not help obviously. You have to uninstall the driver and MS Access and then install again. Repair does not replace all necessary DLLs.

You may try to uninstall VFP ODBC driver and install it again from the original MSI (link is available e.g. here: http://fox.wikis.com/wc.dll?Wiki~VFPODBCDriver)

If it fails again then create an empty Access database and try to link FoxPro tables from this new empty database.

Also test the linked tables with firewall and antivirus disabled.

I am puzzled.  I installed the FoxPro application on a Win 7 Pro SP1 system (64bit) and the application is working fine BUT when I try to link tables into a Access 2010 mdb I get the same error. I installed VFPODBC.msi and created a data source using C:\Windows\SysWOW64\odbcad32.exe. I can select the created data source at “Machine Data Source”, however the link operation yields:
ODBC – call failed.
Specified driver could not be loaded due to system error 0: The operation completed successfully. (Microsoft Visual FoxPro Driver, C:\Windows\system32\vfpodbc.dll). (#160)

The “ODBC Data Source Administrator” doesn’t list the installed driver thus I couldn’t use it.

Yes, it is a puzzle...

I just don't understand one point:  You " created a data source using C:\Windows\SysWOW64\odbcad32.exe" but “ODBC Data Source Administrator” doesn’t list the installed driver?

What “ODBC Data Source Administrator” are you talking about?

VFP ODBC driver is visible under the 32 Administrator which is the one you've originally used (C:\Windows\SysWOW64\odbcad32.exe).  64 bit ODBC Administrator cannot see VFP ODBC driver.

Also installed MS Access 2010 must be 32 bit otherwise it cannot see 32 bit VFP ODBC driver which is installed in C:\Windows\sysWOW64\ folder under 64 bit Windows.

There should be no vfpodbc.dll in System32 folder as this folder contains just 64 bit DLLs under 64 bit Windows.

And what's more: MS Access 2010 can crash when you are selecting the VFP ODBC driver via UI. To write the connection string directly is better option if possible... The older Access is better for this purpose...

Simply said Microsoft does everything to kill DBF file format.

Access 2010 (part of MS Office Pro 2010) is 32-bit but Windows 7 is 64-bit.  I access the  “ODBC Data Source Administrator” via Control Panel – Administrative Tools and it seems to show only 64-bit drivers.
When I use odbcad32.exe I can see the 32-bit drivers that are also available at the XP & Access 2003 system.
vfpodbc.dll resides in Windows\SysWOW64 (12/7/1999 954 KB) and in
Windows\winsxs\x86_microsoft-windows-m..nents-mda-odbc-jet_31………..  (7/13/2009 21 KB)

So everything seems to be right version on the right place, just the Access does not work as expected...

Do you see the 32 bit DSNs in MS Access? I mean DSNs created in 32 bit ODBC Administrator (Windows\SysWOW64\odbcad32.exe).

Yes, I do.

I have two logs. This one is using system DSN FoxPro tables
SQL.LOG

This one is using user DSN I created
SQLMySource.LOG

This I can read from your logs:
1) Access handles the system DSN as 64 bit because it looks for C:\Windows\system32\vfpodbc.dll which is folder used for 64 bit DLLs exclusively. (I am just not sure whether this message isn't hardcoded...)

2) Access cannot find the user DSN. It could be caused by the fact Access bit depth is different from the DSN "bit depth". (DSN does not have any bit depth 32/64. It is just defined in a different part of Windows registry.)

I cannot say what is causing such a mismatch on your PC.  You could obtain more details if you look at the log produced by Process Monitor (https://technet.microsoft.com/en-us/library/bb896645.aspx). If you set the filter to vfpodbc.dll then it should say where it is looking for this file.

Thanks for the analyses of these two logs. I cannot (yet) understand why I get basically the same error in both environments (at XP there is no 32/64 business).  "(#160)" is contained in all error messages, do you know what it means? There is a log attached  at my initial post (from XP).

(160) seems to be a synonym for DIAG [IM003]... Who knows...

You could look into Windows Registry for more info about defined DSNs:

Look at the key HKLM - SOFTWARE - Wow6432Node - ODBC - ODBC.INI  (this key contains 32 bit machine DSNs)
How is the DSN for VFP defined? What driver/path is used in this definition?

If there is System32 folder used then you could change it by the correct SysWow64 folder.

This would have been too easy but was a good idea. I changed the corresponding registry entry to point to SysWOW64 but got the same error.  All drivers under ODBC are recorded in the registry with an system32 address!?

You are right - VFP ODBC driver has System32 folder recorded in registry (I've just installed it in W7/64) but some other drivers do have SysWow64, e.g. SQL Server Native Client 11.

I don't have MS Access available but I've tested ODBC connection to VFP tables in MS Excel 2010 w/o problems.

Could you please try to connect your DBF data in MS Excel 2010?

First, I very much appreciate your dedication trying to help me!
Yes, I can import into Excel. Steps taken were:
Blank worksheet, Data – Other sources, Microsoft Query, Visual FoxPro Tables yielding list of all tables in left panel. Selected one table, return data to Excel and “Voila” there are data.

So if MS Excel uses VFP ODBC driver without problems then you have to focus on MS Access... I am a little bit lost.

If the error appears when you are trying to define the Linked table then you may play with connection string or create the linked table in VBA code. One possible way is described here. Visual FoxPro connection string sample is e.g. here but you may also use connection string based on your DSN.

If the Linked table is defined successfully and the error appears when you are retrieving data then possible steps are:
- use Process Monitor to check what is happening
- use Dependency walker to check what could have been missing in your Windows installation (this is not necessary probably because MS Excel works with VFP ODBC)
- test linked table with a different data source and different ODBC driver
- reinstall MS Access
- reinstall Windows

You may also play with Access and VFP ODBC in some virtual PC environment etc. etc. Unfortunately, everything is time consuming.

Is there any possibility to port DBF data to e.g. SQL Server or to convert them into older DBF format and use the dbf driver attached to MS Office which reads dBase data?

I installed the Foxpro application on an old XP system, the ODBC driver, Access 2003 and all worked fine. Next I removed the driver and Office from the system I have the reported problem followed by an installation of Office 2003 and the driver, however I have the same error.  This makes me believe the problem is in XP. What could it be and more importantly what could I do to fix this.

Reinstall XP is not feasible since I have no CD (preinstalled system) and supported "recovery" would wipe out all data and applications. I keep trying to find the defect.

Nothing against your decision but this will really be long way run...

You may buy the XP installation media on eBay (http://www.ebay.com/bhp/windows-xp-professional-sp3) and use your installation key. Additional option is MSDN Operating systems.

I am glad to read the problem is solved!

And I have to say yes, the profile deletion would solve the problem because it deletes all files in the profile whereas the system recovery does not delete all files in the OS installation...

Just re-installation didn't correct the problem.