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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-16-2019, 05:37 PM   #1
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 138
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Exporting to SQL Server

Hello,

Looking for a easy / repeatable way of exporting data from Access to SQL server.

I can manually export an Access table to CSV say, and then use SQL Import tool to bring things into SQL server, but the SQL Import tool doesn't let you easily save preferences, layouts, etc. At least not in a user-friendly way. Has anyone done something in VBA that could help? Other suggestions (maybe 3rd party import tools?)

CedarTree is offline   Reply With Quote
Old 07-16-2019, 05:53 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,964
Thanks: 13
Thanked 4,055 Times in 3,991 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Exporting to SQL Server

Simplest would be if you can link to the SQL Server table(s) in Access. Then it's a simple append query.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 07-16-2019, 07:20 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,563
Thanks: 88
Thanked 1,479 Times in 1,395 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Exporting to SQL Server

Quote:
Originally Posted by pbaldy View Post
Simplest would be if you can link to the SQL Server table(s) in Access. Then it's a simple append query.
Definitely works but it is incredibly slow if there are large numbers of records.

Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
CedarTree (07-17-2019)
Old 07-16-2019, 07:38 PM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,563
Thanks: 88
Thanked 1,479 Times in 1,395 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Exporting to SQL Server

Quote:
Originally Posted by CedarTree View Post
I can manually export an Access table to CSV say, and then use SQL Import tool to bring things into SQL server, but the SQL Import tool doesn't let you easily save preferences, layouts, etc.
SSIS (SQL Server Integration Services) can save import specifications. It can read from many sources (including an Access database) and automate the whole process
Galaxiom is offline   Reply With Quote
Old 07-16-2019, 08:32 PM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,964
Thanks: 13
Thanked 4,055 Times in 3,991 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Exporting to SQL Server

Quote:
Originally Posted by Galaxiom View Post
Definitely works but it is incredibly slow if there are large numbers of records.
I did say simplest.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 07-17-2019, 11:34 AM   #6
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 138
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Exporting to SQL Server

Agreed. Simplest. And slowest (there's always a price to pay). But thanks.
I haven't really figured out SSIS. Is there a user friendly front-end for that? Preferably open source.
CedarTree is offline   Reply With Quote
Old 07-17-2019, 11:41 AM   #7
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: Exporting to SQL Server

What is your front end going to be to connect to SQL Server?

I have been using Excel for import/export, and that can be done via linked tables and VBA. The main issues I have had to deal with is the data itself - spelling errors, spelling variations (which means duplicate records in some cases), and if I'm trying to keep everything linked, the ID field numbers won't always match up with the new ID's in SQL Server, so I've had to create a quasi-random code to link the records to export/import.

So, it's not automated yet, but I hope to get there...

And, I'm with you on SSIS, barely getting my toes wet.


__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard

Last edited by mjdemaris; 07-17-2019 at 11:46 AM.
mjdemaris is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting to SQL Server ECEK SQL Server 2 01-15-2019 06:24 AM
Link to SQL Server with ODBC without risking original data in SQL Server? David Ball General 1 08-21-2015 07:59 PM
Exporting Access Web Reports on Sharepoint server to Excel smccoy Reports 1 12-28-2012 10:27 AM
Exporting to Word and Remote Server Unavailable Error Starman Modules & VBA 5 08-29-2008 12:14 PM
[SOLVED] Need help exporting to SQL server usvijay Modules & VBA 5 11-28-2001 02:00 PM




All times are GMT -8. The time now is 02:12 PM.


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

Featured Forum post


Sponsored Links


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