Updating records in Access table using excel VBA

[Anthony1312002]

Hello everyone. Below is code in excel vba that I'm trying to adapt so that it will allow me to update multiple records in a Access table. Everything runs fine until it reaches the .MoveFirst portion highlighted in bold. There it gives me the error:

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.


What am I doing wrong and what would be the fix?

P.S: The key field in the excel sheet is in column A. It is a numeric field. The field in the Access table is called PopID. This ID correspondes to the key field in the excel sheet. It is also numeric and is the primary key for the table. This is how the data in the excel sheet is connected to that in the Access table. You can also see that I'm using a cell range to connect to the PopID in the SQL Statement. This is so I can update all the records in the Access table by ID.

I know we can beat this thing and make it work.

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 As Long
    Dim j As Long
    Dim sSQL As String
    Dim StartRow As Long
    Dim TableName As String
 
    lngID = Range("A1:H900").Select
    StartRow = 2 ' Enter row in sheet to start reading records
 
    sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & lngID
 
    Set cnn = New ADODB.Connection
    'MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
    MyConn = "C:\Admin\DB_test1.mdb"
 
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenKeyset, LockType:=adLockOptimistic
 
 
Do While Range("I" & StartRow).Value <> ""
' repeat until first empty cell in column A
With rst
.MoveFirst
.Find "[PopID] = " & Cells(StartRow, "A").Value
If Not .EOF Then
' add values to each field in the record
.Fields("Country") = Range("B" & StartRow).Value 'The Country
.Fields("Yr_1950") = Range("C" & StartRow).Value 'The Year 1950
.Fields("Yr_2000") = Range("D" & StartRow).Value 'The Year 2000
.Fields("Yr_2015") = Range("E" & StartRow).Value 'The Year 2015
.Fields("Yr_2025") = Range("F" & StartRow).Value 'The Year 2025
.Fields("Yr_2050") = Range("G" & StartRow).Value 'The Year 2025
.Fields("Region") = Range("H" & StartRow).Value 'The Region
.Fields("MyField") = Range("I" & StartRow).Value 'New Field
.Update
End If
End With
 
Loop
     MsgBox "Data has been updated!", vbInformation

 

[Jythier]

I don't see you checking for records in the recordset, yet you try to move to the first record. Are there any records in 'rst' when it moves?

 

[Anthony1312002]

Jythier, my mind is a bit foggy. Guess I'm trying to do to much. Can you help me out with that? What would the change look like so that it checks for a record?

 

[Jythier]

Right before .MoveFirst
<CODE>
<CODE>

With rst
If .BOF = True And .EOF = True Then
msgbox "No records in recordset."
Exit Sub
End If
.MoveFirst
</CODE>

</CODE>

 

[Anthony1312002]

I thought it might be good to look at this from another angle. Here is a script that updates a single record in an Access table using the ActiveCell.row method. How could this code be altered to update all the records in the table instead of just one?

My earlier approach just doesn't seem to be working. But this code is working to update a single record now which is why this may be a better base to start from.

NOTE: The PopID is the primary key in the Access table. The key field in the excel sheet is in column A and corresponds exactly to the primary key in the Access table.

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 As Long
    Dim j As Long
    Dim sSQL As String
    Dim StartRow As Long
    
    lngRow = ActiveCell.Row
    lngID = Cells(lngRow, 1).Value

    sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & lngID
     
    Set cnn = New ADODB.Connection
    'MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
    MyConn = "C:\Admin\DB_test1.mdb"
    
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenKeyset, LockType:=adLockOptimistic
    
    'Here is where the update to the Access table is made.
    For j = 2 To 7
        rst(Cells(1, j).Value) = Cells(lngRow, j).Value
    Next j
    rst.Update
    
    MsgBox "Data has been updated!", vbInformation

 

[Jythier]

In your first example, did it update things and then give you the error, or did it give you the error the first time through?

 

[Jythier]

Maybe I'm looking at this the wrong way... also, anyone else wanting to help, just jump right in here... I'm not so sure I'm going to solve this one anymore.

 

[Anthony1312002]

It gave me an error the first time through. When I tried removing the .MoveFirst it just ran forever and never completed.

 

[Jythier]

lngID = Range("A1:H900").Select
StartRow = 2 ' Enter row in sheet to start reading records
 
sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & lngID

Does this actually work like you think it should? What is lngID returning here? It's probably returning a -1, and therefore not pulling ANY records.

 

[Anthony1312002]

SOLVED: Updating records in Access table using excel VBA

Hey JyThier,

It looks like we have a solution. This script works in that it updates all the records in an Access table. I thought you might like to have a copy of it since this will prove very useful for updating not only Access tables but can be altered to update SQL Server tables as well.

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 j As Long
Dim sSQL As String

LR = Range("A" & Rows.Count).End(xlUp).Row
Upd = LR - 1

lngRow = 1
Do While lngRow <= LR


lngID = Cells(lngRow, 1).Value

'sSQL = "SELECT * FROM tblPopulation WHERE (((tblPopulation.[PopID])=" & "'" & lngID & "'" & "));"
sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & lngID


Set cnn = New ADODB.Connection
'MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
MyConn = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source = C:\Admin\DB_test1.mdb"
With cnn
' .Provider = "Microsoft.Jet.OLEDB.4.0"
.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.

' rst(Cells(1, j).Value) = Cells(lngRow, j).Value
'Next j
With rst
'.Fields("PO") = Cells(lngRow, 1).Value
.Fields("Country") = Cells(lngRow, 2).Value
'.Fields("Yr_1950") = Cells(lngRow, 3).Value
'.Fields("Yr_2000") = Cells(lngRow, 4).Value
'.Fields("Yr_2015") = Cells(lngRow, 5).Value
'.Fields("Yr_2025") = Cells(lngRow, 6).Value
'.Fields("Yr_2025") = Cells(lngRow, 7).Value
.Fields("Region") = Cells(lngRow, 8).Value
'.Fields("Po Status and/or Update") = Cells(lngRow, 20).Value

rst.Update
End With

' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

lngRow = lngRow + 1

Loop
MsgBox "You just updated " & Upd & " records"