MS Access no longer ablle to link to FoxPro tables

Without to me apparent reason MS Access (2003) will no longer link to FoxPro tables. Obtain error:
ODBC call failed  Specified Driver could not be loaded due to system error 0 (Microsoft FoxPro VFP Driver (*.dbf) (#160). I am attaching log file. I know enough to be dangerous but obviously I am getting outdated.
I'll thank you for any answer.
SQL.LOG
gfkmpfrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
What version of Access are you using?
gfkmpfrAuthor Commented:
Version 2003 SP3
Dale FyeOwner, Developing Solutions LLCCommented:
sorry, missed that.  So, are you saying that this process has been working, but now isn't?

Dale
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gfkmpfrAuthor Commented:
Yes, it was working for years and suddenly I have the reported problem. No updates have been applied for months.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
pcelbaCommented:
Could you please look at the VFP ODBC driver version? It should be visible in ODBC administrator.
gfkmpfrAuthor Commented:
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.
gfkmpfrAuthor Commented:
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.
pcelbaCommented:
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.
gfkmpfrAuthor Commented:
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.
pcelbaCommented:
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.
gfkmpfrAuthor Commented:
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)
pcelbaCommented:
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).
gfkmpfrAuthor Commented:
Yes, I do.
gfkmpfrAuthor Commented:
I have two logs. This one is using system DSN FoxPro tables
SQL.LOG
gfkmpfrAuthor Commented:
This one is using user DSN I created
SQLMySource.LOG
pcelbaCommented:
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.
gfkmpfrAuthor Commented:
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).
pcelbaCommented:
(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.
gfkmpfrAuthor Commented:
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!?
pcelbaCommented:
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?
gfkmpfrAuthor Commented:
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.
pcelbaCommented:
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?
gfkmpfrAuthor Commented:
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.
pcelbaCommented:
Yes, some DLL was overwritten by incompatible version in your XP installation. The fastest fix seems to be XP reinstallation (not the repair). To compare all used DLLs and registry entries is always much more work.
gfkmpfrAuthor Commented:
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.
pcelbaCommented:
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.
gfkmpfrAuthor Commented:
The search for the fault was fruitless. I performed a system recovery that installed XP, followed by installing SP2 from a CD I possessed. Next I down loaded xpsp3_5512.080413.2113_usa_x86fre_spcd.iso and installed it bringing me close to the system I had (of course subsequent updates are missing since they are no longer available). However, the problem remained. Lastly I deleted my account (data files were left untouched by the system recovery) and created a new one which finally eliminated the reported problem.  In retrospect I am wondering if deleting my account prior to all this work would have solved the problem.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
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...
gfkmpfrAuthor Commented:
Just re-installation didn't correct the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.