Automatically Export Data from SQL Server to Access
Posted on 2007-10-01
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.