CODEWITHSUNDEEP
sql-serverexcelvbat-sqlms-access
m.falconelli
m.falconelli

Reputation: 61

How to speed up tables transfer between Access and SQL Server using VBA?

I am trying to move tables from access to SQL Server programmatically. I have some limitation in the system permissions, ie: I cannot use OPENDATASOURCE or OPENROWSET.

What I want to achieve is to transfer some table from Access to SQL Server and then work on that tables through vba (excel)/python and T-SQL.

The problem is in the timing that it is required to move the tables. My current process is:

  1. I work with vba macros, importing data from excel and making same transformation in access, to then import into the SQL Server
  2. destroy the table in the server: "DROP TABLE"
  3. re-importing the table with DoCmd.TransferDatabase

What I have notice is that the operation seems to be done based on a batch of rows and not directly. It is taking 1 minutes and half each 1000 rows. The same operation on Access it would have taken few seconds.

I understood that it is a specific way of SQL Server to use import by batches of 10 rows, probably to have more access on data: Micorsoft details

But in the above process I just want a copy the table from access to the SQL as fast as possible as then I would avoid cross platform links and I will perform operation only on the SQL Server.

Which would be the faster way to achieve this goal?

Upvotes: 2

Views: 2183

Answers (1)

ASH
ASH

Reputation: 20322

Why are functions like OPENDATASOURCE or OPENROWSET are blocked? Do you work in a bank?

I can't say for sure which solution is the absoute fastest, but you may want to consider exporting all Access tables as separate CSV files (or Excel files), and then run a small script to load each of those files into SQL Server.

Here is some VBA code that saves separate tables as separate files.

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

Now, you can very easily, and very quickly, load CSV files into SQL Server using Bulk Insert.

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/

Also, you may want to consider one of these options.

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

Upvotes: 4

Related Questions