[AccessD] Handling PK/FK relationships via VBA

Stuart McLachlan stuart at lexacorp.com.pg
Fri Aug 6 19:14:49 CDT 2021

I sometimes use a DAO recordset with AddNew
The PK is available.as soon as you Addnew
(of course it gets used up even if you don't .Update for some reason)

Dim OrderPK As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblrders")
CustPK = rs!customerID
rs!CustomerFK = 1234
MsgBox OrderPK

On 6 Aug 2021 at 12:54, Ryan W wrote:

> 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

More information about the AccessD mailing list