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
gtrappAsked:
Who is Participating?
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.

Corey HabbasSecurityCommented:
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....
Corey HabbasSecurityCommented:
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
gtrappAuthor Commented:
How should I create the ODBC connection?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Corey HabbasSecurityCommented:
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...
jrbbldrCommented:
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

CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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.
Olaf DoschkeSoftware DeveloperCommented:
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.
pcelbaCommented:
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.
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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!
Olaf DoschkeSoftware DeveloperCommented:
I tried both in Access 2007 once, and ODBC was easier, but you would also need no code to use an OLEDB Connection instead of ODBC. Just search remote database access in the Access help and you'll be helped...

Before you start install the foxpro ODBC and OLEDB Provider from the links pcelba has given you. They're for data access from remote systems to DBFs. VFP apps don't need these drivers, they can work on DBF files natively, therefore you most probably don't find these drivers on a PC with a foxpro application having DBFs.

1. create a new empty access database
2. choose the third menu "external data", everything non Access inclluding Foxpro is external data.
3. besides Access, Excel, Sharepoint, Text, XML you'll find "further" or "more" (I have the german version, don't know how the original item name is).

That's where you'll find ODBC database, what follows is a wizard, importing will read in data to an access table, linking a table will leave the data in the DBF, but allows access to work withit, as if it was an access table (or database)

That's what you can do with ODBC and it's easier. Try that way first. Come back, if you have problems accessing the dbfs via ODBC.

Bye, Olaf.
IainMacbCommented:
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!
gtrappAuthor Commented:
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?
gtrappAuthor Commented:
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.
Olaf DoschkeSoftware DeveloperCommented:
What do you have? If you have a dbc you choose dbc, if you just have old foxpro dbfs, that's called free tables in VFP, so choose free table directory. If you choose one or the other wou'll be prompted for a dbc or a directory, you can't do nothing wrong and redo or start creating a new DSN, don't be shy, don't ask to much, it's two option, learning by doing is much faster...

Wonder what you mean by a .c file, this has absolutely nothing to do woth a foxpro database.

Simply test the DSN in the ODBC administrator, Access then should be able to use it. Maybe you actually have dbase or clipper dbfs, dbfs don't necessarily mean foxpro tables.

Bye, Olaf.
gtrappAuthor Commented:
Sorry, that is a DBC file, not a C file.
pcelbaCommented:
If VFP data use DBC (database container) the you have to specify path + DBC file name in ODBC setup. If your VFP data are stored in free tables (just .DBF file extensions in your data folder) the use Free Table directory and don't specify the file name in ODBC setup.

ODBC driver ver. 6 is OK if your VFP data do not contain autoincremented integer fields, varchar, varbinary, and Blob fileds.

gtrappAuthor Commented:
I have both. I have DBC and I have DBFs files.
gtrappAuthor Commented:
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?
pcelbaCommented:
The Access crash could be caused by insufficient access rights (logged-in user must be able to call ODBC driver files stored in Windows\System32\ folder) or by incomplete ODBC driver installation.

You should also read ODBC info in Vfpodbc.txt file.

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:
Data stored in a DBF file can be deleted logically or physically. Logical deletion means just a flag in each deleted row (Deletion mark). Logically deleted rows can be Recalled back to normal state. Physicall deletion (packing) means physical removal of rows containing the Deletion mark from the database table and as such this action is not recoverable if you don't have backup copy.

The ODBC driver contains the "Deleted" checkbox. Deleted records are not visible when this checkbox is checked. If you uncheck it then you may see logically deleted data and work with them as if they were not deleted.

gtrappAuthor Commented:
Thanks for the help.
Olaf DoschkeSoftware DeveloperCommented:
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.
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
FoxPro

From novice to tech pro — start learning today.