Solved

Link FoxPro2.6 dbf into Access97 via odbc (so Acc2003 can use it)

Posted on 2007-03-29
20
763 Views
Last Modified: 2012-06-27
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\vfpodbc.dll dated 12/7/1999.  I've seen comments about using an OLE DB driver instead, but will Access97 (DAO) use that?)

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
0
Comment
Question by:TimDeaton
  • 10
  • 9
20 Comments
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
And we in here are not MS Access programmers or users in most cases.  And this communications link between other apps and FoxPro data is not something most of us are used to either as we work natively in FoxPro without the ODBC connections.

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.
0
 

Author Comment

by:TimDeaton
Comment Utility
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
0
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
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?
0
 

Author Comment

by:TimDeaton
Comment Utility
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

0
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
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.
0
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
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
0
 

Author Comment

by:TimDeaton
Comment Utility
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?

0
 

Author Comment

by:TimDeaton
Comment Utility
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?
0
 

Author Comment

by:TimDeaton
Comment Utility
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
0
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
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.
0
 

Author Comment

by:TimDeaton
Comment Utility
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.  
0
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
OK.
0
 

Author Comment

by:TimDeaton
Comment Utility
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
0
 

Author Comment

by:TimDeaton
Comment Utility
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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22493238.html
0
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
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
0
 
LVL 33

Accepted Solution

by:
CarlWarner earned 500 total points
Comment Utility
While this gives an example in Excel, I think it shows generally the steps that all/most Office need to go through to make an OLE DB Provider connection.  The key seems to be in choosing an Other/Advanced
selection when it asks for a Data Source and then you build an OLE DB Provider connection from there.  But, I don't know what Access 97 shows.

How to: Access Visual FoxPro Data in Microsoft Office
http://msdn.microsoft.com/library/en-us/dv_foxhelp9/html/39cc8d25-9d07-4ec4-8160-7e9ef85e8056.asp?frame=true
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
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
0
 

Author Comment

by:TimDeaton
Comment Utility
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
0
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
I hated it when companies I worked in got sold.  A new broom sweeps clean.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now