[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Automatically Export Data from SQL Server to Access

Posted on 2007-10-01
10
Medium Priority
?
407 Views
Last Modified: 2008-01-09
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

Background:
     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.
0
Comment
Question by:Jon Bredensteiner
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 16

Assisted Solution

by:SQL_SERVER_DBA
SQL_SERVER_DBA earned 200 total points
ID: 19992899
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.
0
 
LVL 51

Assisted Solution

by:Ted Bouskill
Ted Bouskill earned 200 total points
ID: 19992930
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.
0
 
LVL 5

Accepted Solution

by:
nicolasdiogo earned 1600 total points
ID: 19994034
hi,

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
http://www.sqldts.com/107.aspx



0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 19994180
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?
0
 
LVL 5

Expert Comment

by:nicolasdiogo
ID: 19994217
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.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 19994698
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?
0
 

Author Comment

by:Jon Bredensteiner
ID: 19994782
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
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 19995382
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.
0
 
LVL 5

Expert Comment

by:nicolasdiogo
ID: 19997051
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.
0
 

Author Comment

by:Jon Bredensteiner
ID: 19999186
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question