2

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?

Andre
  • 26,751
  • 7
  • 36
  • 80
m.falconelli
  • 61
  • 11
  • The 10-rows-at-a-time is for fetching data from Sql Server into Access, not for inserting. – Andre Jul 18 '18 at 09:18
  • Possible duplicate of [How to increase performance for bulk INSERTs to ODBC linked tables in Access?](https://stackoverflow.com/questions/25863473/how-to-increase-performance-for-bulk-inserts-to-odbc-linked-tables-in-access) – Andre Jul 18 '18 at 09:18
  • (While the question is about Oracle, the answer is for SQL Server.) -- See also https://stackoverflow.com/questions/32759978/how-to-increase-performance-for-bulk-inserts-to-odbc-linked-tables-in-access-for – Andre Jul 18 '18 at 09:19
  • Why use Access tables at all? Whatever you do with the access table can be done with a second SQL table linked to access, assuming it is not possible to do what you need with just a single table. – SunKnight0 Jul 18 '18 at 19:34
  • Hi, because I cannot import excel files into SQL Sever as functions like OPENDATASOURCE or OPENROWSET are blocked.then for same simple operation Access is strangely faster. – m.falconelli Jul 19 '18 at 07:52

1 Answers1

4

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

ASH
  • 20,759
  • 19
  • 87
  • 200