Option Compare Database
Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : SetDSNLessTablesNViews
' Author    : Patrick Wood  http://gainingaccess.net
' Date      : 5/28/2011
' Purpose   : Set Table and View Connections to DSN-Less.
'---------------------------------------------------------------------------------------
'
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
	    

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

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

End Function
	    

'---------------------------------------------------------------------------------------
' Procedure : ListODBCTableProps
' Author    : Patrick Wood  http://gainingaccess.net
' Date      : 5/28/2011
' Purpose   : List the Table Properties of ODBC linked tables.
'---------------------------------------------------------------------------------------
'
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