sql - 将数据从 Excel 插入或更新到 Access

Question

我有 800 到 1000 个 Excel 文件要上传到 Access。这些文件有特定的表格和6-7个表格，由不同的管理者在不同的时间填写。这些文件中的数据可能会不时更新或更改。

我有两个想法:

我最想要的是在这个 Excel 工作表中创建一个 VBA 宏，当经理填写所有数据时，它会在关闭时自动运行并将数据从 Excel 上传到 Access。所以我总是在 Access 中有最新的数据。
在这种情况下，我需要使用 ADO，因为我不知道是否有任何管理员在他们的 PC 上安装了 MS Access 以使用 DAO。

第二个 - 我从 Access 运行宏，它在准备好时读取所有 800-1000 个文件。在这种情况下，我可以使用 DAO 和 ADO。但我不想一个人完成这项工作。

我有第一种情况的测试代码。

Sub TestUpload()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\User\Desktop\TestDataBase.accdb;"
rs.Open "TestDB", cn, adOpenStatic, adLockOptimistic, adCmdTable

Dim contractSAP As String
contractSAP = Sheets("TestUpload").Range("A1").Value
Dim contractASU As String
contractASU = Sheets("TestUpload").Range("B1").Value

Dim strSqlSelect As String
Dim strSqlInsert As String
Dim strSqlUpdate As String

strSqlSelect = "SELECT 1 FROM TestDB WHERE ContractNumSAP = '" & contractSAP & "'"
strSqlInsert = "INSERT INTO TestDB (ContractNumSAP, ContractNumASU) VALUES ('" & contractSAP & "', '" & contractASU & "')"
strSqlUpdate = "UPDATE TestDB SET ContractNumASU = '" & contractASU & "' WHERE ContractNumSAP = '" & contractSAP & "'"

cn.Execute (strSqlInsert)

End Sub

我有一些问题。

1) 如果两个或多个管理器同时将数据上传到同一个表，是否会发生冲突？记录由主键区分。

2)在Access中检查表中是否已有记录的最佳方法是什么？

3) 上传或插入的最佳方式？

4) 有没有办法通过 ADO 将整个范围/表中的记录从 Excel 插入/更新到 Access，或者我是否需要检查循环中每一行的记录是否存在？

5) 有没有一种方法可以使用 SQL 从 Excel 工作表中选择数据并在同一个 SQL 查询中插入/更新它们，而不是像现在一样使用 Cell 逐条寻址？

Answer 1

使用命令DoCmd.TransferSpreadsheet在 Access 中，以便将 excel 工作表作为表格链接到 access 中。然后您可以像常规 Access 表一样查询这些表。

1) 如果您从 Access 中提取文件，则不会与同时上传的管理员发生冲突。

2)每条记录都需要一个唯一标识它的主键。这可以是一列或多列的组合。您可以像这样更新和插入记录(使用查询，不要使用循环):

首先更新现有记录(假设 ContractNumSAP 是您的 PK，并且您要更新 ContractNumASU ):

UPDATE
    MyAccessTable A
    INNER JOIN MyLinkedExcelTable X
        ON A.ContractNumSAP = X.ContractNumSAP
SET A.ContractNumASU = X.ContractNumASU

然后插入缺失的:

INSERT INTO MyAccessTable (ContractNumSAP, ContractNumASU)
SELECT ContractNumSAP, ContractNumASU
FROM MyLinkedExcelTable X
WHERE X.ContractNumSAP NOT IN (SELECT ContractNumSAP FROM MyAccessTable)