[AccessD] Handling PK/FK relationships via VBA

[Susan Harkins]

In Access, you have referential integrity options that determine how you
deal with orphans. 

https://www.techrepublic.com/article/ensure-data-integrity-with-proper-datab
ase-design/

I hope that will be of some help to you. But it won't answer your question
unfortunately -- and you might already know it all already. 

Susan H. 

-----Original Message-----
From: AccessD <accessd-bounces+ssharkins=gmail.com at databaseadvisors.com> On
Behalf Of Ryan W
Sent: Friday, August 6, 2021 2:19 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Handling PK/FK relationships via VBA

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