Import Excel- append to Access Related Tables

[kjyoung]

Hello-

I have been trying to import an excel data worksheet to Access where the worksheet data gets split up to related Access tables. I have success doing this using the Access excel import function followed by the table analyzer (to split and relate the tables) if all the Access tables have no exixting data or the tables don't exist yet. In this case the foreign key relationships are all established easily using the table analyzer. However the key generated by the table analyzer are numbered sequentially from 1 so the keys aren't going to work in my existing tables with existing records when I try to append to existing data in my tables.

A simple example would be an invoice that has foreign keys from a customer table and a product table.

My excel worksheet would contain all the data fields and data needed for appending the invoice, customer and product table.

Thanks much!

 

[John Big Booty]

Perhaps the sample posted in this thread will give you some pointers.

 

[kjyoung]

Thanks much John BB. Just starting to deconstruct the process employed for the import for the sample db. I was wondering if the sample db is supposed to be functional. It isn't for me- I'm using Access 2007. I've looked at the code behind the welcome form etc.-assuming the db is supposed to be functional- but so far I've not been quite savvy enough to figure out what the problem is.
(The data import seems to work fine, it's the welcome page search that isn't functioning either before or after the import.)

Again thanks, you've put me on the right track for what I need to accomplish.