Retrieving Last Inserted Max Record From MS access to Excel Using ADO

Question

Hi May Would Like to know why Copyfromrecordset wont work Any work around using ADO?

I only have one Table One Number COlumn and it does not accept duplicates. Also need to retrieve the ID number in order to be used by other codes for MultiUser Purpose.

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

Answer 1

See this paragraph in Remarks

It is not a good idea to use the Source argument of the Open method to perform an action query that does not return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed. To perform a query that does not return records, such as a SQL INSERT statement, call the Execute method of a Command object or the Execute method of a Connection object instead.

If you work around with separate select and insert queries, the risk is that another user could create a record in between the two. Using an Auto-Increment key is preferred. With that caveat try

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 As String, sql As String
    Dim newID 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

    rst.Open "SELECT MAX(DVNumber)+1 FROM DvID", cnn
    newID = rst(0)
    cnn.Execute "INSERT INTO DvID(DVnumber) VALUES (" & newID & ")"

    Sheet3.Range("A2") = newID

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

    MsgBox newID & " Inserted", vbInformation

    On Error GoTo 0
    Exit Sub
errHandler:

    Set rst = Nothing
    Set cnn = Nothing

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PostData"

End Sub