Looking for fast way to import data from Excel into Access table with relationships/IDs.
I have a user that needs to import data daily into Access from an Excel spreadsheet.
I've been using the importspreadsheet in my Access VBA, then dump that data into a temp table, and then import it using append queries.
The queries run from a form with buttons so the user doesn't have to do anything, everything is imported in the back with the VBA.
The problem I'm having is that there are about 50 fields that have relationships to other tables using a primary key. For example the spreadsheet has the Customer Name but in Access the customer has an ID number. Before I write a Dlookup for each field I was wondering if there is an easier way.
What's the best/fastest way to lookup the correct IDs from the other tables?
I've been using the importspreadsheet in my Access VBA, then dump that data into a temp table, and then import it using append queries.
The queries run from a form with buttons so the user doesn't have to do anything, everything is imported in the back with the VBA.
The problem I'm having is that there are about 50 fields that have relationships to other tables using a primary key. For example the spreadsheet has the Customer Name but in Access the customer has an ID number. Before I write a Dlookup for each field I was wondering if there is an easier way.
What's the best/fastest way to lookup the correct IDs from the other tables?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
Apparently repeating advice is the ticket.
Maybe for some cases ...especially when someone jumps to a well answered question but this is not the case...and i have witnessed it far too often ...
JT
JT
ASKER
Apologies Pat I did try to mark your advice as solution but it didn't take. Sorry! I frequently refer to your solutions and appreciate you taking the time to respond.
You can ask a moderator to reopen the question if you wish to reassign the points.
ASKER