Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Primary Key
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Primary Key
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recordset with Primary Key | Excel Programming | |||
Generating a primary key | Excel Discussion (Misc queries) | |||
(Primary) Key Column? | Excel Discussion (Misc queries) | |||
PRIMARY KEYS | Excel Programming | |||
Check for Primary Key | Excel Programming |