Basically I've got some code here that updates Access from Excel, it does this via seeing if the 'Monitoring ID' equals the 'IngID' which is essentially the Monitoring ID. So basically if they match, the code needs will update the field in the access table marked 'TOWN'.
However when I do this I get an error at .Fields("TOWN") = Cells(lngRow, 74).Value. The error being: "Run-time error 3021 Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."
The record does exist in the table and I have tried with multiple however it still doesn't seem to work, any ideas?
Application.ScreenUpdating = False ' Prevents screen refreshing.
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim MyConn
Dim lngRow As Long
Dim lngID, LR, Upd
Dim sSQL As String
LR = Range("BN" & Rows.Count).End(xlUp).Row
Upd = LR - 1
lngRow = 1
Do While lngRow <= LR
lngID = Cells(lngRow, 66).Value
sSQL = "SELECT * FROM Tbl_Primary WHERE MonitorID = '" & lngID & "'"
Set cnn = New ADODB.Connection
MyConn = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source =location of access file.mdb"
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic
'Load all records from Excel to Access.
With rst
'
.Fields("TOWN") = Cells(lngRow, 74).Value
rst.Update
End With
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
lngRow = lngRow + 1
Loop
MsgBox "Update Complete"
