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]

Re: SOLVED: Updating records in Access table using excel VBA

Well, figured out what the problem was. At least there's that. Glad you got the answer though. An answer at least. I'm pretty sure there's another way to do this that actually does go and get the partial recordset you're going to update and then goes through it. I can make lngID go out and get an array of values, but I don't know off the top of my head how to make that into a string of values for SQL to read... not without a loop doing it, anyway. The problem was that you lngID was returning -1 instead of an array of values for SQL to read. Having the SQL part inside the loop solved that.

 

[Anthony1312002]

Yep, that was my problem. I just couldn't see it. But your suggestions really helped me start down the right path. And your right, it would really be more efficient to update only those records that have been altered instead of all of them. I'm doing this currently in a web application I created. But I couldn't figure out how to apply this to excel vba. But I won't stop trying. If you are able to make it happen let me know.

 

[dimsums]

Hi there,
I tried using this code above, but it seems to be giving me a system error at "load all records from excel to access"
The error is "Run time error '-2147217900(80040e14)':