Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2017, 03:11 AM   #1
IvanM
Newly Registered User
 
Join Date: Jul 2016
Location: Berkshire
Posts: 20
Thanks: 14
Thanked 0 Times in 0 Posts
IvanM is on a distinguished road
Access VBA Import from Excel to Azure SQL Server

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

IvanM is offline   Reply With Quote
Old 08-15-2017, 04:35 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,932
Thanks: 0
Thanked 644 Times in 629 Posts
Ranman256 will become famous soon enough
Re: Access VBA Import from Excel to Azure SQL Server

normally, youd attached the excel file as an external table
then run an append query.

Does this not work?
Ranman256 is offline   Reply With Quote
Old 08-15-2017, 04:43 AM   #3
IvanM
Newly Registered User
 
Join Date: Jul 2016
Location: Berkshire
Posts: 20
Thanks: 14
Thanked 0 Times in 0 Posts
IvanM is on a distinguished road
Re: Access VBA Import from Excel to Azure SQL Server

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.

IvanM is offline   Reply With Quote
Reply

Tags
access , azure sql database , excel , import

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Project Nami: WordPress for Azure SQL Database/SQL Server Rx_ Web Design and Development 0 04-06-2016 05:14 AM
Import Linked SQL Server Data into Excel - convert text to numeric Rx_ Code Repository 0 07-03-2013 11:22 AM
Import data from Excel into SQL server 2005 sven2 SQL Server 1 10-08-2009 06:05 AM
import to sql server from excel nisha Modules & VBA 0 10-06-2009 02:20 AM
import to sql server from excel nisha Forms 0 10-05-2009 09:37 PM




All times are GMT -8. The time now is 08:04 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World