Microsoft Access for SQL Server Reporting

I like to use Microsoft Access for end-user SQL Server Reporting. My business users love Excel and Access is one of the easiest ways to provide SQL views in spreadsheet-compatible form. This is related to my earlier post Microsoft Access Linked Table Notes.

One concern with providing SQL Server data through MS Access is the possibility of a privileged database user accidentally changing data in data-sheet view. Queries in Access are Editable by default if Access has enough key information to uniquely identify each record. To prevent the possibility of accidental data modifications to data-sheet view queries in Access, I recommend the use of Pass-Through queries with a File Data Source with Integrated Windows Authentication (Trusted Connection) for portable DSN-less data access. The pass-through functionality in Access makes the data-sheet view read-only (a very useful side-effect for spreadsheet reporting). If you want to provide a data-sheet copy of the current pass-through data that can be modified, I recommend you create a Macro or VBA Module that imports/refreshes a second Access Table. This can be accomplished by deleting all records and then updating the table with fresh data, or by deleting or renaming the table and then re-importing a fresh table. As a native Access table, the copy can be modified by the user safely without any harm to to original SQL Data protected from MS Access modification by the handy pass-through query feature.

In addition to being read-only, the SQL-only setup of a pass-through query adds another level of abstraction to help prevent casual browsing of your database server tables. Because the query is “passed” directly to your database server, you can use native SQL Server T-SQL code that is not directly supported in standard Access queries.


About notesbytom

Keeping technology notes on to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s