Link to home
Create AccountLog in
Avatar of João serras-pereira
João serras-pereiraFlag for Portugal

asked on

opening DBASE (.DBF) file on MS/Access 2007 and windows 7 (64 bit)

I need to open and update a few tables that are DBASE (Visualfoxpro).
I can directly open in EXCEL but cannot open (import or link) on MS/Access. I suppose that I needed to install a Visualfoxpro ODBC driver and I have downloaded one from the internet (microsoft site), but it won't install and the product is not supported.

I need to keep the DBASE files due to an old legacy software that I need to run.

Any clues to sort out the problem?
Avatar of Rincewindwizz
Rincewindwizz
Flag of Switzerland image

I suppose editing in VisualfoxPro is not an option?

Why not edit the files in Excel. Take a backup first but it should be fine (although changing the column widths is not recommended!)

If you have edited index field data you will have to re-index the database but hopefully your legacy app has the functionality to do that

But why cant you open the dbf file in access? I have just tried a dbf file with Access 2003. File->Open
Change the file type to .dbf
and it opens the database fine and then asks for index files.

Clearly you are not getting this so can you provide some more information
Good Luck


<<I can directly open in EXCEL but cannot open (import or link) on MS/Access. I suppose that I needed to install a Visualfoxpro ODBC driver and I have downloaded one from the internet (microsoft site), but it won't install and the product is not supported.>>

  Yes, as Microsoft removed the ISAM driver in Access for Foxpro:

You cannot connect directly to FoxPro databases in Access 2007 and in Access 2003
http://support.microsoft.com/kb/824264

<<I suppose that I needed to install a Visualfoxpro ODBC driver and I have downloaded one from the internet (microsoft site), but it won't install and the product is not supported.>>

  What's the issue you had?

JimD.
BTW, is this hte one you tried to install:

http://msdn.microsoft.com/en-us/vfoxpro/bb190233

JimD.
PS
Sorry I should have said.  Once you have updated it in access you will need to convert the mdb back to dbf. There are some shareware utilities to do this. For example, see http://www.topshareware.com/MDB-%28Access%29-to-DBF-download-45833.htm)

or
 if you want to do it in VB there is some promising looking code (using ADO) at
http://www.vbforums.com/archive/index.php/t-388973.html
Avatar of João serras-pereira

ASKER

Rincewindwizz::~
the issue on driver installation was an error code "Internal Error 2738" on the MSI installer. I suppose it's because I am having a 64 bit OS.

The installer I was trying to run was really the one you have pointed out. what puzzles me is that Excel perfectly opens the DBF files!

Doing a quick search, that error typically indicates that vbScript is not registered properly.

Won't hurt anything to try and register it.  Drop down to a command prompt (make sure you do a run as admin), and then do:

cd to C:\windows\syswow64
regsvr32 vbscript.dll

  See if that let's you run the install.

JimD.
dbf suffix does not necessarily mean it's a foxpro dbf. And dbase is not foxpro for sure. If it's a dbase dbf you'd need the dbase ODBC driver, if it's a clipper dbf, you'd need a clipper driver.

Bye Olaf.
The Visual FoxPro ODBC driver mentioned by Jdettman in answer ID:34473768 can be installed on W7 / 64 bit without problems (I just did it a few seconds ago).

You may also try Access 2007 ACE ODBC driver which is able to read dBase III, IV, and V files.

If your files are from Clipper or newer dBase then it could be a problem. Also accessing DBF files from 64 bit Office 2010 is problematic except dBase formats.

Excel does not use VFP ODBC driver when opening DBF files.
jdettman: I have run the CMD to register the DLL and it signaled be OK (registered).
Then tried again to run the installer and it did not run (same error - 2738) and BTW just after the error a new dialog message appears (same as yesterday) telling "Installation Ended Prematurely because of an error"

After reinstalling the jet engine as suggested by PCELBA the error is the same "External Table is not in the expected format". even as I tried the different available format options (DB III, IV and 5)

Could it be that the DBF tables are REALLY corrupted?
If you can open the DBF in excel it is most unlikely that the tables are corrupted.
if the table works in the legacy app, it is almost unthinkable that the tables are corrupted.
You can look at the tables in a text/hex editor if you wish and check the format.

If you want to upload a (section of a) table here (you may need to change the extension so that the file is accepted) I am very happy to have a look as well.
BTW

I have been told by the End User that I am really required to install the Visual FoxPro ODBC driver
So if he's right, the problem is to be able to install it. I have tryied to open a few other tables in the same application, but not the ones that I really need.

I can try to send the files that I can't open. Infortunately both exceed the 50 Mb max size (one is 59Mb and the other is 97Mb.
dBase files usually compress very small when you zip them. Use maximum compression on the smallest db and see what size of zip you get. I'd guess 5Mbish

ODBC driver: OK sorry to be pedantic but exactly what OS are you using? Win 7 64 or something else


PS
have you tried the 3rd party ODBC driver available at
http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=12&Product=14&Platform=6
It does most things up to VFP 9 except the V9 binary index.

Its a 32bit app but may do the trick on your 64 bit machine.
Hi

I am attaching the problem files. You are right. Zipping really compresses them.
I have changed the file extensions so I could upload them

My OS is Win 7 64 bit


MyProblemFiles.zip
Your files are correct. And they are compatible with old FoxPro 2.6. CUTS.DBF even has dBase III format but it contains Code Page mark. LIBRARY.DBF has memo file associated and this file is also correct (some memo fields contain just spaces but it is allowed).

So, the ACE.12 driver should read CUTS.DBF, LIBRARY.DBF requires VFP driver or conversion to dBase format which is easy but probably not a solution in your situation.
<<jdettman: I have run the CMD to register the DLL and it signaled be OK (registered).
Then tried again to run the installer and it did not run (same error - 2738) and BTW just after the error a new dialog message appears (same as yesterday) telling "Installation Ended Prematurely because of an error">>

 Sorry to hear that.  The vbScript.DLL not being properly registered is the usual culprit with that.

  Looks like your down to working with the tables via ADO/OLEDB as the KB article suggested.

JimD.
JDDettmen: can you please pont me to the article?
This one I posted earlier:

http://msdn.microsoft.com/en-us/vfoxpro/bb190233

which says:

"We strongly recommend using the Visual FoxPro OLE DB provider as a replacement. Please refer to the following article for more information and related links to issues when using the VFPODBC driver: http://support.microsoft.com/kb/277772."

That article towards the bottom has this:

"To download the most recent version of the OLE DB Provider for Microsoft Visual FoxPro, visit the following MSDN Web site:
http://msdn.microsoft.com/vfoxpro/downloads/updates (http://msdn2.microsoft.com/en-us/data/aa937695.aspx)

We strongly recommend that you use the OLE DB Provider for VFP to access VFP data. However, a stand-alone installation for the VFP ODBC driver is still available at the following Web site:

http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx (http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx) "


  That only leads to a general type page though.   Here's a better link on the OLEDB provider:

http://msdn.microsoft.com/en-us/library/0xzsac67(VS.71).aspx

  There's links at the bottom of that which will show you code samples, etc.

  What this means is that within Access, you won't be able to work with it as a linked table, but with a recordset.  That still however allows you to build queries on the tables, use forms, do reporting, etc.

JimD.
Thanks.

I think that we have reached a dead end: I can't anstal any ODBC driver for Visualfox pro on my machine.

as the Instller won't run. I was able to install it on a secondary machine using using ODBCAD32 on Syswao64. and link through Access.

However, I have found that it contains 2 tables. One of the tables is Ok and opens; on the other table, it won't (takes a lot of time and all fiedls and records give "#error".

I have browsed through the article but really I can't find a normal solution for this.
On ms/access I can't access the data so I am thinking on moving to .NET

ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Normal solution would be to reinstall the station which does not allow VFP ODBC driver installation. You don't know what everything is corrupted on it.
Ok. My problem is that I am not (anymor) a coder expert. Reading throgh the article I find that in VB I shoud use:

oConnection.Open("Provider=vfpoledb.1;Data Source=.\TasTrade.dbc")

(and shoud replace ".\trastrade.dbc" by ".\library.dbf" isn't it? and should put that command on a load form event or on a query (I do need to update the original tables as an open query macro or on a module). But then the tables won't appear in the ms/access table list and I do need to hard code all accesses to the tables?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ok. I'll try this way

thanks a lot

joao
Thanks for the help.
I am really recoomending the end user to drop legacy sw