CopyFromRecordset 不工作

嗨，我插入了一個選擇 MaxID+1 數據並想檢索我插入的那個 ID 以下是程式碼

我試圖將數據從 Excel 插入 Ms Access 並自動檢索該數據。出於多使用者目的，我需要專門檢索我插入的數據。

Sub PostData()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset classe here
Dim dbPath
Dim x As Long, i As Long

'add error handling
On Error GoTo errHandler:

dbPath = Sheets("Sheet3").Range("h1").Value

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

Set rst = New ADODB.Recordset 'assign memory to the recordset
Sql = "INSERT INTO DvID(DVnumber)SELECT Max(DVNumber)+1 FROM DvID "
rst.Open Sql, cnn
Sheet3.Range("A2").CopyFromRecordset rst
rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

On Error GoTo 0
Exit Sub
errHandler:

Set rst = Nothing
Set cnn = Nothing

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"
End Sub

我終於想通了。

Do
On Error Resume Next 'reset Err.obj.

'Get the Max ID +1
Set rst = Nothing
Set rst = New ADODB.Recordset 'assign memory to the recordset
SQL = "SELECT Max(ApNumber)+1 FROM PayVoucherID "
rst.Open SQL, cnn

'Check if the recordset is empty.
'Copy Recordset to the Temporary Cell
Sheets("MAX").Range("A2").CopyFromRecordset rst

'Insert the Data to Database And Check If no Errors
Sql2 = "INSERT INTO PayVoucherID(ApNumber)Values('" & Sheets("MAX").Range("A2") & "') "
cnn.Execute Sql2
Loop Until (Err.Number = 0)}

引用自：https://dba.stackexchange.com/questions/267264