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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Fabrice LambertFabrice 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.