The ADO Object Model
ADO is a simple way of making a connection with a database, and issuing commands (such as stored procedures in SQL Server), or retrieving data into so-called RecordSets.
ADO resides on top of OLE-DB (from within Visual Basic, at least). OLE-DB enables you to access datasources of different types, without having to deal with issues like network-packets, security, etc.
The ADO Object model graphically looks like this:
Yes, only three objects. Let’s look at them individually, and discuss what they are used for.
Connection Object
The top level object; handles the actual connection to the database. In order for a Connection object to succesfully work, it needs to be told where to connect to, how to connect, and the database to access. This information is stored in it’s ConnectionString property.
Command Object
Using a Command object, you can issue a SQL statement that returns a single row, execute a stored procedure (A prec-compiled SQL statement, or batch of statements on SQL Server), or issue other commands against a database. Commands objects can also support parameters (for instance for useage with a stored procedure). You can use the Command object, without specifying an existing Connection (the Command object will create a Connection object by itself. I strongly recommend not using this approach though, and always creating a Connection object. That way, you can issue multiple commands on the same connection, and you prevent large numbers of connections being created (and hogging up valueable resources on the server)).
RecordSet Object
The RecordSet object defines rows returned from a database; more or less like a database table. A RecordSet is the object we will be using most, if we want to query a database. It is possible to not use an explicit connection when opening a database; however, if you use a connection, you can open multiple RecordSets on the same connection. (Also see above).
A RecordSet is built up out of multiple fields, that each represent a column of data. Graphically, a RecordSet could look like this:
(We created this ‘RecordSet’ by issuing the SQL query SELECT EmployeeID, LastName, FirstName FROM employees). The whole returned list is the RecordSet. The individual ‘boxes’ are all fields. So ‘Anne’ is a field, ‘3’ is a field, and ‘Buchanan’ is a field. (Notice the gray columns are just to provide some readability, and are NOT part of the RecordSet). The total number of fields in this particular RecordSet would be 27. (9 rows of three fields each).
Implementing ADO in an application
Let’s build a quick application to retrieve the RecordSet shown above, shall we?
Start up Visual Basic, and select ‘Standard Exe’ as the project-template.
To display the RecordSet in our program, we’re going to use a Hierarchical Flexgrid. Right-click within the ToolBox, and select Components. Scroll down untill you see ‘Microsoft Hierarchical Flexgrid Component‘.
Check the box in front of it to add the component to your Toolbox. In the Toolbox, you’ll notice a new icon:
Draw a new Hflexgrid on your form. The size doesn’t really matter; we can make it fit our RecordSet later.
Now we’re going to add the ADO object-libraries to our project. Click on Project, and select ‘References…’. In the list, scroll down untill you come across ‘Microsoft ActiveX Data Objects 2.1 Library‘. (You can also use other versions, instead of 2.1. Just make sure that if you will distribute the application to other users, they also have the same, or a later version).
What exactly did we do here? Well, basically we imported the classes (read: Objects) (The Connection, Command and RecordSet, that is) of ADO into our project, which means as much as that we can now start to use the objects within the ADO-library, along with their events, properties, and functions. (You can get an overview of the complete ADO-library by using the Object Browser. The Object Browser can be called either by using it’s icon in the Toolbar, or by pressing F2.
In the Object Browser, make ADODB the library you want to explore (Set the upper ComboBox’s value to ADODB).
Switch to the code-window (Use either F7, or the ‘View Code‘ icon in the Properties Window).
If you followed our Installation Instructions, you ought to see ‘Option Explicit’ there. (If not, Don’t worry. Click on Tools, Options…, and check the ‘Require Variable Declaration‘ box). This requires that you declare all variables with the Dim, Public, or Private statement.
In the General Declarations-area (Which is after ‘Option Explicit‘ and before any subroutines), we’ll declare two object-variables; 1 for a Connection, and 1 for a RecordSet:
Dim CN As ADODB.Connection
Dim RS As ADODB.RecordSet
Notice that we just allocated memory-space for the variables. The objects themselves do not exist yet. (In other words, the object is set to Nothing). If you don’t like the names I picked, feel free to change them within your own application. CN and RS are just a habit of mine.
Go to the Form_Load() event. (Either double-click on the form itself in the Form Designer, or use the ComboBoxes above the Code-Window. Use the left one to set the current control to ‘Form1’. A form’s default event will be ‘Load’, so you don’t need the right ComboBox to select the event.
Alright. We want to retrieve the employee_id, last name, and first name from the employees table. Remember the SQL statement we used earlier? If not, it’s:
SELECT EmployeeID, LastName, FirstName FROM employees
Our RecordSet will be built with that query. However, we need to supply that query to the RecordSet somehow. In my opinion, the most elegant way to do it is by using a string to store the query in. (You could also supply it directly when opening the RecordSet, but I dislike having really long lines of code. It makes the code less clear to read. That’s a personal decision though. Since I am the one writing this article though, we’ll use my way *sticks out his tongue*).
Dim SQL as String
(Again, SQL is just a habit of mine; I normally prefer using the Hungarian notation (Which I’ll discuss in a beginner-article soon), but for this example, it will do.
Next, we assign the SQL-statement to the string:
SQL = "SELECT EmployeeID, LastName, FirstName FROM employees"
We’re all set to go now. First, we create a connection object:
Set CN = CreateObject("Adodb.Connection")
Set means we assign an object reference to a variable (The earlier created CN). CreateObject means we want to create a new instance of the object.
Now we provide the Connection with a ConnectionString, so the connection knows where and how to access the database.
For SQL Server:
CN.ConnectionString = "Provider=SQLOLEDB;Data Source=SQLServer;" & _
"User ID=sa;Password="";Initial Catalog=NorthWind"
For MS-Access:
CN.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb;" & _
"User Id=admin;Password=;"
Note: The ampersand (&) and underscore (_) are used here to increase readability. They allow a string to be spread over multiple lines. You could also just type the full string out on a single line though.
Let’s analyse the connectionstring for a second. Provider supplies the OLE-DB Provider to use. Datasource specifies where ADO should look for the database. In the case of SQL Server, this is where you specify the servername. When using MS-Access, you supply the filepath to the database. User ID is which login to use when you’re accessing the server or database. Since both ‘sa’ in SQL Server and ‘admin’ in MS-Access have a default blank password, there’s no need to specify the Password (meaning you can just leave out the whole Password-string).. If, however, you changed the password, you should provide it. In SQL Server, we also have to supply which database to use (A single SQL Server can hold many databases, some of which are mandatory (master, msdb, tempdb and model). The Initial Catalog setting specifies to which database we will be connecting.
The next step is creating a RecordSet:
Set RS = CreateObject("Adodb.RecordSet")
This is similar to how we created the Connection-object. We’ll now open the connection to the data source, so we can retrieve data into our RecordSet:
CN.Open
Ok, now we got everything needed to fill our RecordSet with the required data:
RS.Open SQL, CN
For MS-Access databases, you have to add a little something:
RS.Open SQL, CN, adOpenDynamic
SQL is the parameter supplied as the Source for the RecordSet, while CN is used as the ActiveConnection. You’ll notice that the Intellisense offers other parameters as well. We’ll discuss those next week. For now, this will do just fine for our example.
So, we have the data in our RecordSet now, but we still need to display it in the Flexgrid. We can use a very nifty feature of the HflexGrid, namely the DataSource. This property can be used to display RecordSets. So what we do is we set our RecordSet to be used for the Datasource:
Set MSHFlexgrid.Datasource = RS
We have to use the ‘Set’ statement because we assign an object reference to a property.
Before we go on, I want to take a brief moment to explain a little something about objects. If you create an object, memory is allocated in which to store the object. As long as the object is active, this memoryspace will be in use by it. The same thing goes for the connection. We opened a connection, that is kept live both on the client (our computer) and on the server (The SQL Server, or, if you’re using a local instance of SQL Server or an MS-Access-Database, locally). To free up that memory, I recommend closing both RecordSets and Connections as soon as possible. Closing them does not mean the objects are destroyed; it just closes the connections. Since we don’t need them anymore anyway, we destroy them right after closing them. We do this by setting the object to Nothing:
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing
That’s it! To see the results, run the code. If all goes well, and you didn’t make any typos, you should be seeing the RecordSet displayed:
That’s it for this article.