Microsoft Office Tutorials and References
In Depth Information
The Connection Object
With entire books devoted to database integration with ADO, there is much more complexity to the
topic than this lesson is meant to cover. The best way to start becoming familiar with ADO is to
examine the three primary tools in its object model: the Connection object, the Recordset object,
and the Command object.
The connection object
The Connection object establishes a path that connects Excel and the database. With ADO from
Excel, you normally issue commands that pass information back and forth through the Connection
object. Among the key methods belonging to the Connection object are Open , which establishes
the database connection, and Close , which closes the connection. The Connection object’s
ConnectionString property defines how to connect to the database.
Connecting to the database is accomplished with the Provider keyword. The following line of code
is a common syntax for Excel versions 2007 and 2010:
Provider = “Microsoft.ACE.OLEDB.12.0;Data Source= _
C:\YourFilePath\Database1.accdb”;Persist Security Info=False;”
In versions of Excel prior to 2007, the Provider would have been specified as the Microsoft Jet
database engine of Access:
Provider = “Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=C:\YourFilePath\Database1.accdb; Extended Properties=Excel 8.0;”
Or, depending on the circumstance, more simply:
Provider = “Microsoft.Jet.OLEDB.4.0”
When working with databases, you almost always connect to them, meaning you
do not open them in a way you’d open a Word document if you were working
with Word from Excel. The Connection object is like a conduit between Excel
and your database.
The recordset object
The Recordset object is probably the most commonly used object in ADO. When you instruct
ADO to retrieve a single record or the entire count of records from a database table, you use the
Recordset object to do that.
Among the key members of the Recordset object are the following:
The ActiveConnection property, which is a connection string or a Connection object
that identifies the connection being used to access the database. As with this property for
the Command object, where objRecordset and objConnection are object variables, the
ActiveConnection syntax is
Set objRecordset.ActiveConnection = objConnection
Search JabSto ::

Custom Search