Best way to export from SQL Server to Access

Question

Best way to export from SQL Server to Access

ibbo14

Old Hand

Points: 392
More actions
October 25, 2022 at 2:20 am

#4103960

Hi there!
I have always been importing from MS Access to SQL, but in this case I have a client wanting to export data from SQL server into an MS Access DB for distribution to external consultants.
I have a few views which I will be calling from, but looking for an easy way to export into access by using a Select * into approach into a blank access DB. Is this possible or is there a better way of achieving this?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

you could let a user connect to SQL Server from Access and import a bunch of data from stored procedures, views, or tables (or or or), or let someone with permissions already do it and then just hand it over ( drop into a network folder ). Depends if it's a one-off or has t obe redone periodically. Querying SQL Server (creating a view or stored procedure) is pretty easy, and then the Access users can connect to those and grab data.

ibbo14 Old Hand Points: 392 More actions · Answer 2

Thanks for your reply.

The users will not have access and this is something I would like to Automate without having to open access and run it daily as a job.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Then create the queries you need in your SQL database, and then use SSIS to run the queries and output the data to whatever format and location you want. then schedule them to run.

ibbo14 Old Hand Points: 392 More actions · Answer 4

OK, SSIS is the way to go then. I'll give that a go. Thanks pietlinden!