[AccessD] Handling PK/FK relationships via VBA
wrwehler at gmail.com
Fri Aug 6 13:50:32 CDT 2021
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.
> 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
> > is OrderID which is not yet known because it hasn't committed to a table
> > 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
> Website: http://www.databaseadvisors.com
More information about the AccessD