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 SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


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

Featured Database Articles

MS SQL

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))
    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




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


















Thanks for your registration, follow us on our social networks to keep up-to-date