Microsoft Office Tutorials and References
In Depth Information
Next a new instance of the ADODB.Command object is created. The newly opened Connection
object is assigned to the ActiveConnection property, and the query is assigned to the
CommandText property. Finally, the CommandType property is set to adCmdText , meaning
that the Command object contains an SQL statement.
A new ADODB.Recordset object is created, and the Source property is set to the Command
object that was just initialized. The Connection object’s Errors collection is explicitly cleared,
and then the Recordset object’s Open method is called.
Any errors that occur while opening the Recordset are trapped, and an error message is dis
played to the user. Otherwise, the CopyRows subroutine is called to copy the rows from the
Recordset object to the current worksheet.
Finally, the Recordset object and the Connection object are closed (in that order). These steps
release any resources held by those objects back to the operating system.
Tip Close Your Connections
For the best database server performance, always minimize the amount of time that a
connection is open. Closing unneeded connections reduces the resources required to run
the database server, which in turn lets the database server handle more connections and
perform more work.
Once you have a Recordset that contains rows from the database, the only step left is to copy
the rows to your worksheet one row at a time. The CopyRows routine in the ThisWorkbook
module relies on three local variables: i and j, which contain pointers to the current cell row
and the column on the worksheet, and f, which contains a field from the Recordset object.
Sub CopyRows(rs As ADODB.Recordset)
Dim f As ADODB.Field
Do While Not rs.EOF
For Each f In rs.Fields
Cells(i, j) = f.Value