vba 将数据从 Excel 插入或更新到 Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45257324/
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
Insert or Update data from Excel to Access
提问by Invirtus
I have 800 to 1000 Excel files, to upload to Access. These files have specific forms and 6-7 tables, which will be filled in by different managers at different times. The data in these files could be updated or changed once in a while.
我有 800 到 1000 个 Excel 文件，要上传到 Access。这些文件有特定的表格和6-7张表格，由不同的经理在不同的时间填写。这些文件中的数据可能会不时更新或更改。
I have two ideas:
我有两个想法：
The one I want most is to make a VBA macro in this Excel sheet, which would autorun on close and upload data from Excel to Access, when a manager fills in all the data. So I always have up to date data in Access. In this case I need to use ADO, because I don't know if any of managers have or don't have MS Access installed on their PC in order to use DAO.
The second one - I run the macro from Access, which reads all 800-1000 files, when they are ready. In this case I can use DAO as well as ADO. But I don't want to do the job by my self.
我最想要的是在这个 Excel 工作表中制作一个 VBA 宏，当经理填写所有数据时，它会在关闭时自动运行并将数据从 Excel 上传到 Access。所以我在 Access 中总是有最新的数据。在这种情况下，我需要使用 ADO，因为我不知道是否有任何经理在他们的 PC 上安装或没有安装 MS Access 以便使用 DAO。
第二个 - 我从 Access 运行宏，当它们准备好时，它会读取所有 800-1000 个文件。在这种情况下，我可以使用 DAO 以及 ADO。但我不想独自完成这项工作。
I have this test code for the first case.
我有第一种情况的测试代码。
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
and I have some questions.
我有一些问题。
1) Could there be a conflict if two or more managers upload the data to the same tables simultaneously? The records are differentiated by Primary Key.
1) 如果两个或多个管理器同时将数据上传到同一个表，是否会发生冲突？记录由主键区分。
2) What is the best way to check if there is already a record in the table in Access?
2）检查Access中表中是否已经有记录的最佳方法是什么？
3) The best way to Upload or Insert?
3）上传或插入的最佳方式？
4) Is there a way, to insert/update records from Excel to Access via ADO by the whole Range/Table, or do I need to check if the record exists for every row in the loop?
4）有没有办法通过整个范围/表通过ADO从Excel插入/更新记录到Access，或者我是否需要检查循环中每一行的记录是否存在？
5) Is there a way to SELECT data from Excel sheet using SQL and insert/update them in the same SQL query not using Cell adressing one record by one, as I have now?
5) 有没有办法使用 SQL 从 Excel 工作表中选择数据并在同一个 SQL 查询中插入/更新它们，而不是像我现在一样使用 Cell 逐条记录？
回答by Olivier Jacot-Descombes
Use the command DoCmd.TransferSpreadsheetin Access in order to link the excel worksheets as tables into access. Then you can query these tables just like regular access tables.
在 Access 中使用命令DoCmd.TransferSpreadsheet将 Excel 工作表作为表格链接到 Access。然后您可以像常规访问表一样查询这些表。
1) If you pull the files from within Access, you won't have conflicts with managers uploading simultaneously.
1) 如果您从 Access 中提取文件，您将不会与同时上传的管理员发生冲突。
2) Each record needs a primary key which uniquely identifies it. This can be one column or a combination of columns. You can update and insert records like this (use queries, don't use loops):
2) 每条记录都需要一个唯一标识它的主键。这可以是一列或列的组合。您可以像这样更新和插入记录（使用查询，不要使用循环）：
First Update existing records (assuming ContractNumSAPis your PK, and you want to update ContractNumASU):
首先更新现有记录（假设ContractNumSAP是您的 PK，并且您要更新ContractNumASU）：
UPDATE
MyAccessTable A
INNER JOIN MyLinkedExcelTable X
ON A.ContractNumSAP = X.ContractNumSAP
SET A.ContractNumASU = X.ContractNumASU
Then Insert missing ones:
然后插入缺失的：
INSERT INTO MyAccessTable (ContractNumSAP, ContractNumASU)
SELECT ContractNumSAP, ContractNumASU
FROM MyLinkedExcelTable X
WHERE X.ContractNumSAP NOT IN (SELECT ContractNumSAP FROM MyAccessTable)
回答by ASH
Please see the following URL:
请查看以下网址：
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm
Post back with additional questions.
带着其他问题回帖。
