I have a customer who wants to run all their reporting in Access but they need data from my SQL Server database and I need to be able to filter what data they see by a few parameters. The data needs to be updated frequently, what is the best way to handle this.
Add a comment
|
2 Answers
You could create a login with read permissions on your prod db, or a login with read permission on "publication db" where you would copy the visible info nightly.
Your question needs to be more precise in terms of timing (real time info or not) and granularity (all details or summarized data), at least.
2 Comments
Slee
it would be real real time, say every minute or so - so I guess I could create a DB that gets populated by a SP every minute. So say I then have that done - how do I let Access see that data?
Patrick Honorez
It makes little sense to populate another db every minute. If it's big, it's impossible, if it's not big, it's not worth it. So just create linked tables in your Access db, pointing to your prod db, using a read only account. Eventually just give "them" access to a few views in which you filter the data they can see.
My suggestion would be to create Views that do any permanent filtering (that is, that filter what you allow the Access user to see). Then create a login with Read on those Views.
From the Access end, link to the Views with Get External Data (by whichever method applies to the Access version), for an ODBC Connection, which contains the login credentials if you're not using Integrated Security on the SQL Server. The Views show up as tables & the user can report to their heart's content.
