Dynamically Link SQL Sever Tables at Runtime

[mattv10]

Hi,

I have the book called Access Cookbook. There is a section in there about dynamically connect SQL Tables to Access without using the File Get External Data menu commands(Linking Tables)..

So, I successfully implemented this on Access.. However, I have multiple databases and multiple servers to connect to. I was wondering if it was possible to connect to all of them at the same time.. They all have the same Logon and Pass which will probably make it possible to do this.

Here is the code if you would like to run through it...
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 old links, if any
Call DeleteLinks

' Create recordset to obtain server, database and table 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

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
' Only delete 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

Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Private Sub Form_Load()
Call DeleteLinks
End Sub

Private Sub optAuthentication_AfterUpdate()

Select Case Me.optAuthentication
Case 1 ' NT authentication
Me.txtUser.Visible = False
Me.txtPwd.Visible = False

Case 2 ' SQL server authentication
Me.txtUser.Visible = True
Me.txtPwd.Visible = True
End Select
End Sub

Thanks in advance!!!

 

[Pat Hartman]

The connection string is unique for each server so you would need to know which server each table needed to come from and change the connect string. This would be terribly inefficient.

Your database must take forever to open. What is the problem with leaving the tables permanently linked?