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
Bookmarks