Visual Basic Concepts

Establishing a Connection to a Database

RDO

To open a connection, you must supply a connection string with parameters. Note that a connection is not required by RDO to create an rdoQuery object, but is required to initially create an rdoResultset object:

Dim cn As New rdoConnection
Dim cnB As New rdoConnection
Const ConnectString = "uid=myname;pwd=mypw;driver={SQLServer};" & _
 "server=myserver;database=pubs;dsn=''"

This connect string accesses a specific SQL Server and permits ODBC to open a connection without a DSN. This is a typical ODBC connect string with all of the standard arguments.

The next section, in the form's Load event, establishes the type of cursor driver and the login timeout. By default, RDO uses the rdUseIfNeeded cursor type, which invokes server-side cursors on SQL Server. This default is overridden in the example below by specifying rdUseNone. The rdDriverNoPrompt flag means that the application generates an error if the user ID and password do not match.

Private Sub Form_Load()
   With cn
      cn.Connect = ConnectString
      cn.LoginTimeout = 10
      cn.CursorDriver = rdUseNone
      cn.EstablishConnection rdDriverNoPrompt
   End With

This second connection performs any client-batch updates:

   With cnB
      cnB.Connect = ConnectString
      cnB.CursorDriver = rdUseClientBatch
      cnB.EstablishConnection
   End With
End Sub

The last event occurs when the connection operation completes and it handles any errors that occur when the connection is opened. With it, you can test to see if the connection worked, and if so, enable any buttons that rely on an open connection.

Private Sub cn_Connect(ByVal ErrorOccurred As Boolean)
   If ErrorOccurred Then
      MsgBox "Could not open connection", vbCritical
   Else
      RunOKFrame.Enabled = True
   End If
End Sub

ADO

To establish a database connection in ADO, first create a set of ADO objects referenced from the ADODB object. These are used later to set specific properties that open connections and generate resultsets:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cnB As New ADODB.Connection
Dim Qy As New ADODB.Command

The next line creates a connect string, just like the one you created in the previous RDO example. In both cases, you are using ODBC’s "non-DSN" connection strategy to save time and increase performance:

Const ConnectString = "uid=myname;pwd=mypw;driver={SQL Server};" & _
 "server=myserver;database=pubs;dsn=''"

The following declarations initialize the variables used in this example. (Note the creation of a variant array to hold the resultset):

Dim sql As String
Dim rc As Integer
Dim i As Integer
Dim Changes As Integer
Dim bms() As Variant

Next, open an ADO connection to a database in the Form_Load event. Note that this code is very similar to the RDO code except that the constants are prefaced with "ad" rather than "rd". To see all available constants, look at the ADODB type library.

Note   There's no need to specify the prompting behavior since ADO defaults to "no prompt". If you elect to change this, however, use the ADO Properties collection to deal with the desired prompt behavior. In RDO, you can set the behavior using the OpenConnection argument. In ADO, you must set the Properties ("Prompt") property.

Also, there's no need to specify a cursor driver if you don't want to use one (such as the RDO CursorDriver = rdUseNone), since ADO defaults to no cursor driver by default.

Private Sub Form_Load()
   With cn
      ' Establish DSN-less connection
      .ConnectString = ConnectString
      .ConnectionTimeout = 10
      '.Properties("Prompt") = adPromptNever
      ' This is the default prompting mode in ADO.
      .Open
   End With
   With cnB
      .ConnectString = ConnectString
      .CursorLocation = adUseClient
      .Open
   End With
End Sub