Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 7, 2001

ODBC DSN-Less Connection Tutorial - Page 2

By Danny Lesandrini

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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM