Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. 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.

2. 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

3. 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

4. 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.

5. 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

6. 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.

Although you can link SQL Server tables using ADOX, the SQL Server tables are then read-only in Access.