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...
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...