Link to home
Create AccountLog in
Avatar of gtrapp
gtrapp

asked on

How to import a FoxPro Database into a MS Access 2007 Database

Hello,

Do you have a information on how to import data from a FoxPro database to a Access database?

Thanks,

Paul
Avatar of Corey Habbas
Corey Habbas
Flag of United States of America image

Hi,

You can open the MS Access Database, then link the foxpro tables into your MS Access database in a similar manner as you would if you were linking in tables from an external access database.

Then you may operate on them as you would a native table.

I hope this helps....
oops ... you could do this back when it was MS Access 2003, but now in MS Access 2007 there is another method.  I found this link to help you out...
http://support.microsoft.com/kb/824264
Avatar of gtrapp
gtrapp

ASKER

How should I create the ODBC connection?
I found an article from Microsoft on how to set the ODBC connection up.
http://msdn.microsoft.com/en-us/library/ca6axakh(v=vs.80).aspx


I hope this helps...
There are also commercial tools which I have not personally used, but I have tested a few.

If you do a Google search for  DBF to MDB  you will find some converters

One that I have partially tested was  DBConvert  
     http://dbconvert.com/products.php?pDB=1#access
It looked promising.

I guess the big question is -- Will this be a one-time effort or will this need to occur on a regular basis.  
If the first, then an not-too-expensive commercial tool might be worthwhile.
If the later, then you want to use the ODBC Connection method.

Good Luck

Avatar of Cyril Joudieh
If you have FoxPro, you can export the tables to TXT, CSV or various other formats in a single command.

COPY TO.

In older versions of Access you right click on the table and select Import I think and it will prompt you for dBase or FoxPro files as one of the options. I don't have it anymore since I have 2007 now.
I'd not go the way through TXT/CSV and use OLEDB instead of ODBC. Foxpro's ODBC has not been renewed since VFP6, if the database is of that version or earlier you can go the ODBC router, otherwise use the VFP OleDB Provider.

An MS Access Expert should be able to guide you to how to connect to an external database via an OleDB Provider.

You might have trouble with some data types and values, eg VFP supports empty date/datetime values, not NULL, but not a date either. General fields would also be an issue I can't tell you how to convert, it's kind of OleObject, but then not compatible to that Access field type.

Bye, Olaf.
I would start with VFP ODBC connection via linked table - it is easier than OLE DB data access because you just have to define a Linked table in Access. You may download the VFP ODBC driver here: http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx

If your FoxPro DBF file is of a newer version (means it uses autoincremental integer fields or varchar fields or varbinary data) then you have to use VFP OLE DB provider: http://www.microsoft.com/downloads/en/details.aspx?familyid=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

VFP OLE DB provider probably needs a short VBA code to import FoxPro data into Access but I am not sure about all possibilities.

BTW, I would rather export data from VFP to Access via Access ODBC driver.
It is very easy to manipulate Access or SQL data from FoxPro. I hope you have a development version of FoxPro. FoxPro is a software King!
SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I agree with JRBBLDR in  that what you do depends on whether this is a one-off or something that you (or someone else) will be doing on a regular basis.

If it is only a one-off, then CaptainCyril's suggestion  about TXT/CSV output is going to be by far the simplest.

If it's a regular job, then again I'd agree with CaptainCyril about the superiority of VFP over Access. Do the whole job in VFP and don't bother with the conversion!
Avatar of gtrapp

ASKER

For creating the Data Source, would I select the Microsoft FoxPro VFP Driver (*.DBF)?

Would I select the Visual FoxPro database (.DBC) or the Free Table Directory?

If I select the Visual FoxPro database (.DBC), would include in the path the DBC file?
Avatar of gtrapp

ASKER

Note: I am working with Access 2007 SP2 Version 12. This is a one time thing. I have a MS Visual FoxPro 9.0 SP2. The FoxPro driver is 6.01.8629.1.

When I try to import the data, Access crashes. I attempted to import the data via ODBC database that is using the *.C file. In Access, I select external data, more, ODBC Database, import the source to a table, select the datasource, and Access throws an error. No particular error message: "Microsoft Office Access has encountered a problem and needs to close." There is an option to repair the database, but this has no affect. Then, Access closes.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of gtrapp

ASKER

Sorry, that is a DBC file, not a C file.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of gtrapp

ASKER

I have both. I have DBC and I have DBFs files.
Avatar of gtrapp

ASKER

OK, I have tried various combinations of paths, selections and such and having some success.

I heard something about "packing". Records may have been deleted, but may not show up. If unpacked, then they show. How do I do this, if needed?
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of gtrapp

ASKER

Thanks for the help.
A DBC is not self contained, surely you always also have DBFs.

You wouldn't typically want to see deleted records, so you better stay off the option to query deleted records. You won't recognize them as being deleted and they also surely don't cause access to crash.

In the ODBC administrator click on the [Options>>] Button to see all the options you have. Again - don't be shy or helpless, take a look around. You can check Deleted in the extended options to also retrieve deleted records, but it will just be, as if there are no deleted records and even issuing a DELETE sql statement will then not make a difference for ODBC, you'd be able to delete records but ODBC would still see them until a PACK of the table. You may only want to do that to retrieve the maximum of data out of the DBFs, but as in each normal database, you normaly delete records to delete them and not want them back, for that there is backups.

There's a simple reason, records are only marked deleted in DBFs: The whole file after a deleted record would need to move, if you really wanted to physically delete that space of the record. And that would mean averagely moving half the table on disk, just to kill one record. I'd recommend to stay with the defaults.

Bye, Olaf.