ODBC DSN-Less Connection Tutorial

Tuesday Aug 7th 2001 by Danny Lesandrini
Share:

In a previous article, Problematic or Programmatic ODBC, I described the various ways I have used to distribute a DSN (ODBC Data Source Name) to users of my client/server applications. Towards the end of that article, I mentioned an Access Database Application I created to serve as a tutorial for people with questions about reconnecting linked MS Access tables to SQL Server using DSN-Less ODBC. This article will focus on the details of that tutorial, which is available for download.

What is DSN-Less ODBC?

In a previous article, Problematic or Programmatic ODBC, I described the various ways I have used to distribute a DSN (ODBC Data Source Name) to users of my client/server applications. Towards the end of that article, I mentioned an Access Database Application I created to serve as a tutorial for people with questions about reconnecting linked MS Access tables to SQL Server using DSN-Less ODBC. This article will focus on the details of that tutorial, which is available for download.

DSN-Less ODBC simply means that instead of storing connection information (like server, database, etc.) in a file or the Windows Registry, you have plunked them into a connect string, which is then associated with some data object, like an Access linked table. Here is an example of the connect string generated by my tutorial:
    ODBC;DRIVER={SQL Server};SERVER=cliff;DATABASE=pubs;UID=sa;PWD=;
You will notice that there are five parameters that need to be populated:
    DRIVER={SQL Server}
    SERVER=cliff
    DATABASE=pubs
    UID=sa
    PWD=
In our example, the DRIVER parameter will always be SQL Server, so the Login form doesn't need to bother the user for that information. On the other hand, the other arguments are subject to change, so the user needs to be queried to supply the SERVER, DATABASE, USER and PASSWORD. In most cases, the user is required to supply some or all of these values. The Access form which I use to collect this information looks like this:



Making it Easier for the User

Since login screens can be intimidating for some users, I decided to add an optional "auto-populate" feature to the login form. Instead of supplying the user with values which need to be typed into the login form, the user simply supplies the path to a file (an old-fashioned ini file) containing that information. In our application, that path was hidden from the user and login was truly automatic. This idea worked well for us, since it was customary to create a common network folder where application setup and shared files could be accessable to all users. In order to automate the login process, the application adminstrator needs simply to edit the ini file with current data source information.



Next: Page 2



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


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved