如何使用VBA加速Access和SQL Server之间的表传输

Question

我正在尝试以编程方式将表从SQL Server的访问权限中移动。我在系统权限方面有一些限制，即：我不能使用OPENDATASOURCE或OPENROWSET。

我想要实现的是将一些表从Access传输到SQL Server，然后通过vba(excel(/python和T-SQL处理这些表。

问题在于移动桌子所需的时间。我目前的流程是：

1. 我使用vba宏，从excel导入数据，并在访问中进行相同的转换，然后导入SQL Server
2. 销毁服务器中的表："DROP table">
3. 使用DoCmd.TransferDatabase重新导入表

我注意到，操作似乎是基于一批行而不是直接执行的。每1000行需要1分半钟。在Access上执行同样的操作需要几秒钟的时间。

我知道SQL Server使用批量导入10行是一种特定的方式，可能是为了对数据有更多的访问权限：Micorsoft详细介绍

但在上面的过程中，我只想尽快将表从访问SQL复制到SQL，这样我就可以避免跨平台链接，并且只在SQL Server上执行操作。

实现这一目标的更快方法是什么？

Answer 1

为什么像OPENDATASOURCE或OPENROWSET这样的函数被阻止？你在银行工作吗？

我不能确定哪种解决方案是最快的，但您可能需要考虑将所有Access表导出为单独的CSV文件(或Excel文件(，然后运行一个小脚本将每个文件加载到SQL Server中。

下面是一些VBA代码，可以将单独的表保存为单独的文件。

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If Left(obj.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , obj.Name, obj.Name & ".csv", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, obj.Name & ".xls", True
End If
Next obj

现在，您可以非常轻松、快速地使用Bulk Insert将CSV文件加载到SQL Server中。

Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
BULK
INSERT CSVTest
FROM 'c:csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

https://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

此外，您可能需要考虑其中一个选项。

https://www.online-tech-tips.com/ms-office-tips/ms-access-to-sql-database/

https://support.office.com/en-us/article/move-access-data-to-a-sql-server-database-by-using-the-upsizing-wizard-5d74c0df-c8cd-4867-8d07-e6e759d72924