Go to Top

Part 2: Linking tables using a SQL Server table

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.

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.

OpenMyConnection

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.

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.

4 Responses to "Part 2: Linking tables using a SQL Server table"

  • G. Kant
    January 16, 2013 - 9:56 pm Reply

    I finally succesfully adopted your method of DSN-less linking but I ran into a couple of issues first (of course). For creating multi-column indexes on views I had to change the statement in RelinkAllTablesADOX to: strSQL = “Create Index IX_” & strTableName & ” On ” & strLocalTableName & ” (” & strField …

    • Ben Clothier
      January 17, 2013 - 2:48 am Reply

      Thank you – it should be using table’s name, not field’s name for the creation – the typo has been fixed.

  • Srdjan Vasiljevic
    May 29, 2012 - 7:53 pm Reply

    There is a typo

    30 strSQL = “Select * from tblTablePermissions Where DonLink = 0”

    should be

    DontLink

    Also, the code is great. Thanks a lot.

    • Juan Soto
      May 29, 2012 - 7:59 pm Reply

      Thanks for the tip! Article updated.

Leave a Reply

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

 

Contact Us
  • This field is for validation purposes and should be left unchanged.