[AccessD] Handling PK/FK relationships via VBA

[Ryan W]

That relationship happens naturally with LinkMaster/LinkChild fields on
forms, but in VBA that relationship has to be manufactured.  Jim Dettman's
response is one of the ways I was looking at it.  Again this is purely just
for the sake of discussion on "how do I do this a way that I've never done
it before" ... just for gaining knowledge and learning.


On Fri, Aug 6, 2021 at 1:46 PM Rocky Smolin <rockysmolin2 at gmail.com> wrote:

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