How to export Excel data into MS Access by querying first the MAX column value and adding 1?

Question

I'm creating an Excel form in which my database is MS Access.

First I encode data in Excel then using command button to post this data.

The code within the command button: first get the max number from Access and use that number to complete the data in Excel to be exported to Access. The problem is If I use 2 and above users to simultaneously Post it will consolidate all the data into one with the same number.

What I want is to lock opening data until posted since I need to get the max number of a column then add 1 first then import a complete set of data including the MAX number as a control number.

I tried using Do While adStateOpen <> 1 and also Do While IsRecordBusy = True then wait and loop and set the recordset to nothing instead of closing it twice. But it wont work it will consolidate the data with the same control number.

This is my code:

Option Explicit

Sub ImportJEData()

Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath
Dim x As Long, i As Long
Dim nextrow As Long
Dim Var
Dim LockType
Dim SQL
Dim IsRecordBusy


'add error handling
On Error GoTo errHandler:

'Variables for file path and last row of data
dbPath = Sheets("Update Version").Range("b1").Value
Set Var = Sheets("JE FORM").Range("F14")
nextrow = Sheets("LEDGERTEMPFORM").Cells(Rows.Count - 5, 1).End(xlUp).Row

'Initialise the collection class variable
Set cnn = New ADODB.Connection

'Create the ADODB recordset object. for Max Number
Set rst = New ADODB.Recordset 'assign memory to the recordset
LockType = adLockPessimistic

'Do While adStateOpen <> 1
Do While IsRecordBusy = True
Application.Wait (Now + TimeValue("0:00:01") / 1000)
Loop

SQL = "SELECT distinct Max(DVNumber),Max(ChckID) FROM DV "

rst.Open SQL, cnn

Sheets("Max").Range("A2").CopyFromRecordset rst

Set rst = Nothing

Set rst = New ADODB.Recordset 'assign memory to the recordset

rst.Open Source:="DV", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockPessimistic, _
Options:=adCmdTable

On Error Resume Next

'you now have the recordset object
'add the values to it
For x = 7 To nextrow
rst.AddNew
For i = 1 To 37
rst(Sheets("LEDGERTEMPFORM").Cells(6, i).Value) = Sheets("LEDGERTEMPFORM").Cells(x, i).Value
Next i
rst.Update
Next x

'close the recordset
rst.Close
' Close the connection
cnn.Close
'clear memory
Set rst = Nothing
Set cnn = Nothing


'Update the sheet



Application.ScreenUpdating = True

'Clear the data
On Error GoTo 0
Exit Sub
errHandler:

'clear memory
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"

End Sub

Answer 1

Im finally done with this code. I just added another Table in access where such table is unique the uses sql to get the max+1 and insert it back and loop it if error occurs because of the duplicate. Heres the code below

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)

Hope this helps for Excel Front users.