Updating multiple records in Access table using excel VBA

Hoping someone can help me with this. I've have a script that I'm modifying. It's original purpose was to add new records to an access table, which it does with no problem. What I want it to do is go from adding new records to updating existing ones. The only change I've made is in bold. I changed the .AddNew to .Update. I thought is would work but apparently not. Can anyone help me see what I'm missing that would make this work?



Application.ScreenUpdating = False ' Prevents screen refreshing.

Dim cn As ADODB.Connection
Dim DatabaseName As String
Dim TableName As String
Dim rs As ADODB.Recordset
Dim StartRow As Long

Set rs = New ADODB.Recordset

DatabaseName = "DB_test1.mdb" ' Enter your database name here
TableName = "tblPopulation" ' Enter your Table name here
StartRow = 2 ' Enter row in sheet to start reading records


Dim shtSheetToWork As Worksheet
Set shtSheetToWork = ActiveWorkbook.Worksheets("Table download")
'********

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Admin\DB_test1.mdb;Persist Security Info=False;"


rs.Open TableName, cn, adOpenKeyset, adLockOptimistic


Do While Range("A" & StartRow).Value > 0

' repeat until first empty cell in column A
With rs
.Update ' create a new record
' 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

' add more fields if necessary...
.Update ' stores the new record
End With
StartRow = StartRow + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
MsgBox "Data has been updated!", vbInformation
Set cn = Nothing

I see what your saying. Along those lines found this code on this forum that updates a single record but I don't know what alterations to make so that it will update multiple ones. It uses the active cell as the targeted update. Any thoughts on how this could be altered to use a range instead of just updating the active cell?

Please Login or Register  to view this content.

Thanks for being patient. I'm need to understand what you mean by key field. Are speaking of the primary key in the Access table?

Okay, I understand now. Well the key field in the Access table is PopID. This code that updates a single record based on the PopID in the Access table uses the ActiveCell function highlighted in bold along with the sql statement. I've been trying to use a range of cells instead of just the active cell in the update process but with no success. Can that be done using this code or the one I posted earlier?

Please Login or Register  to view this content.

Okay, it looks like I really gave some wrong info. I really apologize. Here is what I meant to say. The key field in my excel file is in column A. It is numerical and matches with the primary key filed in the Access table. That is how the excel file is connected to the Access table. How can I use this code to update multiple records in the Access table using the key field?

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


lngRow = Range("A1:A228").Select

sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & lngID


Set cnn = New ADODB.Connection
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

I know this will have to change, but to what I don't know
'Update records in Access.
For j = 2 To 7
rst(Cells(1, j).Value) = Cells(lngRow, j).Value
Next j
rst.Update

Man, I keep forgetting about those tags. Thanks!

Please Login or Register  to view this content.

It looks like we're close but I'm still missing something. Maybe if you had a copy of the excel file it would help. I've attached it. You will see that there are several scripts including one that creates a database and puts it in the directory C:\Admin.

Hey OnErrorGoto0, Sorry about the late response. I just tried the code but it seems to be be taking a long time to run. Did you run into that as well?