Excel VBA and ADODB: Inserting Two Records with Foreign Key into Access Database for Enhanced Data Integrity

First, let me explain the basic theory of inserting two records with Excel VBA into Access using ADODB with FK. ADODB stands for ActiveX Data Objects Database, which is a library that allows you to manipulate data from various sources, such as Access, Excel, SQL Server, etc. FK stands for foreign key, which is a field in one table that refers to the primary key of another table. This creates a relationship between the two tables, and ensures data integrity and consistency. For example, if you have a table of employees and a table of departments, you can use the department ID as a foreign key in the employee table, to link each employee to their respective department.

To insert two records in two tables where record 1 ID is the FK in the record 2 table, you need to do the following steps:

• Create and open a connection to the Access database using the ADODB.Connection object. You need to specify the provider, the data source, and any other connection properties.
• Define the SQL statement for inserting the first record into the first table. You can use the INSERT INTO syntax to specify the table name, the field names, and the values to insert. You can use variables or cell references to get the values from Excel.
• Execute the SQL statement using the ADODB.Connection.Execute method. This will insert the record into the Access table and return a recordset object.
• Retrieve the ID of the last inserted record using the @@IDENTITY function. This is a system function that returns the value of the identity column (usually the primary key) of the last inserted record. You can use the ADODB.Connection.Execute method again to run a SELECT query with the @@IDENTITY function, and get the value from the recordset object.
• Define the SQL statement for inserting the second record into the second table. You can use the same syntax as before, but this time you need to use the ID of the first record as the FK value for the second record. You can use the variable that stores the ID value from the previous step.
• Execute the SQL statement using the ADODB.Connection.Execute method. This will insert the second record into the Access table and return another recordset object.
• Close the connection and the recordset objects using the ADODB.Connection.Close and ADODB.Recordset.Close methods. This will release the resources and end the communication with the database.

To illustrate these procedures, let me give you a scenario and an example with real data. Suppose you have an Access database named “School.accdb” that contains two tables: “Students” and “Courses”. The “Students” table has the following fields: StudentID (primary key, auto-number), FirstName, LastName, and CourseID (foreign key, number). The “Courses” table has the following fields: CourseID (primary key, auto-number), CourseName, and Instructor. You want to insert two records into these tables using Excel VBA, one for a new student and one for a new course. You have the following data in Excel:

You can use the following code to insert these records into the Access database:

Sub InsertTwoRecords()
    ' Declare the variables
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim newStudentID As Long
    Dim newCourseID As Long
    
    ' Set up the connection string to the Access database
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\School.accdb;"
    
    ' Define the SQL statement for inserting the first record into the Courses table
    strSQL = "INSERT INTO Courses (CourseName, Instructor) VALUES ('" & Range("C2").Value & "', '" & Range("D2").Value & "');"
    
    ' Execute the SQL statement and get the recordset object
    Set rs = conn.Execute(strSQL)
    
    ' Retrieve the ID of the last inserted record using @@IDENTITY
    strSQL = "SELECT @@IDENTITY AS NewID;"
    Set rs = conn.Execute(strSQL)
    newCourseID = rs.Fields("NewID").Value
    
    ' Define the SQL statement for inserting the second record into the Students table
    strSQL = "INSERT INTO Students (FirstName, LastName, CourseID) VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', " & newCourseID & ");"
    
    ' Execute the SQL statement and get the recordset object
    Set rs = conn.Execute(strSQL)
    
    ' Retrieve the ID of the last inserted record using @@IDENTITY
    strSQL = "SELECT @@IDENTITY AS NewID;"
    Set rs = conn.Execute(strSQL)
    newStudentID = rs.Fields("NewID").Value
    
    ' Close the connection and the recordset objects
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    ' Display the IDs of the inserted records
    MsgBox "The ID of the new course is: " & newCourseID & vbCrLf & "The ID of the new student is: " & newStudentID
End Sub

This code will insert the records into the Access database and display the following message:

The ID of the new course is: 5 The ID of the new student is: 11

You can verify the result by opening the Access database and checking the tables. You should see the following data: