Access append to SQL Server - slow

[mahjohn]

Hello,
I've read several previous posts in the forum, but still don't have a solution for my problem.

I have a weekly excel sheet to upload to a SQL server table.Each week the table is cleared of all data and the new data uploaded. If I use MSSMS it takes about 6 mins (33,000 rows with SQL in AWS). If I try to use MS Access and import to a linked table,it is more than twice as slow. I've tried to import the file to a local table in Access and then an append query, still just as slow. I can use MSSMS, however, there are a few others that may use this process, and MSSMS introduces a lot of areas where people can miss a step or select something they shouldn't, and is why I'd like to have it run through a form button with a linked macro.

Why is Access so slow and anything I can do or am I stuck using MSSMS?

thanks

 

[Galaxiom]

You could investigate the techniques suggested here. Some of them only read text so you may have to convert the Excel file to csv.

I have used BCP (Bulk Copy Program). to export from SQL Server table and it was lightning fast.
Bulk Insert is similar to the SSIS process so I would expect it to be a similar speed.
OpenRowSet can read the Excel file directly.

 

[mahjohn]

You could investigate the techniques suggested here. Some of them only read text so you may have to convert the Excel file to csv.

I have used BCP (Bulk Copy Program). to export from SQL Server table and it was lightning fast.
Bulk Insert is similar to the SSIS process so I would expect it to be a similar speed.
OpenRowSet can read the Excel file directly.

Thank You, will give it a shot.