[AccessD] Handling PK/FK relationships via VBA

[Stuart McLachlan]

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")
rs.AddNew
CustPK = rs!customerID
rs!CustomerFK = 1234
'......
rs.Update
rs.Close
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
>