Recipe 14.1 Dynamically Link SQL Server Tables at Runtime

14.1.1 Problem

Your Access SQL Server database uses linked tables and views in SQL Server. You have set up security and permissions in SQL Server and want to make sure that each user's linked tables are attached under their own permissions, not another user's permissions. In addition, you don't want the users to be prompted for an additional login ID and password each time they use a table.

14.1.2 Solution

If you link SQL Server tables from an Access database using the File Get External Data menu commands, you will be prompted to use or create a Data Source Name (DSN). The main drawback to DSNs is that they need to be installed on every user's machine. A better solution is to use VBA code to link or relink tables. You can supply connection information in the Connection string without having to create a DSN.

This technique uses DAO to create new TableDef objects in each database when the application starts up. The startup form for the application has a dialog where the user can supply a login and password to be used to connect to SQL Server. The list of table names is stored in a local Access ( Jet) database.

To add this technique to your application, follow these steps:

Create a table to hold the names and properties of the SQL Server tables to which your application will link. In the 14-01.MDB sample database, the local table is named tblSQLTables. The column definitions are listed in Table 14-1.

Table 14-1. Column definitions for tblSQLTables
Column name	Data type	Primary key?	Required?
SQLTable	Text 50	Yes	Yes
SQLDatabase	Text 50	No	Yes
SQLServer	Text 50	No	Yes

Enter data in the table. Figure 14-1 shows the datasheet view of the table used to store data about the tables that are linked from the Northwind database on the local SQL Server.

Figure 14-1. tblSQLTables has entries to link to the tables in the Northwind database

Create the startup form. The example shown in Figure 14-2 uses an option group to determine whether integrated security (Windows XP, Windows 2000, or Windows NT authentication) or a SQL Server login and password is being used. If a SQL Server login is selected, users can enter their logins and passwords in the text boxes.

Figure 14-2. The startup form allows users to supply login information for the linked tables

Once you've created the form and the necessary controls, you'll need to write the code to set up the links. In design view, select the OnClick event of the Connect command button and choose Event Procedure. This will open the VBA code window.
You'll need to set a reference to the DAO 3.6 Object Library by choosing Tools References... and checking the Microsoft DAO 3.6 Object Library, as shown in Figure 14-3.

Figure 14-3. Setting a reference to the DAO object library

Here's the complete code listing for the cmdConnect_Click event procedure:

Private Sub cmdConnect_Click( )
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim strServer As String
    Dim strDB As String
    Dim strTable As String
    Dim strConnect As String
    Dim strMsg As String
    
On Error GoTo HandleErr

    ' Build base authentication strings.
    Select Case Me.optAuthentication
        ' Windows/NT login
        Case 1
           strConnect = "ODBC;Driver={SQL Server};Trusted_Connection=Yes;"
        ' SQL Server login
        Case 2
           strConnect = "ODBC;Driver={SQL Server};UID=" _
            & Me.txtUser & ";PWD=" & Me.txtPwd & ";"
    End Select
    
    ' Get rid of any old links.
    Call DeleteLinks
    
    ' Create a recordset to obtain server object names.
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
    If rst.EOF Then
        strMsg = "There are no tables listed in tblSQLTables."
        GoTo ExitHere
    End If
    
    ' Walk through the recordset and create the links.
    Do Until rst.EOF
        strServer = rst!SQLServer
        strDB = rst!SQLDatabase
        strTable = rst!SQLTable
        ' Create a new TableDef object.
        Set tdf = db.CreateTableDef(strTable)
        ' Set the Connect property to establish the link.
        tdf.Connect = strConnect & _
            "Server=" & strServer & _
            ";Database=" & strDB & ";"
        tdf.SourceTableName = strTable
        ' Append to the database's TableDefs collection.
        db.TableDefs.Append tdf
        rst.MoveNext
    Loop
    
    strMsg = "Tables linked successfully."
    
    rst.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing
    
ExitHere:
    MsgBox strMsg, , "Link SQL Tables"
    Exit Sub

HandleErr:
    Select Case Err
        Case Else
            strMsg = Err & ": " & Err.Description
            Resume ExitHere
    End Select
End Sub

The completed application is shown in 14-01.MDB, which contains the local table used to store data about the tables that are linked from the Northwind SQL Server database. A startup form contains the relinking code.

14.1.3 Discussion

The first step in linking SQL Server tables is to build the ODBC Connection string that will be used to link the tables. You could use a DSN, but you'd have to create the DSN if it didn't exist. We find it easier to simply build a dynamic string with all the required information. The first part of the string contains connection information that will be the same for every table:

Select Case Me.optAuthentication
    ' Windows/NT login
    Case 1
       strConnect = "ODBC;Driver={SQL Server};Trusted_Connection=Yes;"
    ' SQL Server login
    Case 2
       strConnect = "ODBC;Driver={SQL Server};UID=" _
        & Me.txtUser & ";PWD=" & Me.txtPwd & ";"
End Select

The next step is to delete any old linked SQL Server tables by calling the DeleteLinks procedure:

Call DeleteLinks

The DeleteLinks procedure walks through the current database's TableDefs collection, deleting only linked ODBC tables. Here's the complete listing:

Private Sub DeleteLinks( )
    ' Delete any leftover linked tables from a previous session.
    
    Dim tdf As DAO.TableDef
    
    On Error GoTo HandleErr
    For Each tdf In CurrentDb.TableDefs
        With tdf
        ' Delete only SQL Server tables.
            If (.Attributes And dbAttachedODBC)  = dbAttachedODBC Then
                CurrentDb.Execute "DROP TABLE [" & tdf.Name & "]"
            End If
        End With
    Next tdf
    
ExitHere:
    Set tdf = Nothing
    Exit Sub

HandleErr:
    MsgBox Err & ": " & Err.Description, , "Error in DeleteLinks( )"
    Resume ExitHere
    Resume
End Sub

The next step is to create a recordset that lists the table names, the SQL Server database name, and the SQL Server itself. If no tables are listed, the procedure terminates. This portion of code is as follows:

Set db = CurrentDb
Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If rst.EOF Then
    strMsg = "There are no tables listed in tblSQLTables."
    GoTo ExitHere
End If

Next, walk through the recordset, creating a new TableDef object for each table listed. The Connect property is set to the base connection string, with the server and database name concatenated. The TableDef object's SourceTableName is set to the table name in the database, and the TableDef object is appended to the TableDefs collection. This portion of code resides in the following Do Until loop:

Do Until rst.EOF
    strServer = rst!SQLServer
    strDB = rst!SQLDatabase
    strTable = rst!SQLTable
    ' Create a new TableDef object.
    Set tdf = db.CreateTableDef(strTable)
    ' Set the Connect property to establish the link.
    tdf.Connect = strConnect & _
        "Server=" & strServer & _
        ";Database=" & strDB & ";"
    tdf.SourceTableName = strTable
    ' Append to the database's TableDefs collection.
    db.TableDefs.Append tdf
    rst.MoveNext
Loop

Once the TableDefs are appended, the cleanup code runs and the user is notified that the tables have been successfully linked:

    strMsg = "Tables linked successfully."
    
    rst.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing
    
ExitHere:
    MsgBox strMsg, , "Link SQL Tables"
    Exit Sub

The technique discussed here for relinking tables works well in any version of SQL Server and is not specific to any version of Access. Any time you use DAO in your code, you need to open the Tools References... dialog in the Visual Basic editor and make sure that a reference is set for the Microsoft DAO library: the version of DAO used in Access 2000 or later is 3.6.