Access Cookbook - Ken Getz [291]
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.
TIP
Although you can link SQL Server tables using ADOX, the SQL Server tables are then read-only in Access.
14.2. Dynamically Connect to SQL Server from an ADP
Problem
When you create a new ADP, you are prompted for connection information that is saved with the ADP. If you want to change it later, you need to choose File → Connection from the menu and manually input new connection information in the Data Link dialog. Since the users of your ADP may not know how to do that, they would be connecting to SQL Server using your security credentials, not their own. You'd like to create a project that automatically opens the Data Link dialog and prompts the users for their own connection information instead of displaying your connection information.
Solution
This solution involves creating an unbound ADP (an ADP that is not yet connected to a SQL Server database) and prompting the user to fill in the connection information by displaying the Data Link dialog.
Since connection information is saved with the ADP, you need to create a new ADP with no connection information.
Follow these steps to implement this functionality in your ADPs:
Create a new ADP by choosing File → New and clicking on Project (Existing Data), as shown in Figure 14-4.
Figure 14-4. Creating a new ADP
Designate a location for the new project when prompted. When the Connection dialog opens, press Cancel. Do not fill in any connection information.
You will now have an empty project. You want to create a startup form like the one shown in Figure 14-5.
Figure 14-5. The startup form for the ADP
This form has a button that allows users to change their connection information. However, you want to prompt them to connect to the SQL Server database the first time they connect, so place the following code in the Form_Load event:
Private Sub Form_Load( )
If Not CurrentProject.IsConnected Then
DoCmd.RunCommand