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.
Next week : Code in Access to read tblTablePermissions and setup table links!
Thanks a bunch for this amazing design. One thing I noticed that doesn’t work out is the three created roles (Admin, Employee and Client) can’t execute the stp_RunPermissions stored procedure because in SQL Server only a db_owner of the sysadmin of the entire server has the right to grant, deny or revoke permissions. Thus in my Access Front End when myusers log on they can’t execute this. Any ideas on how to give them these rights without at the same time giving the control of the database? Still don’t want these roles to create or alter procedures and functions?
Thanks for a great and very helpful article.
One question though, what are the Fields (Names) in the SQL Table tblTablePermissions. I could not find any explicit definition of the table’s fields. (Or am i missing something?)
I am using DSN-less connections for my Access 2010 ODBC but have noticed that if I link to a SQL view it does not show up in my Access tabledefs or querydefs collections so the “Fix Connections” code from Doug Steele doesn’t work. How do I programmatically change the connection string for a linked SQL view?
Thanks for reaching out. Views from SQL Server should show up just like a linked Access table so I’m at a loss why it would not show up in your tabledefs collection. When you first linked the view did you see the view as a linked table? If not, try linking manually and once you see the view in your list of tables it should refresh using Doug’s code.
Please help me understand. I am trying to use this method you have provided, to make a more secure MS Access database when linking to MS SQL Server hosted in the cloud. However, once I link tables using this method, I am able to simply open a blank database and import all the linked tables into the blank database within seconds. All I have to do is keep the database open which contains the linked tables while doing the import. I then have access to all data, no questions asked. I MUST be missing something here. Please help me out. I greatly appreciate it.
Make sure you are using Ben’s technique to not store password with the linked tables as discussed here:
If you want to make your database more secure, please create an accde version of your file or even better accdr and disable access to special keys. Users will not be able to bypass your startup code if you also disable the startup bypass key via a new database property, finally, provide a menu for users and don’t provide them direct access to tables.
Juan, have you been able to accomplish dsn-less linked tables with sql azure from Access 2013? I receive a message of permission denied.
The connection is working since a dsn one, gets the job done.
Are you trying to do this using ADO? If so, that will not work because Azure no longer supports ADO. Use DAO instead. You can adapt code from Doug J. Steele’s DNS-less connection to do the relinks.
Merci milles fois
Thanks a lot.