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

You can review part one here. Part three here. Part four here.

MS Access Consulting

In last week’s post I talked about using a single table in your SQL Server database to easily manage security, in today’s post I’m going to take it one step further and use that same table to create all the necessary links needed for your app on startup.

Use ADODB to loop through table and create links
The technique uses an ADODB recordset to loop through all the records in tblTablePermissions and create the links on startup. We store the user’s name and password with the link, so we use a custom login form to store the user’s name and password for the session, which are then used to create the links.

Step 1: Trap user’s login info and test to see if they can connect with SQL Server
Here’s the code we use when the user clicks the login button on our login form:


Change between Beta and Production Databases
By including the name “Beta” in the project name the code will point to the Beta database instead of the production one. I’ll be elaborating more on this later in the series.


Notice the use of OpenMyConnection, which I use in my Easy ADODB methods. Here is the definition of the function:


RelinkAllTablesADOX Procedure
This procedure is where the fireworks go off:

Only link tables where DontLink = 0
Notice in Line 30 above we only link tables that have DontLink set t False. You may wonder why we even bother placing a table in tblTablePermissions when it’s not used in Access as a linked table? Remember in my first post we use it for setting security of all tables, and if there is ever a need to use ADODB and go directly to the table in SQL Server via our Access code or pass-through query then we need that table listed in tblTablePermissions.

Does view need index?
In line 50 above the code checks if the table being linked is a view, and if so, creates the index in Access using a local table called tblLinkViews. You could probably do away with this concept in your code and just add another column to tblTablePermissions called ViewIndex, then just use !ViewIndex in the code above to read the SQL syntax into strSQL.

Rename SQL Server Table?
Notice in line 35 above the system will actually use a different name for the Access table if so specified. This technique can come in handy when your dealing with a legacy app and wish to use names in SQL Server that better suit you but can’t be used in Access due to legacy code.

AttachDSNLess Table Code
The above procedure calls this sub to actually do the linking:


In my next post I’ll discuss how to destroy the links when you exit the application.