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))
Wend
Close intFile
strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=" & strServer _
& ";DATABASE=" & strDatabase _
& ";UID=" & strUID _
& ";PWD=" & strPWD & ";"
Else
' 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
Err.Clear
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)
qdfPUBS.Execute
' 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
Me.Repaint
tdfPUBS.RefreshLink
MsgBox "Link to " & strTable & " has been refreshed.", , "Linking Tables"
End If
Next
' 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
Conclusion
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
» 
IE 7
Firefox 2.0
Comment and Contribute
