[AccessD] Handling PK/FK relationships via VBA

Susan Harkins ssharkins at gmail.com
Fri Aug 6 13:06:12 CDT 2021

You shouldn't enter items until you have an orderid. 

Susan H. 

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
Website: http://www.databaseadvisors.com

More information about the AccessD mailing list