[AccessD] Handling PK/FK relationships via VBA

[Ryan W]

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.