Managing DSN-Less Connections

[Seth Schrock]

Introduction
Until just recently, I would use system DSNs for all of my MS Access databases that connected to MS SQL Server backends. It is really easy to setup and didn't require all the code that I was seeing online for the DSN-Less connections. However, it is a pain to try to switch to a test server, make sure that you have the correct DSN setup on the client computer (changes depending on 32 or 64 bit client system), etc. So now I have made the switch to DSN-Less connections and decided to post how I did it (since I can't find everything that I need online).

Setup
First, I created two local tables to store the different SQL Server locations and another on for the list of tables and where they should be connected to.

tblBE
BE_ID_pk      AutoNumber
Driver        Text
Server        Text
DatabaseName  Text
 
 
 
 tblTableLocation
TableID_pk       AutoNumber
TableName        Text
RemoteTableName  Text
LocationID_fk    Number
 
One-to-Many join on tblBE.BE_ID_pk = tblTableLocation.LocationID_fk

Code
First, I have the code to add a table to the TableDefs collection. This function adds the table to the TableDefs and then adds the table to the table list in tblTableLocation.

Public Function AddTable(TableName As String, RemoteTable As String, Location As Long) As Boolean
On Error GoTo Error_Handler
 
Dim db As DAO.Database
Dim td As TableDef
Dim strCon As String
Dim strAddQry As String
 
Set db = CurrentDb
strCon = "ODBC;" & GetLocation(Location) & "Trusted_Connection=YES"
Set td = db.CreateTableDef(TableName, dbAttachSavePWD, RemoteTable, strCon)
 
db.TableDefs.Append td
 
strAddQry = "INSERT INTO tblTableLocation (TableName, RemoteTableName, LocationID_fk) " & _
            "VALUES ('" & TableName & "', '" & RemoteTable & "', " & Location & ")"
 
db.Execute strAddQry, dbFailOnError
 
AddTable = True
 
Exit_Procedure:
    Set db = Nothing
    Set td = Nothing
    Exit Function
 
Error_Handler:
    AddTable = False
    Resume Exit_Procedure
 
End Function
 
 
 
Private Function GetLocation(LocationID As Long) As String
On Error GoTo Error_Handler
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strRst As String
Dim strLoc As String
 
strRst = "SELECT * FROM tblBE WHERE BE_ID_pk = " & LocationID
 
Set db = CurrentDb
Set rst = db.OpenRecordset(strRst, dbOpenDynaset)
 
With rst
    strLoc = "DRIVER=" & !Driver & ";SERVER=" & !Server & ";DATABASE=" & !DatabaseName & ";"
End With
 
GetLocation = strLoc
 
Exit_Procedure:
    On Error Resume Next
    rst.Close
    Set db = Nothing
    Set rst = Nothing
 
    Exit Function
 
Error_Handler:
    Resume Exit_Procedure
 
End Function

The parameters are as follows

1. TableName: The name that you want to appear in Access
2. RemoteTable: The name of the table in SQL Server (includes the schema name, ie. dbo.Customers)
3. Location: The PK value from tblBE for the server/database that you want to connect to. The location information comes from the GetLocation() function.

The code to move a table between servers is very much the same, but before it adds the table to the TableDefs collection, it first deletes it. So the linked table from the old location is deleted and then a new linked table is created that is linked to the new location.

Public Function MoveTable(LocalTable As String, RemoteTable As String, Location As Long) As Boolean
On Error GoTo Error_Handler
 
Dim td As TableDef
Dim strCon As String
Dim db As DAO.Database
 
Set db = CurrentDb
 
db.TableDefs.Delete LocalTable
 
strCon = "ODBC;" & GetLocation(Location) & ";Trusted_Connection=YES"
 
Set td = db.CreateTableDef(LocalTable, dbAttachSavePWD, RemoteTable, strCon)
db.TableDefs.Append td
 
MoveTable = True
 
Exit_Procedure:
    Set db = Nothing
    Set td = Nothing
 
    Exit Function
 
Error_Handler:
    AttachDSNLessTable = False
    Resume Exit_Procedure
 
End Function

Calling the Code
I have an unbound form that allows me to enter all my information in textboxes and comboboxes. The exact design isn't important, but I have two sections: one to add a table and one to move tables to another server.

First, adding a table. A textbox for the local table name, remote table name, and a combobox for the server/database. I think that the control names are fairly obvious as to what they are.

Private Sub cmdAttachTable_Click()
On Error GoTo Error_Handler
 
If Me.txtLTable & "" <> "" And Me.txtRTable & "" <> "" And Not IsNull(Me.cboLoc) Then
    If AddTable(Me.txtLTable, Me.txtRTable, Me.cboLoc) = False Then
        MsgBox "Attempt to attach " & Me.txtLTable & " to the database failed.", vbCritical
 
    End If
Else
    MsgBox "All three fields need to be populated in order to attach a table.", vbInformation
End If
 
Exit_Procedure:
    Exit Sub
 
Error_Handler:
    Resume Exit_Procedure
 
End Sub

Moving the tables. Comboboxes for the From and To server/database.

Private Sub cmdChangeBackend_Click()
On Error GoTo Error_Handler
 
Dim strTables As String
Dim db As DAO.Database
Dim rstTables As DAO.Recordset
Dim strMsg As String
 
If Not IsNull(Me.cboFrom) And Not IsNull(Me.cboTo) Then
    strTables = "SELECT * FROM tblTableLocation WHERE LocationID_fk = " & Me.cboFrom
 
    Set db = CurrentDb
    Set rstTables = db.OpenRecordset(strTables, dbOpenDynaset)
 
    With rstTables
        Do While Not .EOF
 
            If AttachDSNLessTable(!TableName, !RemoteTableName, Me.cboTo) = False Then
                strMsg = "Connection attempt for " & !TableName & " failed."
                MsgBox strMsg, vbCritical, "Connection Attempt Failed"
            Else
                .Edit
                !LocationID_fk = Me.cboTo
                .Update
            End If
 
            .MoveNext
        Loop
 
    End With
Else
    MsgBox "Both drop-down boxes must have values"
End If
 
Exit_Procedure:
    On Error Resume Next
    rstTables.Close
 
    Set db = Nothing
    Set rstTables = Nothing
 
    Exit Sub
 
Error_Handler:
    Resume Exit_Procedure
 
End Sub

All of my SQL Server backend databases have two databases that they connect to. One is the database for the data and one is an Employee database that lists what their permissions are for that database (A custom built database that I did so that I don't have to create it for every database that I do. I might make another article about it later.). Because of this, I can't just move all tables from one server to another. Having the From combobox lets me only move the tables that are one server/database to another server/database and leave the other tables alone.

Conclusion
I have already used this once and I can tell you it is so much simpler than using the Linked Table Manager and making it ask for a new location. For me, it asks for the new location for every single table. When working with 10+ tables (I've got one right now with 24), this is very annoying. Now all I have to do is make two selections and click a button. Done.

[NeoPa]

Hi Seth.

Nice work :-)

Have you considered using the techniques included in Relinking Tables using VBA to adjust your process so that it relinks the existing tables rather than deletes them and recreates them. An advantage of this approach is that it doesn't lose any saved changes to the TableDef that might be useful for a designer to have available when they open it up. Obviously, there may be other reasons to keep the existing TableDefs but that one springs to mind.

[zmbd]

Neopa has a good point there and I have a variation on a theme, basically the same code, that I've used for years because the first time I split databases I lost a ton of stuff and had to rebuild things... made me read more though (^_^)

[Seth Schrock]

I'll confess that I hadn't because I only look at that article to relink to Access backends. Strange since the title says ODBC Tables :) I'll admit that I don't know of an example of changes to the TableDef that would be lost nor any other reasons not to delete the table, but I will take your word for it that there is and redo my code to make it just change the connection string.

I assume that it is the first post that I need to look at and not the second one?

[NeoPa]

There are various saveable settings you may choose to have associated with a Front-End table Seth. Sort order for instance. Another might be the column width for a particular column, or even the order the fields are displayed in.

As for the changes to the code - I expect it would be very similar in essence to the code I posted in the linked thread at post #2. Although there are 62 lines there in all, only 2 of them are directly involved in making the change. If you look at lines #31 & #33 you'll see it's simply a matter of changing the .Connect property followed by calling the .Relink() method.

Clearly, it makes sense to check that it worked, which is what much of the other lines of code are about, but the meat and drink are in those two lines. I expect porting that across to your own article would be pretty straightforward for you.