使用Excel VBA+ADODB向Access插入含FK关联的两条记录

问题描述 #

我正在搭建一个多用户Excel应用，以Access作为数据库层。目前遇到的问题是：如何向两个表中插入两条记录，其中第一条记录的ID是第二条记录的外键（FK）。

我曾尝试使用DMax和最后修改函数，但不确定在最多5人的多用户环境下能否正常工作，且删除操作也会对这两个函数产生影响。

我考虑过两种临时方案：

• 创建第一条记录后，在Excel刷新界面中选中它再触发第二条记录的插入；
• 在插入第一条记录时生成UUID，再通过UUID查询关联，但不确定这两种方案是否可靠。

测试代码与补充说明 #

我测试了使用@@IDENTITY的代码，该代码能正常返回插入记录的ID。需要说明的是，我曾尝试通过单次调用完成操作，但MS Access不支持此方式，且无法使用scope_identity。

测试代码如下：

Sub InsertRecordAndGetID()
    Dim conn As Object
    Dim strSQL As String
    Dim newRecordID As Long
    
    ' Set up the connection string to the Access database
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=My\db\location\db.accdb;"
    
    ' Define the SQL statement for inserting the record
    strSQL = "INSERT INTO employee (EmployeeName, EmployeeSurname) VALUES ('SomeName', 'SomeSurname');"
    
    ' Execute the SQL statement
    conn.Execute strSQL
    
    ' Retrieve the ID of the last inserted record using @@IDENTITY()
    strSQL = "SELECT @@IDENTITY AS NewID;"
    Set rs = conn.Execute(strSQL)
    newRecordID = rs.Fields("NewID").Value
    
    ' Close the connection
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "The ID of the last inserted record is: " & newRecordID
End Sub

内容的提问来源于stack exchange，提问作者Sebastjan Hribar