Access Cookbook - Ken Getz [290]
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.
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