How do I import from one database into another?

March 16, 2006 1:10 PM

How can I import selected fields from one database into another database?

I'm writing a tuition and billing management program for my high school, and they'd like to be able to import students from WinSchool (MS Access Database) and EZ-Care (Fox Pro Database), as well as a CSV file, into the database I'm designing for use with my program. The database I'm working with is MS Access 97, with a Visual Basic 6 front end. Ideally, I'd like to create a program or find a free utility I can bundle with the software that would let the user select column names from the source database and then insert all of the records into the target database, kind of like a mail-merge-type thing where you can "match fields". Some of the fields would have to be filled in with zeros and such automatically by the program.

Anyone have code examples? A freeware application? A way to do it in Access itself?

posted by fvox13 to computers & internet (6 comments total)

Access does this, most certainly for other Access databases and csv files.

Not sure about Fox Pro however.

Is the importing something to be done ongoing, or is it a one-time thing? If the former, then you may want to just write something into your VB front end.

If it's a one-time thing, do it in Access. You want the Get External Data command.
posted by SuperSquirrel at 1:23 PM on March 16, 2006

It's going to be monthly for the csv, yearly for the access stuff, and once for the FoxPro stuff. I'll investigate the Get External Data command further (thanks SuperSquirrel!), but if anyone has experience with FoxPro, or with integrating this into my code somehow, that would be great!
posted by fvox13 at 1:34 PM on March 16, 2006

I don't know about FoxPro, but the get external data seems to be the best bet, as SuperSquirrel suggests. You could even use that for your monthly and yearly stuff, as importing new data doesn't overwrite the old data, it just adds more records to the existing database.
posted by misanthropicsarah at 1:58 PM on March 16, 2006

Back into it. Copy the file to a new name, load it and delete the unwanted fields.
posted by KRS at 2:33 PM on March 16, 2006

Is it all on one machine? If you set them up as named datasources, you should be able to pass data back and forth through your VB app.
posted by yerfatma at 4:14 PM on March 16, 2006

Yerfatma, your method sounds interesting.... what is a named datasource?
posted by fvox13 at 8:56 PM on March 16, 2006