Multiple Simultaneous Table Inserts

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.


Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may