I am on a rush. I have tested it and works on old the sample I had created for you. It puts the value in to Range A2.
Option Explicit
Sub GetmaxToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Range, StrSQL As String
' connect to the Access database
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\Henry\Desktop\Tracking.accdb; Persist Security Info=False;"
StrSQL = "SELECT Max(ID) as MaxValue FROM [Tbl-Form];"
'recordset.open source, activeconnection,cursortype,locktype,options, adOpenForwardOnly is the default- It is foreard cursor only (Go foreard only
'Optimsitc locking is two people are working on the same data. You can lock the record.
rs.Open StrSQL, cn
' copy the record in A2
Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Bookmarks