Show Me The Code

Other than the standard "house cleaning" code, there are really three important functions. The first is the code which parses the ini file and extracts the login parameters. Once located, each parameter is stored in a global variable and another variable is populated with the complete connect string.

Public Sub ParseINIFile()
On Error Resume Next

Dim intFile As Integer
Dim strLine As String
Dim intFoundServer As Integer
Dim intFoundDatabase As Integer
Dim intFoundUID As Integer
Dim intFoundPWD As Integer

intFile = FreeFile

If Right(Dir(strPathToINI), 4) = ".ini" Then

' Path is valid. Continue

    Open strPathToINI For Input As intFile

' Loop through each line of the file, looking for parameters

    While Not (EOF(intFile))
        Line Input #intFile, strLine
        intFoundServer = InStr(1, strLine, "SERVER=")
        If intFoundServer > 0 Then strServer = Trim(Mid(strLine, intFoundServer + 7))
        intFoundDatabase = InStr(1, strLine, "DATABASE=")
        If intFoundDatabase > 0 Then strDatabase = Trim(Mid(strLine, intFoundDatabase + 9))
        intFoundUID = InStr(1, strLine, "UID=")
        If intFoundUID > 0 Then strUID = Trim(Mid(strLine, intFoundUID + 4))
        intFoundPWD = InStr(1, strLine, "PWD=")
        If intFoundPWD > 0 Then strPWD = Trim(Mid(strLine, intFoundPWD + 4))
    Close intFile
    strConnect = "ODBC;DRIVER={SQL Server}" _
               & ";SERVER=" & strServer _
               & ";DATABASE=" & strDatabase _
               & ";UID=" & strUID _
               & ";PWD=" & strPWD & ";"
    ' INI file is missing, cannot continue
    ' Reset connection parameters
    strServer = ""
    strDatabase = ""
    strUID = ""
    strUID = ""
    strConnect = ""
End If

End Sub

The next important piece of code is the function which validates the parameters passed. This is done by turning OFF the error handling and trying to use our newly created connection string to do some work. If an error results, we know the connection string has a problem. In this example, we are trying to delete an Author that doesn't exist from the Pubs database. Even though no records are deleted, the query will not return an error unless the connection fails. (If you are uncomfortable with the idea of executing a DELETE Query, then use a SELECT statement instead. Personally, I like to live on the edge.)

Private Function ValidateConnectString() As Boolean
On Error Resume Next

DoCmd.Hourglass True

' Assume success

ValidateConnectString = True

' Create test Query and set properties

Set qdfPUBS = dbPUBS.CreateQueryDef("")
qdfPUBS.Connect = strConnect
qdfPUBS.ReturnsRecords = False
qdfPUBS.ODBCTimeout = 5

' Attempt to delete a record that doesn't exist

qdfPUBS.SQL = "DELETE FROM Authors WHERE au_lname = 'No Such Author'"

' Simply test one Pass Through query to see that previous ' connect string is still valid (server has not changed)


' If there was an error, connection failed

If Err.Number Then ValidateConnectString = False

Set qdfPUBS = Nothing
DoCmd.Hourglass False
End Function

Finally, our last function is the MS Access code that actually assigns this ODBC DSN-Less connection string to the Connect Property of a linked table.

Private Function RefreshTableLinks() As Boolean
On Error Resume Next

Dim strTable As String
Dim strSuccess As String

' Refresh Access Linked Tables

For Each tdfPUBS In dbPUBS.TableDefs

' Only attempt to refresh link on tables that already ' have a connect string (linked tables only)

    If Len(tdfPUBS.Connect) > 0 Then
        strTable = tdfPUBS.Name

' Set the tables connection string

        tdfPUBS.Connect = strConnect

' Give feedback to user

        strMsg = "Refreshing link to ...  " & strTable
        Me!lblMsg.Caption = strMsg
        MsgBox "Link to " & strTable & " has been refreshed.", , "Linking Tables"
    End If

' Give feedback to user

strSuccess = IIf(Err.Number = 0, "Successful", "NOT successful.")
strMsg = "Finished.  Connect was " & strSuccess
Me!lblMsg.Caption = strMsg

If Err.Number = 0 Then RefreshTableLinks = True

End Function


I've left out much of the code that deals with variable declaration and database objects, but it's all available in the download. Remember that this tutorial was created as a tool to help me explain DSN-Less ODBC to people who didn't know where to start. My login form is not intended to be a "cut-and-paste" enterprise solution, but rather, an idea that was born to be enhanced. In fact, we have one login form that reads and writes the connection parameters to and from the Windows Registry, saving the user's conneciton information and allowing different users to connect the same client to different back-end databases. No doubt, you already have ideas of how this code can be adapted to better fit your program.

See All Articles by Columnist Danny Lesandrini