[AccessD] Handling PK/FK relationships via VBA

Rocky Smolin rockysmolin2 at gmail.com
Fri Aug 6 13:45:47 CDT 2021

The problem, as I see it is that you're using a natural order number as PK
and FK.  If you use an autonumber (fldOrderID) as PK then an order number
iis not required to link the Order with the Order Items.


On Fri, Aug 6, 2021 at 10:54 AM Ryan W <wrwehler at gmail.com> 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