[AccessD] Handling PK/FK relationships via VBA

[Ryan W]

Yes ideally. Just speaking in a purely hypothetical situation. You're using
two local staging tables to make the order, and a button to "send up"...

Something akin to:

https://www.mssqltips.com/sqlservertip/6142/sql-server-insert-parent-and-child-records-with-one-statement/
but using VBA/Jet queries to accomplish similar.  Basically an unknown
primary identifier (and thus, FK) until the data is ready for committing.



On Fri, Aug 6, 2021 at 1:06 PM Susan Harkins <ssharkins at gmail.com> wrote:

> 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
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>