Question:
I am programming a multiple table database and can’t figure out to link each table together so that when a person is posting data to one table that the other tables are tied to the same persons record.Even have my wife and the dog working on this one! Were stumped.
Answer:
Unfortunately, because the INSERT SQL statement can only insert into one table at a time, you aren’t going to be able to do it that way. The way I’d suggest you do it is to create a query in Access that ties the tables together that you want to deal with, and then add your records to that recordset after you create it (Using OpenRecordset). This is always a tricky part of doing multiple tables…you may end up having to do multiple inserts, depending on what you want to do.
On 2/11/00, DevX user Paul W. Dysart responded:
While there is no easy way toaccomplish what this user wanted, following is pseudocode for what they are trying to accomplish. I have used it several times with excellent results.
open connectionbegin transactioninsert into table1table1foreignkey = get @@Identityinsert into table2table2foreignkey = get @@Identityinsert into table3 ......commit trans
This will effectively accomplish what your reader was trying.
