Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Beginner
Oct 1, 1996



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Multiple Simultaneous Table Inserts

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.

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 to accomplish what this user wanted, following is pseudocode for what they are trying to accomplish. I have used it several times with excellent results.

open connection
begin transaction
insert into table1
table1foreignkey = get @@Identity
insert into table2
table2foreignkey = get @@Identity
insert into table3 ......
commit trans
This will effectively accomplish what your reader was trying.
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date