Access VBA SQL - Add records to two linked tables

Hi
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?
Thanks
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
Hi,

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
    ws.BeginTrans
    transactionProcessing = True
        '// 1st, add a new order
    rsOrders.addNew
    rsOrders.fields("....").value = "....."    '// add you new values here
    rsOrders.Update
    rsOrders.Bookmark = rsOrders.LastModified    '// go to last modified record

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

    rsOrderDetails.Close
    Set rdOrderDetails = Nothing
    rsOrders.Close
    Set rsOrders = Nothing
    Set db = Nothing
    Set ws = Nothing
Exit Sub
Error:
        '// something went wrong, undo all changes
    If(transactionProcessing) Then
        ws.RollBack
    End If
        '// cleanup
    if not(rsOrderDetails Is Nothing) Then
        rsOrderDetails.Close
        Set rsOrderDetails = Nothing
    End If
    If Not(rsOrders Is Nothing) Then
        rsOrders.close
        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

0
 
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.
0
 
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
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you very much
0
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.