Access VBA Import from Excel to Azure SQL Server (1 Viewer)

IvanM

Registered User
Joined
Jul 15, 2016
Messages
20
Hi all,

After a lot of searching, testing and hitting way too many brick walls, I thought I'd see if anyone is able to help -

Originally the below was an Access 2016 Front End and and Access 2016 Data Back End setup.

Current Setup
Access 2016 DB Front End (main tables linked to Azure SQL server BE)
Azure SQL Server Back End

Original Data formats (files which are imported)
Excel .XLSX
Excel .TXT (delimited)

Previously I was importing from these 2 data types into temporary tables using DoCmd.Transfer methods, then transforming the data before Update/Appending to main tables.

I need to find the best-fit method to import EITHER from the original source files (held on a network, not the local PC) into the Azure DB or via the Access front end.
e.g.
Import the .XLSX/.TXT files directly to a temp table in AZURE DB
or
Import the .XLSX/.TXT files into a table in the Access Front End and then import to Azure DB

I have tried several methods so far and seem to hit blockers for one reason or another whichever method I use.
Transferring from the Access local table to an Azure table takes forever due to the 1 record at a time problem.
When I attempt to connect to the source data via SQL using SSMS it seems to have an issue when I use the network location.
And so it continues...
I want to use VBA/T-SQL so I have some control using Access over the import process rather than automate using SSIS which I've succeeded importing with albeit with other issues...

Any help would be gratefully received...
 

Ranman256

Registered User
Joined
Apr 9, 2015
Messages
3,430
normally, youd attached the excel file as an external table
then run an append query.

Does this not work?
 

IvanM

Registered User
Joined
Jul 15, 2016
Messages
20
Hi,

thanks for the prompt reply.
My original solution was to import to a temp table in Access and then run an append query, but it's unbelievably slow, as in hours to append from the Access temp table to the Azure SQL table, there's around 300,000 records in the file I'm testing. That was using an Access query to append the records.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom