Skip Navigation LinksHome Page > Articles By Patrick Wood > TableDef Append Or RefreshLink

Microsoft Access DSN-Less Linked Tables: TableDef.Append or TableDef.RefreshLink?

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

    '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
                Debug.Print "----------------------------------------" 
                For Each prp In tdf.Properties 
                    'Skip NameMap and GUID which are Binary Type Properties here. 
                    If prp.Name <> "NameMap" And prp.Name <> "GUID" Then 
                        Debug.Print prp.Name & ": " & prp.Value 
                    End If
                Next prp
            End If
        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.


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

    'I 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.

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.

UPDATE: I was informed that Views loose the identity of the PK Field when RefreshLink is used with them. So until we find a better solution we will have to save the connection info from all Views in a Table, including which Field is the PK Field, delete the View's TableDef, and then create it again.

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;" _
        & "DATABASE=MySQLDatabaseName;" _
        & "UID=MyUserName;" _
        & "PWD=MyPassW0rd;" _
        & "Encrypt=Yes"

End Function
							

You can see or download the code used in this article from our Free Code Samples page.

Login Status: Not logged in.
Login      Free Registration

Get a Free Consultation

Need help with a database or Excel?
We can help!
Email:
Phone:

Share

Site Navigation

Skip Navigation Links.
Return to Top