When it came to creating DSN-Less Linked Tables I had always used a procedure that deleted the TableDef and appended a new one until a problem occurred. The code I was using to save Linked Tables as DSN-Less Tables was not working with some of the Views in SQL Azure. This was a serious problem because the application I was developing would be distributed to clients who would then distribute it to their clients. We did not want to use a DSN file. But now the code that normally worked without a hitch was failing.

Because I was developing for SQL Azure, I had to use SQL Azure Security which includes the Username and Password in the Connection string. Even though I explicitly set the dbAttachSavePWD (Enum Value: 131072) when I appended the new TableDefs the Connection Property of my views still did not include my Username and Password. So I quickly wrote some code to loop through the TableDef properties to see if I could discover the problem.

Sub ListODBCTableProps()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property

    Set db = CurrentDb

    For Each tdf In db.TableDefs
        If Left$(tdf.Connect, 5) = "ODBC;" Then
            Debug.Print "----------------------------------------"
            For Each prp In tdf.Properties
                'Skip NameMap (dbLongBinary) and GUID (dbBinary) Properties here
                If prp.Name <> "NameMap" And prp.Name <> "GUID" Then
                     Debug.Print prp.Name & ": " & prp.Value
                End If
            Next prp
        End If
    Next tdf

    Set tdf = Nothing
    Set db = Nothing

End Sub

I discovered that the TableDef Attributes of the Views for which my code was not working was 536870912 but for the Tables and Views that were working it was 537001984. After checking the TableDefAttributeEnum Enumeration values I was puzzled. The Attributes value for the Views which were not working was 537001984 which is the value for dbAttachedODBC (Linked ODBC database table). And the value of the Attribute for the Tables and Views that were working was 536870912 which is not in the list. After a few moments I figured it out. I saw that if you add the dbAttachedODBC value of 536870912 to the dbAttachSavePWD value of 131072 it equals the 537001984 Attributes value of the DSN-Less Tables and Views that were set properly. This made sense since the documentation Description for dbAttachSavePWD is “Saves user ID and password for linked remote table”. Apparently the Views needed both Attributes. But how could I set it?

Even though my code explicitly set the Attributes value to dbAttachSavePWD when creating the new TableDefs it was not working. Eventually I found some code that used the TableDef.RefreshLink Method, added the TableDefs Attributes dbAttachSavePWD (131072) value, and tested it. This solution worked. Below is the code I used.

Function SetDSNLessTablesNViews() As Boolean

     Dim db As DAO.Database
     Dim tdf As DAO.TableDef
     Dim strConnection As String

     SetDSNLessTablesNViews = False 'Default Value

     Set db = CurrentDb

     'Use a Function to get the Connection string
     'Note: In actual use I never use "Connection" in my Variables or Procedure names.
     'I disguise them to make it hard for a hacker to use code to get my Connection string
     strConnection = GetCnnString()

     'Loop through the TableDefs Collection
     For Each tdf In db.TableDefs
         'Verify the table is an ODBC linked table
         If Left$(tdf.Connect, 5) = "ODBC;" Then
             'Skip System tables
             If Left$(tdf.Name, 1) <> "~" Then
                 Set tdf = db.TableDefs(tdf.Name)
                 tdf.Connect = strConnection
                 If tdf.Attributes < 537001984 Then
                     tdf.Attributes = dbAttachSavePWD 'dbAttachSavePWD = 131072
                 End If
                 tdf.RefreshLink
             End If
         End If
     Next tdf

     SetDSNLessTablesNViews = True

     Set tdf = Nothing
     Set db = Nothing

End Function

I felt better about using the tdf.RefreshLink Method rather than deleting the TableDefs and appending them again. I read that you could delete your TableDefs and not be able to append a new one if there is an error in the Connection string at this page on Doug Steele’s website at the bottom of the page.

I found an interesting discussion about whether to delete and then append a new TableDef or use the RefreshLink Method on Access Monster. However the latest Developer’s Reference documentation settles the matter for me when it states the TableDef.RefreshLink Method “Updates the connection information for a linked table (Microsoft Access workspaces only).”

You may also want to see the sample code from The Access Web by Dev Ashish using the RefreshLink Method.

Below is an example of the code used to get the Connection string. As I stated in the procedure notes I never use “Connection” in Constants, Variables, or Procedure names. Nor do I use cnn, con, cnnString, etc. Instead I disguise the name of my Procedure to make it hard for a hacker to use to get my Connection string. Constants and Procedure names, along with some variables, are easily seen by opening up even an accde or mde file with a free Hex editor unless you have encrypted the database file. If I can see the name of your Constant I can very easily get its value.

'Don't forget to change the name of this procedure
Function GetCnnString() As String

     GetCnnString = "ODBC;" _
         & "DRIVER={SQL Server Native Client 10.0};" _
         & "SERVER=MyServerName;" _
         & "UID=MyUserName;" _
         & "PWD=MyPassW0rd;" _
         & "DATABASE=MySQLDatabaseName;" _
         & "Encrypt=Yes"

End Function