Updating a database from Excel
My project of the day is a Sales Budget database in Access 2000 and I need to update the information in the Budget table with info from Excel. I have most of it figured out but how to check that the to key match and update that record for the three columns.
Here is what I have so far:
VB Code:
Do Until Trim(Sheet.Cells(Xo, constKEY)) = ""
lblNote.Caption = "Uploading data " & Xo - 1 & " of " & RowCount - 2 & "..."
Me.Refresh
DoEvents
'Move the row data to the TYPE Var to reduce the number of time that this aplication accesses MS Excel.
.Key = Sheet.Cells(Xo, constKEY)
.Revised = Sheet.Cells(Xo, constREVISED)
.Budget = Sheet.Cells(Xo, constBUDGET)
'Load data to db and do validation for empty strings
Set rsAccess = New ADODB.Recordset
sSQL = "SELECT * FROM tblBudget"
rsAccess.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic
If .Key <> "" Then
With rsAccess
.AddNew
.Fields("Revised03").Value = Trim(RowData.Revised)
.Fields("2004Budget").Value = Trim(RowData.Budget)
.Fields("Changes").Value = Trim(RowData.Budget)
.Update
End With
End If
Xo = Xo + 1
Progbar.Value = Xo
DoEvents
Loop
after the validation for blank records I need to match tblBudget.key with rowdata.key and update the fields revised03, 2004budget and changes. right now it will add them as new, this I think just needs to be .update. Am I right there.