Link Read Only SQL Server Tables in Access

When you link tables in Access from SQL Server, the users are able to interact with them as though they were local tables in the .mdb file.  They can, provided they have permissions, modify the data and run queries against the table.  They can, unless you have completely locked down your .mdb file, open the table directly.  This can be more of a problem than with local or linked Access tables.

When a user opens a SQL Server linked table in Access, SQL Server will place page level locks on the table.  As the user navigates through the table, the page lock might be on different parts of the table.  If another user or any other process attempts to update data in the area that is locked, they will get a lock contention error.  This happens even if the user opens a query based on the SQL Server table.  If you are not expecting this error, it can be quite difficult to troubleshoot, because it comes and goes, and it isn’t obvious who is causing it.

I ran across one of these scenarios recently.  I discovered that many times the users were opening the table just to look around and do searches in ways that weren’t supported by the forms.  They were not actually updating the data during those times.  So I decided to create a non-locking, read-only view for them to browse and query.  Here are the steps to creating and linking the view to Access.

First, create a view on SQL Server.  In the simplest terms, a view is a query stored on SQL Server that appears as a table to other queries in SQL Server and to Access if we link it in.  In many circumstances, a view is updateable.  That is, you can insert, update, and delete with the view.  This is not true with stored procedures, which you can think of as more of a function call that may return a row set.

For this view, you can do a simple select statement, adding the WITH (NOLOCK) clause.

CREATE VIEW dbo.v_Inventory_NOLOCK
AS
SELECT * FROM Inventory WITH (NOLOCK)

This creates the view and tells SQL Server not to do any table locks when using it.  Note that this does not automatically make the view read-only.  There are still a couple more steps to do.  The view should have the SQL Server permissions set up so your users can select from the view, but not do any data manipulation.

Once the view is created, then you can link the view as a table in Access.  Open Access and choose File-->Get External Data-->Link Tables… from the main menu.  Choose ODBC Databases in the Files of Type combo box to bring up the ODBC Select Data Source dialog box.  Choose the appropriate DSN from this box.  Choose the view name in the Link Tables dialog box.  Now here is the most important part.  The next dialog box you see is labeled “Select Unique Record Identifier”.  This dialog box is asking you what column is the primary key for the table you are linking.  If you do not specify one, Access will still link the table, but you will not be able to change any data (which is what we wanted).  Click OK without selecting any of the fields in the list.

Now you have the view linked in as a table in Access.  It is read-only because you did not specify a primary key, and because of the permissions on SQL Server.  It does not lock the table because of the WITH (NOLOCK) clause in the select statement.  Users can play around with this ‘table’ all they want and it will not cause locking problems on SQL Server.