[AccessD] Handling PK/FK relationships via VBA

[Rocky Smolin]

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.

r

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
>