[AccessD] Handling PK/FK relationships via VBA

[Jim Dettman]

 You commit the header first, grab the ID, then commit the order items.

<<
Now you want to upload your OrderItems data, but you need that OrderID.
>>

If you are doing SQL, you can do:

SELECT @@IDENTITY AS 'OrderID'

 Right after the header insert.   If you are doing it in a recordset, you
can do:

 Rec.Update
 Rec.Bookmark = Rec.LastModified
 lngOrderID = Rec![OrderID]

 is that what you are after?

Jim.

-----Original Message-----
From: AccessD On Behalf Of Ryan W
Sent: Friday, August 6, 2021 1:54 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: [AccessD] Handling PK/FK relationships via VBA

Hey List,
 As you all know Access does a wonderful job of handling PK/FK
relationships on forms with LinkMaster and LinkChild fields.


 However, my question to you all is... if you're working with temporary
data how do you handle PK/FK fields once you commit it to the database.

For instance you have Orders and OrderItems.  The relationship between them
is OrderID which is not yet known because it hasn't committed to a table to
get a number yet:

So you run your SQL query to upload the order data.

Now you want to upload your OrderItems data, but you need that OrderID.

How do you handle keeping that PK/FK relationship intact?  with TSQL you
can get crafty and use SCOPE_IDENTITY() or use the OUTPUT clause. Keep in
mind this is a multi-user database, so using DMAX("OrderID", "tblOrders")
to get the potential OrderID could be erroneous.

Keep in mind this is strictly using ad-hoc type queries in VBA to
accomplish this.  Purely just an exercise type question.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com