ADO is Microsoft’s latest way of making it easier for us developers to retrieve and work with databases. Don’t worry, you don’t have to be a SQL-guru to use ADO; however, some basics would be nice.
SQL (Structured Query Language) is a ‘programming’ language used for querying data within Relational Databases. The most basic command is SELECT. SELECT is used to retrieve data from one or more tables of the database. For this article, we will be using the NorthWind database. (It comes with both SQL Server, and MS-Access (For MS-Access, it is installed in the Samples-Folder)).
If we want to retrieve all rows within a certain table, we would issue the following statement:
SELECT * FROM employees
This would retrieve all data from the ’employees’ table. However, if we’d want to see which employee handled which order, we’d query two tables; employees, and orders. How would we know which employee handled which order though? This is where the relational part of the Relational Database-concept kicks in. Instead of listing the employee’s full data (employee-id, name, DOB, etc), the orders-table just shows a reference to a unique number assigned to the employee dealing with a particular order. If we match the number from employees with the number from orders, we would get the full information about the employee, as well as the full order detail. The statement to do this would be:
SELECT * FROM employees, orders WHERE employees.EmployeeID = orders.EmployeeID
Let’s look at that statement for a second. It’s not that hard, and I don’t want to spend too much time on the actual SQL-syntax (Mike handles the SQL Server articles). The first part is similar to our very first statement. SELECT * specifies we want to retrieve all rows from the table(s). FROM employees, orders specifies the tables to retrieve the data from are employees and orders. The comma acts to tell the database we want data from both tables. (If you’d specify more tables, you’d just seperate them with commas each time). WHERE employees.EmployeeID = orders.EmployeeID is what we use to specify the relationship between the tables. In this case the EmployeeID of the orders table is equal to an ID in the employeesID, and we want to see all the information. Does this sound complicated? Let’s look at a quick graphical representation of the tables.
Without going into too much detail, you might see from the diagram (which I whipped up using SQL Server), that between Orders and Employees, a one-to-many reationship exists. One order has one employee handling it. However, an employee can handle many orders. (The way to recognise this is the little symbol looking like two intertwining o’s. this is the mathematical symbol for infinity. (Don’t pin me down on this. Math never was my favourite subject) going to the orders-table).
That ought to be enough about SQL for now. Let’s get started with ADO; after all, that is why you are reading this article.
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.
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.
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)).
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”
CN.ConnectionString=”Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;” & _
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:
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:
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.