从Excel工作表数据更新Access数据库 [英] Updating Access Database from Excel Worksheet Data

问题描述

我使用宏将Access数据库中的数据提取到Excel工作表中.我首先打开与数据库的连接，在字符串var中定义我的sql语句，然后将该数据转储到记录集中:

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

这很好用.我将其分发给某些人，并要求他们更新字段.然后，我需要使用传回的数据更新Access数据.就设计而言，简单的事情是，提取的excel数据在结构上镜像了访问db，因此更新查询应该很简单.还有一个主键，所以我只需要映射到该字段即可.

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.

有什么想法可以做到吗?我可以将整个excel数据表加载到记录集中并运行一些时髦的更新查询吗?

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

推荐答案

您需要遍历工作表1上的行，并为每一行创建类似于以下内容的sql字符串:

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

然后做

db.Execute thatString

PS:我的语法可能有误.并且在创建字符串时需要将单元格值转换为字符串.

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

这篇关于从Excel工作表数据更新Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！