ADO Main Setup

by Linda Quinn

Close Window Print Page

* This example is a Visual Basic module that creates a global variable for the data connection.

* The connection itself is defined in this module.

* Other modules in the project can reference the variable gConn to refer to the ADO connection object.

Declare the global variable gConn.

Option Explicit
Public gConn As New ADODB.Connection

The function DataOpen uses the global variable gConn to create an
open ADO connection.

Public Function DataOpen(oConn As Connection) As Boolean

   On Error GoTo open_EH

   oConn.CursorLocation = adUseClient
   oConn.ConnectionString = ConnectString()
   oConn.Mode = adModeReadWrite
   oConn.Open

   DataOpen = True

   Exit Function

   open_EH:
   Call ErrorHandler(gConn)
   DataOpen = False
   Exit Function

End Function

The function ConnectString assigns a DSN or connection string to the ADO ConnectionString

Public Function ConnectString()

ConnectString = "DSN=prodreq"

End Function

Error Handler for errors opening the ADO connection.

Public Sub ErrorHandler(oConn As Connection)

   Dim oErr As Error
   Dim strMsg As String

   For Each oErr In oConn.Errors
      strMsg = strMsg + "Error #: " + oErr.Number + vbCrLf
      strMsg = strMsg + "Description: " + oErr.Description + vbCrLf
      strMsg = strMsg + "Source:" + oErr.Source + vbCrLf
   Next

   MsgBox strMsg

End Sub

StrNulls - convert null values to strings

Public Function StrNulls(vntField As Variant) As String

   If IsNull(vntField) Then
      StrNulls = ""
   Else
      StrNulls = Trim$(CStr(vntField))
   End If

End Function

chkNulls - Test if a value is null

Public Function chkNulls(vntField As Variant) As Integer

   If IsNull(vntField) Then
      chkNulls = vbUnchecked
   Else
      chkNulls = IIf(vntField, vbChecked, vbUnchecked)
   End If

End Function