Access VBA SQL - Add records to two linked tables

In my Access VBA SQL code I need to add records to two linked tables: "Orders" and "Order Details".
I need to first add a record to the "Orders" table. Then I need to use the new number in the "ID" column
of this table and use it to insert records in the "Order Details" table, which has a field called "Order ID".
How do I do this?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:

You can achieve this with two recordsets, and for data integrity purpose, a transaction:
(Assuming ID fields are on auto-number, no need to provide value)
public sub Update()
On Error goto Error
    Dim transactionProcessing As Boolean
    transactionProcessing = False

    Dim ws As DAO.Workspace
    Set ws = Application.DBEngine(0)
    Dim db As DAO.Database
    Set db = CurrentDb

    Dim rsOrders As DAO.Recordset
    Set rsOrders = db.Tabledefs("Orders").OpenRecordset(dbOpenDynaset)

    Dim rsOrderDetails As DAO.Recordset
    Set rsOrderDetails = db.Tabledefs("Order Details").OpenRecordset(dbOpenDynaset)

        '// Start transaction
    transactionProcessing = True
        '// 1st, add a new order
    rsOrders.fields("....").value = "....."    '// add you new values here
    rsOrders.Bookmark = rsOrders.LastModified    '// go to last modified record

        '// 2nd, add a new order detail
    rsOrderDetails.Fields("Order ID").Value = rsOrders.Fields("ID").Value
    rsOrderDetails.Fields(".....").Value = "...."    '// add other fields here
        '// Everything is Ok, commit the transaction (and push all changes to the database)
    transactionProcessing = False

    Set rdOrderDetails = Nothing
    Set rsOrders = Nothing
    Set db = Nothing
    Set ws = Nothing
Exit Sub
        '// something went wrong, undo all changes
    If(transactionProcessing) Then
    End If
        '// cleanup
    if not(rsOrderDetails Is Nothing) Then
        Set rsOrderDetails = Nothing
    End If
    If Not(rsOrders Is Nothing) Then
        Set rsOrders = Nothing
    End If
    If Not(db Is Nothing) Then Set db = Nothing
    If Not(ws Is Nothing) Then Set ws = Nothing
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub

Open in new window

Máté FarkasDatabase Developer and AdministratorCommented:
You definitely need a key of rows between table Orders And OrderDetails.
For example:
 - You insert Orders with these columns: OrderDate, OrderTime, ClientID
 - You insert OrderDetails with these columns: OrderDate, OrderTime, ClientID, ProductID, Quantity

The you insert data into Orders (and OrderIDs will be generated) and then join data back for OrderDetails to Orders by OrderDate, OrderTime, ClientID (this is the key) and you will get back the OrderID from Orders.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. Thanks. I should have clarified that I am looking for the VBA code to do this
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.