Скачать или смотреть How to Insert into Two Different Tables Linked in Access

Learn how to successfully `insert records into two linked tables` in MS Access. This guide walks you through the necessary steps to achieve this using VBA code.
---
This video is based on the question https://stackoverflow.com/q/64909389/ asked by the user 'Ingis' ( https://stackoverflow.com/u/3468365/ ) and on the answer https://stackoverflow.com/a/64909916/ provided by the user 'jbud' ( https://stackoverflow.com/u/10901808/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to insert into two different tables that are linked together in Access

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Inserting Records into Two Linked Tables in Access

Database management can be a daunting task, especially when dealing with multiple tables that are linked together. In this guide, we will address a common challenge: how to insert data into two different tables that are interconnected within Microsoft Access. We will guide you through the process step-by-step using Visual Basic for Applications (VBA) code.

Understanding the Problem

Imagine you have two tables in Access:

First Table: Contains records where a unique identifier, ColID, serves as the primary key.

Second Table: Also has a unique identifier ColID, along with a foreign key ColIDFirstTable that links back to the primary key of the first table.

When you click a button on your form, you want to:

Insert data into the First Table.

Retrieve the newly generated ID from the First Table.

Use that ID to insert related data into the Second Table.

The Basic Logic

The general flow of this operation can be summarized as follows:

Insert the record into the First Table.

Get the ID of the newly inserted record.

Use this ID to create a new record in the Second Table.

Step-by-Step Solution

To accomplish this task, follow these structured steps using VBA:

Step 1: Set Up Your Database Object

Before performing any operations, declare and define your database object:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Insert Data into the First Table

Next, you will execute an INSERT statement to add a new row to the First Table using values from your form. Use the following code snippet:

[[See Video to Reveal this Text or Code Snippet]]

In this code:

Replace Col2 and vol3 with your actual column names.

Txt1.Value and Txt2.Value are the values you wish to insert from your form.

Step 3: Retrieve the ID from First Table

After successfully inserting the record, use the following line to get the last inserted ID:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Insert Data into the Second Table

With the retrieved ID, now you can insert data into the Second Table:

[[See Video to Reveal this Text or Code Snippet]]

Here:

ColIDFirstTable is the foreign key that references ColID in the First Table.

Replace col3 with the appropriate column name in the Second Table.

Conclusion

By following these steps, you can effectively insert records into two linked tables in MS Access using VBA. This method not only ensures data integrity but also maintains the relationships between the two tables.

Key Points Recap

Set up your database connection properly.

Use SQL INSERT statements to add records.

Retrieve the last inserted ID using @ @ IDENTITY.

Ensure accurate data linkage by inserting the correct foreign key into the second table.

Now, you should be well-equipped to handle record insertions into linked tables in Access. If you have any further questions or need additional assistance, feel free to reach out!