Automatically Export Data from SQL Server to Access

Good morning,
     I would like to somehow create an automated process for SQL Server to export data to an Access DB; it can either update the table or overwrite the table with a new table, either way can work.  One of the people I work with told me that it might be possible to do this with DTS in SQL Server.  Is that true, and how do I do it?
     I am pretty much a beginner in SQL Server, and a intermediate user in Access (at least compared to the people on this forum), so please guide me through the process step by step.  I am one of the admins for the Server though...  Thanks in advance for your help, Jon

     I need to do this because I maintain an Access DB that pulls fresh data from a SQL Server DB on a daily basis.  The problem is that there are a lot of people using the Access DB, and I don't want to give all of them access to the SQL Server data, so I have to run an update query in Access just about every day to make sure that the data is current.  It would be a lot nicer if this was automated somehow; i.e. automated SQL Server export to Access at 1am every night.
Jon BredensteinerProject ManagerAsked:
Who is Participating?

one way to export your data daily without any security risks is by setting up a DTS package and scheduling to work every night.
you can use the wizard by right-clicking on the database name and selecting export:
choose your source table or enter a SQL query
then choose your destination, access DB, and your table (enter any username and password)
next choose if you are going to append, refersh data onto your target table
then choose to save the package and give it a mininful name.
At this point data will be transferred to your Access DB
Then go into Enterprise manager click on Packages choose your pakcage right-click it and choose to schedule it.

and you are done.

for more info have a look at this website, if you need further info just ask

use the SQL Server 2005 Import/Export Wizard and save the package as SSIS, you can even tell it to truncate or append and schedule.
Ted BouskillSenior Software DeveloperCommented:
I don't know how big your team is but here is how I would do it.

I'd setup a view of the data in SQL.  Give it a descriptive name like MsAccess[YourTable]View

I'd then setup an AD group group with members that are allowed to see the view.  I'd then assign 'SELECT' permissions for the view in SQL for the AD security group.

From there I'd then create an Access database with the following database connection:
Server=YourServer;Database=YourDatabase;Integrated Security=true;

Changing access to the database is as simple as changing members of the security group.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ted BouskillSenior Software DeveloperCommented:
How does exporting the data into a Access database using a DTS package eliminate security risk?  How do you control who has access the Access database file?
by having Access with username/pwd and using those details on DTS and then locking the package with username/pwd itself.
but in this case you are not giving access to MSSQL.
you are giving MSSQL rigths to export a small portion of data to a single target.
Ted BouskillSenior Software DeveloperCommented:
The author wants specific people to be able to load the MS Access file to view fresh data.  Permissions for the DTS package don't matter.  Once the DTS package has created an MS Access file how do you propose he restrict access to the access file?  OR, are you proposing that the user executes the DTS package themselves to generate the file?
Jon BredensteinerProject ManagerAuthor Commented:
The data really isn't that sensitive; plus, the Access database is in a share folder on a server for which I control access to.  The only people that can access the folder (database) are the people I have granted access.  I like the idea that tedbilly offered with setting up access to a single view on the SQL Server; however, that is another topic.  For this one I would like to figure out how to have SQL Server do the work for me, and I believe the solution has been explained, so I will try to test it tomorrow, and give you guys the points then.  Thanks again for the quick help, Jon
Ted BouskillSenior Software DeveloperCommented:
Hmm, so you can PUSH the data into the Access using a DTS package or PULL the data in using a linked table.  That is essentially your choices.

From a complexity point of view I'd go with the PULL.  The key difference about pulling the data is that the data is refreshed with live data when you open the Access database.  Pushing the data means it is only updated when you schedule the execution of the package.
i would rather use the PUSH.

by defining when data should be sent from MSSQL you can plan when to do your backups, if this data needs to be processed on the server, you can send it after it is completed.
instead of relying on someone manually doing it when you tell them: this more prone to errors.
Jon BredensteinerProject ManagerAuthor Commented:
I also like the push idea for the following main reason: I do not have to continually grant new people access to the SQL view.

I already have many ODBC linked tables in the Access DB, but I am the only person with access to open them; however, I will explore both avenues later today when I have the bandwidth to do so.  Thank you both for your help, Jon
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.