Go to Top

DSN-less tables; a better way…

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

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

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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!

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

10 Responses to "DSN-less tables; a better way…"

  • Matias
    January 9, 2016 - 6:18 am Reply

    Dear Juan

    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?

  • Anton
    October 1, 2015 - 3:30 pm Reply

    Juan

    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?)

  • Laura Sass
    May 28, 2015 - 11:16 pm Reply

    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!

    • Juan Soto
      May 29, 2015 - 1:32 pm Reply

      Hi Laura!

      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.

      Thanks!
      Juan

  • Jim
    January 21, 2015 - 2:05 am Reply

    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.

    • Juan Soto
      January 22, 2015 - 8:42 am Reply

      Hi Jim,

      Make sure you are using Ben’s technique to not store password with the linked tables as discussed here:
      http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/

      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.

      Regards
      Juan

  • Ricardo
    July 24, 2014 - 2:02 am Reply

    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.

    • Ben Clothier
      July 30, 2014 - 1:16 am Reply

      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.

  • Dactari
    September 12, 2012 - 1:18 am Reply

    Merci milles fois
    Thanks a lot.

    • Juan Soto
      September 12, 2012 - 2:14 am Reply

      Your welcome!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]