Author note: This is part 1 of a series on DSN-less tables in Access.

You can review part two here. Take a look at part three here and part four here.

In my first post on this topic, I sent you to Doug Steele’s great article on how to do DSN-less table connections. Today I’m starting a new series of post on this topic that will cover the following:

  • Using a SQL Server table to drive the process of creating DSN-less tables in Access. As an added benefit we will use the same table to setup security for tables.
  • Code in Access that will cycle through the SQL Server table and create the links.
  • Code in Access to destroy the links before exciting the application.
  • Switching between Beta and production servers with ease.

Why Destroy The Links?
Our firm always uses DSN-less tables; by avoiding a DSN at each workstation at the client site, we simplify our deployment. It works well in a corporate environment since we use a trusted connection, meaning your Windows password and username are not included in the table link. If you grab the application and put it on another workstation that user’s credentials are used instead, not the original users.

But if the application is using SQL Server over the web, (data in the cloud), we’re  using SQL Server security, where we prefer to control the login process using an Access form as opposed to letting Access handle it with a cryptic message. Once the user is authenticated through our code, we proceed to link all of the tables using a single SQL Server table, which saves the username and password with the table links behind the scenes. We destroy the links when the user exits the app so that it can’t be used by unauthorized users.

It takes time to recreate all of the links on startup, so managing user expectations is essential. In general, people don’t mind waiting a little bit on program startup, in particular if it’s good for securing their data

Don’t rely on your table being there…
One benefit of this approach is not worrying if the user, (or the programmer), deletes a linked table while using the app. The system will simply recreate the link again on startup.

One table to rule them all: tblTablePermissions
tblTablePermissions in SQL Server has a dual purpose: set permissions for all other SQL Server tables and drive the linking process in Access.

One of the best features of SQL Server is its ability to secure data, but it can be a chore to maintain and a hassle to setup, the more tables your app has the longer it can take to manually setup security. With tblTablePermissions and usp_RunPermissions we can easily add a table or view to the database and quickly redo our security schema by running usp_RunPermissions.

Three security roles: Admins, Employees and Clients

The app where I designed this technique had three roles in SQL Server:

  • Admin: Members in this role get all rights to every table listed in tblTablePermissions
  • Employee: Read rights to all tables and selective rights based on which columns are set to True in UpdateEmployee, DeleteEmployee or InsertEmployee.
  • Client: The app I designed with this security schema also had clients logging in to mostly view data and only modify certain tables, such as tblNotes. For the majority of tables they get read on nothing at all.
Setting up security is a breeze…
Just add all your table names to tblTablePermissions and set any columns to True as needed. Then run usp_RunPermissions, which in turn will execute three other procedures: usp_GrantAdmin, usp_GrantEmployee and usp_GrantClient. Each of them will setup security on each table based on which column is set to True. Note: Change each procedure as needed to the role name you have in your SQL Server database. In my database they were Admin, Employee and Client but yours may differ.
Don’t forget to setup read only on tblTablePermissions for all user groups.

Next week : Code in Access to read tblTablePermissions and setup table links!