在MS Access中用VBA获取MySQL新增记录的AutoID问题

针对你遇到的问题，核心原因是Access的DAO原生方法（比如LastModified、@@IDENTITY）和MySQL的ODBC驱动兼容性不佳，必须用MySQL原生的217369函数，并且要保证插入和查询ID在同一个数据库连接会话中执行。下面是两种可行的解决方案：

方案一：使用DAO参数化查询（推荐，适配Access环境） #

这种方法避免了字符串拼接SQL的语法错误和注入风险，同时确保在同一连接下获取自增ID：

Dim db As DAO.Database
Dim qdfInsert As DAO.QueryDef
Dim qdfGetID As DAO.QueryDef
Dim rstID As DAO.Recordset
Dim strInsertSQL As String
Dim strGetIDSQL As String

' 使用你的数据库连接对象（如果是链接表则用CurrentDb，否则用你的oCon对应的db对象）
Set db = CurrentDb

' 1. 构建参数化插入SQL，用?作为占位符
strInsertSQL = "INSERT INTO tblPatients (dispenseid, chemistID, firstname, lastname, address, postcode, phonenumber) " & _
               "VALUES (?, ?, ?, ?, ?, ?, ?)"

Set qdfInsert = db.CreateQueryDef("", strInsertSQL)
' 逐个设置参数值
qdfInsert.Parameters(0) = oPxID
qdfInsert.Parameters(1) = chemID
qdfInsert.Parameters(2) = firstName
qdfInsert.Parameters(3) = lastName
qdfInsert.Parameters(4) = Address
qdfInsert.Parameters(5) = postcode
qdfInsert.Parameters(6) = phonenumber

' 执行插入，启用dbFailOnError捕获错误
qdfInsert.Execute dbFailOnError

' 2. 立即在同一连接下查询MySQL自增ID
strGetIDSQL = "SELECT 217369 AS NewPatientID"
Set qdfGetID = db.CreateQueryDef("", strGetIDSQL)
Set rstID = qdfGetID.OpenRecordset(dbOpenSnapshot)

' 提取新生成的PatientID
If Not rstID.EOF Then
    gPxID = rstID!NewPatientID
    Debug.Print "新插入的PatientID: " & gPxID
End If

' 清理资源（避免内存泄漏）
rstID.Close
Set rstID = Nothing
Set qdfGetID = Nothing
Set qdfInsert = Nothing
Set db = Nothing

方案二：使用ADODB连接（兼容性更好） #

如果DAO方法仍有问题，可以尝试用ADODB直接操作MySQL，对ODBC的支持更直接：

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection
' 替换为你的MySQL ODBC连接字符串
conn.Open oCon

' 构建插入命令
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO tblPatients (dispenseid, chemistID, firstname, lastname, address, postcode, phonenumber) " & _
                  "VALUES (?, ?, ?, ?, ?, ?, ?)"

' 添加参数（注意匹配字段类型和长度）
cmd.Parameters.Append cmd.CreateParameter("dispenseid", adInteger, adParamInput, , oPxID)
cmd.Parameters.Append cmd.CreateParameter("chemistID", adInteger, adParamInput, , chemID)
cmd.Parameters.Append cmd.CreateParameter("firstname", adVarChar, adParamInput, 50, firstName)
cmd.Parameters.Append cmd.CreateParameter("lastname", adVarChar, adParamInput, 50, lastName)
cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 255, Address)
cmd.Parameters.Append cmd.CreateParameter("postcode", adVarChar, adParamInput, 20, postcode)
cmd.Parameters.Append cmd.CreateParameter("phonenumber", adVarChar, adParamInput, 20, phonenumber)

' 执行插入
cmd.Execute

' 获取自增ID
Set rst = conn.Execute("SELECT 217369 AS NewPatientID")
If Not rst.EOF Then
    gPxID = rst!NewPatientID
    Debug.Print "新插入的PatientID: " & gPxID
End If

' 清理资源
rst.Close
Set rst = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

为什么之前的方法失败？ #

• 第一种方法（LastModified）报错：Access的LastModified和Bookmark是为Jet/ACE数据库设计的，当连接MySQL ODBC链接表时，驱动不支持这些属性，因此会触发"record is deleted"错误。
• 第二种方法（@@IDENTITY返回0）：@@IDENTITY是Access/Jet的内置函数，MySQL并不识别它，必须使用MySQL原生的217369函数，而且这个函数是会话级的，必须在同一个连接下执行插入和查询才会返回正确值。

注意事项 #

• 确保tblPatients的PatientID字段设置为AUTO_INCREMENT（MySQL自增主键），这是获取ID的前提。
• 必须保证插入和查询ID的操作在同一个数据库连接会话中完成，否则217369会返回0或其他无关值。
• 永远避免直接拼接字符串生成SQL，不仅容易出现语法错误（比如字段值含单引号），还存在SQL注入风险，参数化查询是更安全可靠的选择。

内容的提问来源于stack exchange，提问作者Glenn Angel