vba 从 Excel 工作表数据更新 Access 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15799353/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating Access Database from Excel Worksheet Data
提问by user559142
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:
我使用宏将 Access 数据库中的数据提取到 Excel 工作表中。我首先打开与数据库的连接，在字符串 var 中定义我的 sql 语句，然后将该数据转储到记录集中：
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.
这完美地工作。我将其分发给某些人并要求他们更新字段。然后，我需要使用传回的数据更新 Access 数据。设计上的简单点是提取的excel数据在结构上反映了访问数据库，因此更新查询应该很简单。还有一个主键，所以我只需要在该字段上进行映射。
Any ideas how this can be done? Can I load the whole excel datasheet into a recordset and run some snazzy update query?
任何想法如何做到这一点？我可以将整个 excel 数据表加载到记录集中并运行一些时髦的更新查询吗？
回答by shibormot
You need to loop over rows on sheet 1, and for each row make sql string that looks like:
您需要遍历工作表 1 上的行，并为每一行生成如下所示的 sql 字符串：
"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.
PS：我的语法可能有错误。并且在制作字符串时需要将单元格值转换为字符串。
回答by Steven Marciano
An extension of shibormot's solution using DAO:
shibormot 使用 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
为字符串数据注入 chr(34)
