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.