Exporting to SQL Server (1 Viewer)

CedarTree

Registered User.
Local time
Today, 19:40
Joined
Mar 2, 2018
Messages
404
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?)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:40
Joined
Aug 30, 2003
Messages
36,118
Simplest would be if you can link to the SQL Server table(s) in Access. Then it's a simple append query.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:40
Joined
Jan 20, 2009
Messages
12,849
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

Super Moderator
Staff member
Local time
Tomorrow, 10:40
Joined
Jan 20, 2009
Messages
12,849
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
 

CedarTree

Registered User.
Local time
Today, 19:40
Joined
Mar 2, 2018
Messages
404
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.
 

mjdemaris

Working on it...
Local time
Today, 16:40
Joined
Jul 9, 2015
Messages
424
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.
 
Last edited:

Users who are viewing this thread

Top Bottom