How to import denormalized CSV into relational form

[freeskier]

How can I import a denormalized CSV file into Access and maintain relational integrity?

for example:

csv(fname, lname, workphone, fax)

person(personid , fname, lname)
workerinfo(pers onid, workphone, fax)

I have tried using a simple INSERT INTO but this does not keep the foreign key in workerinfo.

I would like to do this within VBA so that I can make it a macro because I will be running it often


Thanks for your help!

[PianoMan64]

How can I import a denormalized CSV file into Access and maintain relational integrity?

for example:

csv(fname, lname, workphone, fax)

person(personid , fname, lname)
workerinfo(pers onid, workphone, fax)

I have tried using a simple INSERT INTO but this does not keep the foreign key in workerinfo.

I would like to do this within VBA so that I can make it a macro because I will be running it often


Thanks for your help!

One of the biggest things you're going to have to get over is, the relationships between the different tables. Is there a reason that information has to be exported to different tables and then imported into access?

In response to your question, I had to move over a large MS Access database over into oracle. Had the same issues with keeping the references to each record, but since the numbering schemes on each platform was different, and I couldn't start or do it the same way, I was forced to create a new numbering system and pick a number that was well passed the old number scheme used in MS Access (Random) and the new scheme that was used in Oracle (Sequencal). So I was able to turn off numbering in the tables and then import the data, then turn it back on. and that solved the problem.

In your case, since that doesn't seem to be the case at all, not sure why you're having to export data from one system to another. What is the other system that you're getting the data from?

Is there a connector for it? ODBC or something along that line?

If you're not going to be doing any of your adding of records to the MS Access database, then turn off auto numbering on the tables. Let the system that you're importing the data from take care of numbering the information for you.

I hope all this help,

If you need further assistance, please respond back here.

Joe P.