Class: ADOConnODBC in Category SQL Server : SQL Server from Total Visual SourceBook

' Example of CADOConnODBC
'
' To try this example, do the following:
' 1. Create a new form
' 2. Create a command button 'cmdTest'
' 3. Paste all the code from this example to the new form's module.

Private WithEvents mclsADOConnODBC As CADOConnODBC

Private Sub cmdTest_Click()
  ' Example of CADOConnODBC
  '-------------------------------
  ' This example creates the CADOConnODBC object. The connection is made asynchronously.
  ' When the connection is complete, the ConnectComplete event is used to open a recordset using the connection
  ' Note: This procedure will not work unless you have an installation of Microsoft SQL Server on your system with a 'pubs' demo database.
  ' The default userid and password is used. If the userid and password is different on your system, you must change the settings shown below.

  Dim strServer As String

  strServer = InputBox("Enter the name of the SQL Server on your system containing a 'pubs' database")

  If strServer <> "" Then
    Debug.Print "Connecting..."

    Set mclsADOConnODBC = New CADOConnODBC

    With mclsADOConnODBC
      .UserID = "sa"
      .Password = ""
      .CursorLocation = adUseClient
      .Database = "pubs"
      .DataSource = strServer
      .Driver = "SQL Server"
      .Mode = adModeRead
      .ConnectionTimeout = 5
      .OpenConnection
    End With

    Set mclsADOConnODBC = Nothing
  End If

End Sub

Private Sub mclsADOConnODBC_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
  Dim rstAuthors As ADODB.Recordset
  Dim strSQL As String

  strSQL = "select Fullname = au_lname + ', ' + au_fname " & _
           "from authors Order by au_lname"

  If pError Is Nothing Then
    If pConnection.state = adStateOpen Then
      Debug.Print pConnection.ConnectionString
      Set rstAuthors = pConnection.Execute(strSQL)
      If Not rstAuthors.EOF Then
        Do Until rstAuthors.EOF
          Debug.Print rstAuthors("FullName")
          rstAuthors.MoveNext
        Loop
      End If
    Else
      Debug.Print "Connection failure, invalid state"
      MsgBox "Unable to open the connection"
    End If
  Else
    Debug.Print "Connection failure"
    MsgBox "Unable to open the connection"
  End If

End Sub

The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)