Microsoft Office Tutorials and References
In Depth Information
MsgBox "Connection error: " & Err.Description
Exit Sub
End If
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = q
cmd.CommandType = adCmdText
Set rs = New ADODB.Recordset
Set rs.Source = cmd
cn.Errors.Clear
rs.Open
If Err.Number = 0 Then
CopyRows rs
Else
MsgBox "Query error: " & Err.Description
End If
rs.Close
cn.Close
End Sub
To execute the query, this routine uses three ActiveX Data Object (ADO) database objects: a
Connection object, a Command object, and a Recordset object. Each is declared at the start of
this routine. Next, error checking is disabled by using the On Error Resume Next statement
because the routine checks for errors after any critical statement.
The first step in retrieving rows from a database is to establish a connection to the database.
A new instance of the ADODB.Connection object is created, and the connection string value
that’s passed to this routine is assigned to the Connection object’s ConnectionString property.
After instantiating the Connection object, the Open method is used to open a connection to
the database.
Tip Out with the New
Never use the New keyword when defining an object using a Dim , a Private , or a Public state­
ment. Visual Basic for Applications (VBA) includes extra code around each object reference
to determine whether the object has been instantiated. If the object hasn’t been instanti­
ated, the code will automatically create a new instance of the object for you. Although this
extra code doesn’t add a lot of overhead, you will be better off controlling exactly when a
new object is instantiated.
If there was a problem opening the connection, an error message is displayed to the user and
the Exit Sub statement is used to leave the subroutine with any further processing.
Search JabSto ::




Custom Search