Author note: This is part 2 of a series on DSN-less tables in Access.

You can review part one here. Part three here. Part four here.

In last week’s post I talked about using a single table in your SQL Server database to easily manage security, in today’s post I’m going to take it one step further and use that same table to create all the necessary links needed for your app on startup.

Use ADODB to loop through table and create links
The technique uses an ADODB recordset to loop through all the records in tblTablePermissions and create the links on startup. We store the user’s name and password with the link, so we use a custom login form to store the user’s name and password for the session, which are then used to create the links.

Step 1: Trap user’s login info and test to see if they can connect with SQL Server
Here’s the code we use when the user clicks the login button on our login form:
Private Sub cmdLogin()
If IsNull(Me.txtUserID) Or IsNull(Me.txtPassword) Then
MsgBox "You must supply both your user name and password", vbInformation, "Can't Login"
Exit Sub
End If
'Store login credentials in memory
TempVars.Add "UserID", Me.txtUserID.Value
TempVars.Add "Password", Me.txtPassword.Value
If InStr(1, CurrentProject.Name, "Beta") > 0 Then
  TempVars.Add "IsBeta", True
Else
  TempVars.Add "IsBeta", False
End If
If Not OpenMyConnection Then
MsgBox "Connection to SQL Server failed, please verify your user name and/or password", vbInformation, "Login Failed"
Exit Sub
End If

RelinkAllTablesADOX
End Sub

 

Change between Beta and Production Databases
By including the name “Beta” in the project name the code will point to the Beta database instead of the production one. I’ll be elaborating more on this later in the series.

OpenMyConnection

Notice the use of OpenMyConnection, which I use in my Easy ADODB methods. Here is the definition of the function:
Public Function OpenMyConnection() As Boolean
On Error GoTo OpenMyConnection_Error

10 If con.State = adStateOpen Then
20 con.Close
30 End If
40 con.ConnectionString = conConnection & "User ID =" & TempVars!UserID & ";Password=" & TempVars!Password
50 con.Open
60 If Not con.State = adStateOpen Then
70 OpenMyConnection = False
80 Else
90 OpenMyConnection = True
100 End If
On Error GoTo 0
Exit Function
OpenMyConnection_Error:
MsgBox "Error " & Err.Number & " Line " & Erl & " (" & Err.Description & ") in procedure OpenMyConnection of Module mdlConnect"
End Function

 

RelinkAllTablesADOX Procedure
This procedure is where the fireworks go off:
Function RelinkAllTablesADOX() As Boolean
Dim cat As Object
Dim tbl As Object
Dim fLink As Boolean
Dim strSQL As String
Dim strTableName As String
Dim strField As String
Dim strDriver As String
Dim strLocalTableName As String
Dim rs As ADODB.Recordset
Dim strCatalog As String
Const conCatalog As String = "DataBE"
Const conServer As String = "ServerName"
On Error GoTo HandleErr
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection
strDriver = "SQL Server"

For Each tbl In cat.Tables
With tbl
' Delete any linked tables
If .Type = "PASS-THROUGH" Then
CurrentDb.TableDefs.Delete .Name
End If
End With
Next tbl

30 strSQL = "Select * from tblTablePermissions Where DontLink = 0"
OpenMyRecordset rs, strSQL
If TempVars!IsBeta Then
strCatalog = conCatalog & "Beta"
Else
strCatalog = conCatalog
End If
With rs
Do While .EOF = False
strTableName = !Table_Name
35 If IsNull(!Access_Name) Then
strLocalTableName = strTableName
Else
strLocalTableName = !Access_Name
End If
40 fLink = AttachDSNLessTable(strLocalTableName, strTableName, conServer, strCatalog, strDriver, _
TempVars!UserID, TempVars!Password)

50 'Is it a view? If so check if index needed
If Left(strTableName, 2) = "vw" And fLink Then
strField = Nz(DLookup("KeyField", "tblLinkViews", "[View] = '" & strTableName & "'"), "")
If Not strField = "" Then
strSQL = "Create Index IX_" & strLocalTableName & " On " & strLocalTableName & " (" & strField & ") WITH PRIMARY"
CurrentDb.Execute strSQL
End If
End If
.MoveNext
Loop
End With
RelinkAllTablesADOX = fLink

ExitHere:
Set cat = Nothing
Exit Function

HandleErr:
RelinkAllTablesADOX = False
If Not fLink Then
'MsgBox "Linking failed, please contact tech support.", vbInformation, "Error Linking Table"
Resume ExitHere
End If
MsgBox _
Prompt:=Err & ": " & Err.Description, _ Title:="Error in RelinkAllTablesADOX"
Resume ExitHere
End Function

Only link tables where DontLink = 0
Notice in Line 30 above we only link tables that have DontLink set t False. You may wonder why we even bother placing a table in tblTablePermissions when it’s not used in Access as a linked table? Remember in my first post we use it for setting security of all tables, and if there is ever a need to use ADODB and go directly to the table in SQL Server via our Access code or pass-through query then we need that table listed in tblTablePermissions.

Does view need index?
In line 50 above the code checks if the table being linked is a view, and if so, creates the index in Access using a local table called tblLinkViews. You could probably do away with this concept in your code and just add another column to tblTablePermissions called ViewIndex, then just use !ViewIndex in the code above to read the SQL syntax into strSQL.

Rename SQL Server Table?
Notice in line 35 above the system will actually use a different name for the Access table if so specified. This technique can come in handy when your dealing with a legacy app and wish to use names in SQL Server that better suit you but can’t be used in Access due to legacy code.

AttachDSNLess Table Code
The above procedure calls this sub to actually do the linking:
'Note: Code adapted from http://support.microsoft.com/kb/892490
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, strDriver As String, _
Optional stUserName As String, Optional stPassword As String) As Boolean
10 On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

20 If stLocalTableName = "" Then
30 stLocalTableName = stRemoteTableName
40 End If
50 Application.Echo True, "Linking table " & stLocalTableName
'Stop
'//WARNING: This will save the username and the password with the linked table information.
60 stConnect = "ODBC;DRIVER=" & strDriver & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword

70 Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
80 CurrentDb.TableDefs.Append td
90 AttachDSNLessTable = True
100 Exit Function
AttachDSNLessTable_Err:
110 If Err.Number = 3265 Or Err.Number = 3011 Then
'Table does not exist, continue anyway
120 Resume Next
130 End If
140 AttachDSNLessTable = False
150 Stop
160 MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function

 

In my next post I’ll discuss how to destroy the links when you exit the application.