Link FoxPro2.6 dbf into Access97 via odbc (so Acc2003 can use it)
I have many Access97 apps that link to FoxPro 2.6 dbf files. I now have to make it so that Access 2003 can use these Access97 apps (without changing the file format).
(The boss bought a new laptop w/Vista. Office97 will not run on Vista, so he put Office2003 on it. The rest of the company is still on Office97.)
I tried opening existing apps in Access 2003. When I do anything that uses those links to FoxPro2.6 dbf's, I get the "Could not find installable ISAM" error message. So I'm trying to create a new "TestOdbc.mdb" Access97 app that links to the FoxPro2.6 dbf's via ODBC (in hopes that Access2003 will like that).
Each time I try, I go thru a sequence where I select the Visual Foxpro ODBC driver. I then get a "Configure Connection" box (with "Free Table directory" marked), where I browse to the drive & directory containing the FoxPro dbf's. Under an "Options" button, there are also checkboxes for "Null", "Deleted", and "Fetch data in background" that are marked by default, and one for "Exclusive" that is greyed out.
I've tried leaving them all checked, and tried unchecking "Deleted". Either way, when I get to the next step and select certain dbf tables to link to, I get an error message saying: "Invalid field definition 'DELETED()' in definition of index or relationship."
The two tables where this has happened so far are two of the FoxPro tables I need to access most frequently. They both have cdx indexes, and I've been accessing them every day for years using the old (ISAM?) method.
(The ODBC driver I'm using is "c"\Windows\system32\vfpod
Can someone tell me how to fix this? (I don't program in FoxPro. Someone else here does that. I'm the bookkeeper, & also work on Access97.)
Thanks,
Tim Deaton
(The boss bought a new laptop w/Vista. Office97 will not run on Vista, so he put Office2003 on it. The rest of the company is still on Office97.)
I tried opening existing apps in Access 2003. When I do anything that uses those links to FoxPro2.6 dbf's, I get the "Could not find installable ISAM" error message. So I'm trying to create a new "TestOdbc.mdb" Access97 app that links to the FoxPro2.6 dbf's via ODBC (in hopes that Access2003 will like that).
Each time I try, I go thru a sequence where I select the Visual Foxpro ODBC driver. I then get a "Configure Connection" box (with "Free Table directory" marked), where I browse to the drive & directory containing the FoxPro dbf's. Under an "Options" button, there are also checkboxes for "Null", "Deleted", and "Fetch data in background" that are marked by default, and one for "Exclusive" that is greyed out.
I've tried leaving them all checked, and tried unchecking "Deleted". Either way, when I get to the next step and select certain dbf tables to link to, I get an error message saying: "Invalid field definition 'DELETED()' in definition of index or relationship."
The two tables where this has happened so far are two of the FoxPro tables I need to access most frequently. They both have cdx indexes, and I've been accessing them every day for years using the old (ISAM?) method.
(The ODBC driver I'm using is "c"\Windows\system32\vfpod
Can someone tell me how to fix this? (I don't program in FoxPro. Someone else here does that. I'm the bookkeeper, & also work on Access97.)
Thanks,
Tim Deaton
ASKER
Thanks for answering. I know this is a weird animal, & am just hoping that someone can help. I marked both the Access & Foxpro areas when creating the question.
Your steps above are exactly what I did, except that I marked the "Free Tables" box, since I'm dealing with FoxPro dbf files. I tried to create ODBC links to six such files in my test Acc97 project. The first two attempts (large files that are used in many of my Access projects) failed (repeatedly) as detailed above. The other four (some large, some small; some with cdx indexes, some with no index file) succeeded.
This morning my FoxPro programmer reindexed two dbf's where the ODBC link failed yesterday, but they still fail anyway. They do both have cdx index files with several different indexes inside.
Just to be sure, I just tried it again, but selecting the "Visual FoxPro database (.DBC)" box and then trying to link to the cstinv.dbf file. It told me that the dbf file was "not a database".
Could there be something else my FoxPro programmer needs to look at? I'll try to get more info about the cdx files later today. (I've got to do something else now.)
Thanks,
Tim
Your steps above are exactly what I did, except that I marked the "Free Tables" box, since I'm dealing with FoxPro dbf files. I tried to create ODBC links to six such files in my test Acc97 project. The first two attempts (large files that are used in many of my Access projects) failed (repeatedly) as detailed above. The other four (some large, some small; some with cdx indexes, some with no index file) succeeded.
This morning my FoxPro programmer reindexed two dbf's where the ODBC link failed yesterday, but they still fail anyway. They do both have cdx index files with several different indexes inside.
Just to be sure, I just tried it again, but selecting the "Visual FoxPro database (.DBC)" box and then trying to link to the cstinv.dbf file. It told me that the dbf file was "not a database".
Could there be something else my FoxPro programmer needs to look at? I'll try to get more info about the cdx files later today. (I've got to do something else now.)
Thanks,
Tim
Let's be sure what you're using. You say they are FoxPro 2.6 dbfs. Are they really? Is that what your Fox programmer is using to re-index?
ASKER
Yes. All of our FoxPro work has been done & is still being maintained in FoxPro 2.6 for Windows. He's trying to convert to using 8.0, but his 2.6 code apparently won't work once the files have been used by 8.0 code. So none of the 8.0 code is live yet (& won't be until EVERYTHING changes to 8.0 code at once).
I hope that made sense.
-- Tim
I hope that made sense.
-- Tim
That is the reason I asked. If the dbf files were inadvertently (or purposely) massaged by VFP8, VFP8 could add extra data into the headers of the dbfs. It does this because VFP8 has support for newer features that an older version like 2.6 never anticipated. When that occurs, the connectivity software like ODBC may not be able to connect with newly changed dbfs. ODBC only officially supports data formats through VFP6. Once features are updated in VFP7 on through the current VFP9, you would be forced to go to the OLE DB Provider for VFP.
Just to be sure, these are the latest drivers for FoxPro:
Visual FoxPro ODBC Driver
http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
Microsoft OLE DB Provider for Visual FoxPro
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en
Visual FoxPro ODBC Driver
http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
Microsoft OLE DB Provider for Visual FoxPro
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en
ASKER
So that extra data in the dbf headers is why our 2.6 programs can't use the dbf once VFP8 has touched it. The end result for us is that in order for us to use VFP8 at all, we have to replace every 2.6 program at once. And that's scary, because those programs are the backbone of the software that runs this company.
Anyway. I downloaded the two drivers above & ran the install on both of them.
On the ODBC driver: Is that all I have to do for it to be in use? If so, and if the install program put it the same place the old one was (c:\windows\system32\) in this system (Win XP Pro), then it's the same version I already had. And after installing, I tried linking one of the dbf files it didn't like before, and it still didn't like it.
As for the OLE DB driver: Do you know if Access97 can use it? What do I need to do after running the driver's install program?
Anyway. I downloaded the two drivers above & ran the install on both of them.
On the ODBC driver: Is that all I have to do for it to be in use? If so, and if the install program put it the same place the old one was (c:\windows\system32\) in this system (Win XP Pro), then it's the same version I already had. And after installing, I tried linking one of the dbf files it didn't like before, and it still didn't like it.
As for the OLE DB driver: Do you know if Access97 can use it? What do I need to do after running the driver's install program?
ASKER
In my testing & poking around today, I've narrowed down the source of the problem with the two dbf's that the ODBC driver didn't like: I've discovered that the problem is in their cdx index files. At one point I renamed one dbf's related cdx file to hide it, and then the ODBC driver let me complete the link.
I eventually found out that both of the offending cdx's include an index on their dbf's 'Deleted' key. The Foxpro programmer doesn't remember why it's there, but it's apparently been there 'forever'. He took the 'DELETED' index out of one cdx, and then I was able to link to the related dbf via ODBC. Then he put the 'DELETED' index back in, and my link still seems to work. But when I tried copying the link to another Acc97 project, that failed with the same, original error message.
("Invalid field definition 'DELETED()' in definition of index or relationship.")
So I've either got to find another solution or get the FoxPro programmer to permanently remove those 'DELETED' indexes and hope nothing else breaks. Any wisdom is welcome.
Somewhere in my Googling, I read of someone who had apparently found & copied the FoxPro ISDN file from Access97 and pasted it into AccessXP, and the ISDN method then worked in AccessXP. Any ideas on how to find that file and where to put it?
I eventually found out that both of the offending cdx's include an index on their dbf's 'Deleted' key. The Foxpro programmer doesn't remember why it's there, but it's apparently been there 'forever'. He took the 'DELETED' index out of one cdx, and then I was able to link to the related dbf via ODBC. Then he put the 'DELETED' index back in, and my link still seems to work. But when I tried copying the link to another Acc97 project, that failed with the same, original error message.
("Invalid field definition 'DELETED()' in definition of index or relationship.")
So I've either got to find another solution or get the FoxPro programmer to permanently remove those 'DELETED' indexes and hope nothing else breaks. Any wisdom is welcome.
Somewhere in my Googling, I read of someone who had apparently found & copied the FoxPro ISDN file from Access97 and pasted it into AccessXP, and the ISDN method then worked in AccessXP. Any ideas on how to find that file and where to put it?
ASKER
Excuse that last paragraph. I was talking about the "ISAM" method (NOT the 'ISDN' method) of linking to Fox2.6 files. I've been staring at this stuff too long.
Thanks,
Tim
Thanks,
Tim
You might try using the OLE DB Provider for VFP to get to those free tables in FoxPro 2.6 format. If you find it works at all, it should work better and faster anyway from MS Access 2003.
How to bind Microsoft Access forms to ADO recordsets
http://support.microsoft.com/kb/281998/en-us
And again the link to the free one for Visual FoxPro all the way through version 9:
Microsoft OLE DB Provider for Visual FoxPro
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en
How to bind Microsoft Access forms to ADO recordsets
http://support.microsoft.com/kb/281998/en-us
And again the link to the free one for Visual FoxPro all the way through version 9:
Microsoft OLE DB Provider for Visual FoxPro
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en
As an added comment, if and when you use the OLE DB Provider for VFP in your setup, add a simple text file to the same folder where the file VFPOLEDB.DLL exists. The ASCII text file should be named config.fpw and should contain the following directive: TABLEVALIDATE = 0 . The OLE DB Provider will use the contents of that config.fpw file to change the default value for TABLEVALIDATE from 3, which is a default setting that is very restrictive and will definitely slow performance down. Having it reset to zero will avoid that check getting in the way with your FP 2.6 dbfs.
ASKER
Thanks for your help. I haven't forgotten, butI haven't been able to work on this since last Thursday due to other issues at work. (I'm logging on from home now.) First chance I get at work, I'll try to find where VFPOLEDB.DLL is, and see if I can get Acc97 to find it.
OK.
ASKER
I've found the VFPOLEDB.DLL file (in c:\Program Files\Common Files\System\OLE db\), but I can't find any way to make it visible to Acc97. I went to WinXP's |Control Panel|Administrative Tools|Data Sources (ODBC)|, and saw no way to navigate to the "OLE db" directory. I poked around in some other options in |Control Panel|Administrative Tools|, but didn't find anything that looked helpful. Then I went into Acc97. The "Link" process, under File Type "ODBC Databases", let me use the "Look In" box to navigate to the "OLE db" directory, but then it couldn't see the file.
So how do I tell WinXP about the driver or otherwise make it visible to Access?
Thanks,
Tim
So how do I tell WinXP about the driver or otherwise make it visible to Access?
Thanks,
Tim
ASKER
Since Access97 vs OLE DB might require someone more heavily into Access, I asked another question in the Access area about getting Acc97 to use OLE DB, so maybe someone there would see it. The link to it is here:
https://www.experts-exchange.com/questions/22493238/Can-Access97-use-OLE-DB.html
https://www.experts-exchange.com/questions/22493238/Can-Access97-use-OLE-DB.html
And, of course, Access 97 came out before the OLE DB Provider stuff was commonplace and back then ODBC was the norm. I may have sent you down a path where one technology is too far ahead of the other, although newer versions of Access support the use of the OLE DB Provider technology just fine.
Following the OLE DB Provider path, here is how you can see what is on your local PC
(and it's not a matter of DSN setup for ODBC that you may be used to-- it's now all like DSN-less ODBC where you simply provide a proper cnnection string to any OLE DB Provider you want to use):
To quickly get to a list of OLE DB Providers, right click on the Windows Desktop and choose...
Select New>Text Document
Change the name to LookatOLEDB.UDL (note the extension).
Tell Windows "HELL YES!" when it warns about changing the file extension.
Now, DblClick that puppy and check the Providers tab.
You can use this to make an OLE DB connection string that you can just read out of LookatOLEDB.UDL with NotePad, or to browse providers, etc.
Another method of doing the same thing, and a few more details on the UDL files, is at the following URL:
Creating and Configuring Universal Data Link (.udl) Files
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsintro7/html/vxtskCreatingConfiguringUniversalDataLinkFiles.asp
Following the OLE DB Provider path, here is how you can see what is on your local PC
(and it's not a matter of DSN setup for ODBC that you may be used to-- it's now all like DSN-less ODBC where you simply provide a proper cnnection string to any OLE DB Provider you want to use):
To quickly get to a list of OLE DB Providers, right click on the Windows Desktop and choose...
Select New>Text Document
Change the name to LookatOLEDB.UDL (note the extension).
Tell Windows "HELL YES!" when it warns about changing the file extension.
Now, DblClick that puppy and check the Providers tab.
You can use this to make an OLE DB connection string that you can just read out of LookatOLEDB.UDL with NotePad, or to browse providers, etc.
Another method of doing the same thing, and a few more details on the UDL files, is at the following URL:
Creating and Configuring Universal Data Link (.udl) Files
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsintro7/html/vxtskCreatingConfiguringUniversalDataLinkFiles.asp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just a quick note, similar to what I posted in Tim;s Q in the Access TA ...
You could force Access 97 to use OLEDB but in reality under the hood it will convert it through ODBC anyway so you don't gain anything going that route, and you are correct, any version of Access higher than 97 would work nicely with OLEDB.
While an interesting problem ... look at where it started and if you correct that issue all of this goes away until your scheduled upgrades are fully coordinated ...
<(The boss bought a new laptop w/Vista. Office97 will not run on Vista, so he put Office2003 on it. The rest of the company is still on Office97.) >
Steve
You could force Access 97 to use OLEDB but in reality under the hood it will convert it through ODBC anyway so you don't gain anything going that route, and you are correct, any version of Access higher than 97 would work nicely with OLEDB.
While an interesting problem ... look at where it started and if you correct that issue all of this goes away until your scheduled upgrades are fully coordinated ...
<(The boss bought a new laptop w/Vista. Office97 will not run on Vista, so he put Office2003 on it. The rest of the company is still on Office97.) >
Steve
ASKER
Once again, I'm sorry I haven't been able to get back to this. My company's been sold and my other responsibilities have taken ALL my time and then some. And it doesn't look like it's going to let up anytime soon.
Even so, it's not something that's going to go away, so I'll definitely be dealing with it again - probably sooner rather than later.
On the bright side, the two FoxPro tables that ODBC didn't like (because they had indexes on the 'Deleted' field) have apparently not suffered from the removal of those indexes. So maybe ODBC will work for me as long as my files have to remain in Access97 format.
Thank you for all of your help. I really do appreciate it.
- Tim
Even so, it's not something that's going to go away, so I'll definitely be dealing with it again - probably sooner rather than later.
On the bright side, the two FoxPro tables that ODBC didn't like (because they had indexes on the 'Deleted' field) have apparently not suffered from the removal of those indexes. So maybe ODBC will work for me as long as my files have to remain in Access97 format.
Thank you for all of your help. I really do appreciate it.
- Tim
I hated it when companies I worked in got sold. A new broom sweeps clean.
Try the following and see if you get any success:
Linking a Visual FoxPro Table
To link a Visual FoxPro table from a Microsoft Access database, follow these steps:
1. Open the sample database Northwind.mdb.
2. On the File menu, point to Get External Data, and then click Link Tables.
3. In the Link box, in the Files Of Type list box, select ODBC Databases().
4. In the Select Data Source box, on the File Data Source tab, select Visual FoxPro.
5. In the Configure Connection box, select Visual FoxPro database (.DBC), type or select the path to the file, and then click OK.
The link to the table is created.