Return Primary Key

[Lasca]

Hey there

I have an access db with multiple tables linked together with primary keys.
I need to populate this db from excel. Is it possible to upload a record to a
parent table, have the primary key for that record returned, and then used
when uploading records to the child tables.

I am fairly new at working with VBA, and any help would be highly appreciated.

Regards

 

[goshute]

Lasca,
There are lots of ways to do this but this works well.

Create an Interface table in Access that contains all the necessary
columns for each of the parent and child tables.
Create a query that will append this data to the tables.
Create a macro that will run the query. You also need to mark the
rows as processed in the Interface table after running the append
query.

In Excel create a Worksheet in the same format as the Interface table
in Access.
Populate the Excel worksheet
Link the Access database to this Excel Worksheet

Run the query to import the records into the Access Interface Table
either from Access or Excel. Importing the data into the Interface
table allows to do final data integrity checks before loading the data
into your production tables.
Do Data Integrity checks
Load Produciton Tables
You will need the Macro if running from Excel

All records in the Excel Worksheet and the Interface table must be
marked as processed so they can not be imported again and again and
again.

code to run an Access Query from Excel

Sub RunAccessQuery()
Dim appAccess As Access.Application
Set appAccess = New Access.Application
appAccess.Visible = True
appAccess.DoCmd.SetWarnings False
appAccess.OpenAccessProject ("C:\Test.mdb")
appAccess.DoCmd.RunMacro ("MacroToImportRecords")
appAccess.DoCmd.SetWarnings True
appAccess.Quit
Set appAccess = Nothing
End Sub

Goshute