Updating Access Database from Excel Worksheet Data

Question

I extract data from my Access database into an Excel worksheet using a macro. I first open a connection to the database, define my sql statement in a string var and then dump that data in a recordset:

Dim db As Database
Dim rs As RecordSet
Dim sql As String
Dim dbLocation As String

dbLocation = ThisWorkbook.Path & "\database\data.accdb"
Set db = OpenDatabase(dbLocation)
sql = "Select * FROM [Master Table]"
Set rs = db.OpenRecordSet(sql, dbOpenSnapshot)

If Not rs.EOF Then
   Worksheets("Sheet1").Range("A1").CopyFromRecordset rs
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

This works perfectly. I distribute this to some people and ask them to update fields. I then need to update the Access data with data that is passed back. The simple thing in terms of design is that the extracted excel data mirrors the access db in structure so the update query should be simple. Also there is a primary key, so I would just need to map on that field.

Any ideas how this can be done? Can I load the whole excel datasheet into a recordset and run some snazzy update query?

Answer 1

You need to loop over rows on sheet 1, and for each row make sql string that looks like:

"update [Master table] set
  TableField1 = " & Range(Row, Col1).Value & ","
  TableField2 = " & Range(Row, Col2).Value & ","
  ...
where IDTableField = " & Range(Row, IDColNum).Value

and then do

db.Execute thatString

PS: There are may be mistakes in my syntax. And you need to convert cell values to strings when making string.

Answer 2

An extension of shibormot's solution using DAO:

Set objConnection = CreateObject("DAO.DBEngine.36")
Set db = objConnection.OpenDatabase(strDBPath, blnExclusive, blnReadOnly, strPassword)

For Each row In Range("A1:C3").Cells
    strSQL = "UPDATE table SET "
    strSQL = strSQL & "Field1 = " & Chr(34) & row.Cells(1) & Chr(34) & ","
    strSQL = strSQL & "Field2 = " & Chr(34) & row.Cells(2) & Chr(34) & ","
    strSQL = strSQL & "Field3 = " & Chr(34) & row.Cells(3) & Chr(34)
    Db.Execute
Next

Threw in the chr(34) for string data