Total Visual SourceBook

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office 2007 and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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

Class to support client/server operations using ADO with any ODBC connection in VBA and VB6.

This class sets up an ADO connection object for any ODBC connection. Use this connection if a native OLE DB driver is not available for your data source.

Procedure List

Procedure Name

Type

Description

(Declarations) Declarations Declarations and private variables for the CADOConnODBC class
Attributes Property Get the Connection attribute flags used to open the connection to the database. For a Connection object, the Attributes property is read/write, and its value can be the sum of any one or more of the XactAttributeEnum values (default is zero).
CommandTimeout Property Get how long to wait while executing a command before terminating the attempt and generating an error. Use the CommandTimeout property to allow the cancellation of an Execute method call, due to delays from network traffic or heavy server use.
Connection Property Get a pointer to the local Connection object that was created by this class. A Connection object represents a unique session with a data source. This is the object created and managed by the CADOConnODBC class. This property is read-only. You may use this property to gain access to all of the properties and methods of the ADODB Connection object via an object variable declared from the CADOConnODBC class.
ConnectionTimeout Property Get how long to wait while establishing a connection before terminating the attempt and generating an error Use the ConnectionTimeout property on a Connection object if delays from network traffic or heavy server use make it necessary to abandon a connection attempt.
ConnectString Property Get the value of the ConnectString property of the connection. This property is a combination of the values supplied as properties of this class, and additional values supplied after the connection is made. You may get the value of this property if you wish to see the value of the ConnectString as it was supplied to the server. Note that the server may insert additional parameters into the ConnectString after the connection is made.
CursorLocation Property Get the value of the CursorLocation property of the connection. This property allows you to choose between various cursor libraries accessible to the provider. Usually, you can choose between using a client-side cursor library or one that is located on the server.
Database Property Get the string containing the name of the ODBC database used to create the connection.
DataSource Property Get the string containing the name of the server machine used to create the connection.
Driver Property Get the string containing the ODBC Driver name used to create the connection. The value of this property is dependent on the particular ODBC driver you are using.
IsolationLevel Property Get the value of the IsolationLevel property of the connection. Use the IsolationLevel property to set the isolation level of a Connection object. The IsolationLevel property is read/write. The setting does not take effect until the next time you call the BeginTrans method. If the level of isolation you request is unavailable, the provider may return the next greater level of isolation.
Mode Property Get the value of the Mode property of the connection. Use the Mode property to set or return the access permissions in use by the provider on the current connection. You can set the Mode property only when the Connection object is closed.
Password Property Get the text of the password used to log into the database.
Provider Property Get the string containing the ODBC provider name used to create the connection. The Provider property is read/write when the connection is closed and read-only when it is open. The setting does not take effect until you either open the Connection object or access the Properties collection of the Connection object. For generic ODBC datasources, the value is the default "MSDASQL".
UserID Property Get the string containing the user name used to log into the server.
Class_Initialize Initialize Set initial values to defaults which may be overridden with property settings
Class_Terminate Terminate Release resources used by the class
CloseConnection Method Close the current connection to the ODBC database and frees up any associated system resources
OpenConnection Method Open the ADODB Connection to the specified ODBC database, using the properties of this class to control parameters of the connection. Using the OpenConnection method establishes the physical connection to a data source. After this method successfully completes, the connection is live and you can issue commands against it and process the results.
BuildConnectString Private Builds up the string used as the ConnectString property of the class, which is the value used to open the connection. The various property settings of this class are constructed into the actual ConnectString which are used to create the connection. This helper procedure handles creating the string.
m_Connection_BeginTransComplete Private The private local ADODB.Connection variable raises connection- related events. This procedure simply passes the BeginTransComplete events on to the user of this class. This handling method is called after the associated operation on the Connection object finishes executing. BeginTransComplete is called after the BeginTrans operation.
m_Connection_CommitTransComplete Private The private local ADODB.Connection variable raises connection-related events. This procedure simply passes the CommitTransComplete events on to the user of this class. This handling method is called after the associated operation on the Connection object finishes executing. CommitTransComplete is called after the CommitTrans operation.
m_Connection_ConnectComplete Private The private local ADODB.Connection variable raises connection-related events. This procedure simply passes the ConnectComplete events on to the user of this class. The ConnectComplete method is called after a connection starts.
m_Connection_Disconnect Private The private local ADODB.Connection variable raises connection-related events. This procedure simply passes the Disconnect events on to the user of this class. The Disconnect method is called after a connection ends.
m_Connection_ExecuteComplete Private The private local ADODB.Connection variable raises connection-related events. This procedure simply passes the ExecuteComplete events on to the user of this class. This event is raised after a command has finished executing.
m_Connection_InfoMessage Private The private local ADODB.Connection variable raises connection-related events. This procedure simply passes the InfoMessage events on to the user of this class. This method is called whenever a ConnectionEvent operation completes successfully and additional information is returned by a provider.
m_Connection_RollbackTransComplete Private The private local ADODB.Connection variable raises connection- related events. This procedure simply passes the RollbackTransComplete events on to the user of this class. This handling method is called after the associated operation on the Connection object finishes executing. RollbackTransComplete is called after the RollbackTrans operation.
m_Connection_WillConnect Private The private local ADODB.Connection variable raises connection-related events. This procedure simply passes the WillConnect events on to the user of this class. This method is called before a connection starts. The parameters to be used in the pending connection are supplied as input parameters and can be changed before the method returns. This method may return a request that the pending connection be canceled.
m_Connection_WillExecute Private The private local ADODB.Connection variable raises connection-related events. This procedure simply passes the WillExecute events on to the user of this class. This method is called just before a pending command executes on this connection and affords the user an opportunity to examine and modify the pending execution parameters. This method may return a request that the pending command be canceled.

Example Code for Using Class: ADOConnODBC

' 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

Microsoft Access Module LibraryOverview of Total Visual SourceBook

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.

Additional Resources