[AccessD] Handling PK/FK relationships via VBA

[Ryan W]

Thanks. This is more about forming the data with those relationships in
VBA, rather than managing the integrity of them and how to handle
updates/delete cascading.

I think Jim's approaches make sense. I already knew about the SELECT
@@IDENTITY one, if using passthrough queries or ADO, but the recordset
bookmark / last modified 'trick' is a neat approach too... one I didn't
consider.



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

> 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/
> <https://www.techrepublic.com/article/ensure-data-integrity-with-proper-database-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/
> <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
> >
> --
> 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
>