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.

[U]tblBE[/U]
BE_ID_pk      AutoNumber
Driver        Text
Server        Text
DatabaseName  Text



[U]tblTableLocation[/U]
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 tblTableLocatio n.

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.