ADO is short for Microsoft ActiveX Data Objects. It is the technology that is used to communicate with databases (and many other types of data sources). This tutorial will attempt to teach you the basics of ADO, and give you enough knowledge to do 90% of the tasks you will ever need to use ADO for. In most cases with ADO there are several different ways to do a specific task, this tutorial will only demonstrate one way to do things, although in my opinion the way demonstrated here is the most clear, straightforward, and flexible way to do things, and the method I personally use all the time.
To use ADO in your project you must go to the Project Menu within VB and choose the References... option. This will bring up the references dialog box. Find the Microsoft ActiveX Data Objects 2.x Library. There are various version from 2.0 to the latest which I believe is 2.6 or 2.7. As long as its 2.0+ then you will be fine. If for some reason it doesn't appear as an option in this dialog you can install it by downloading MDAC (Microsoft Data Access Components) from http://www.microsoft.com/data/download.htm.
Now the first thing you need to do is create an ADODB.Connection object and use it to create an open connection to your database. To do this you create a variable of type ADODB.Connection, and create a new instance of it. You then have to specify a ConnectionString, which is a string containing all the information that ADO needs in order to find and access your database. It will include things such as what type of database it is and what database driver it needs to use (for example MS Access, SQL Server, Oracle, DB2, Informix, etc), it will also include the location of the database (for access this will be the location of the mdb file, for SQL Server this may be a server name or IP address), it may also include a user name and/or password that is needed to access the database, along with potentially lots of other information that the specific database you are connecting to requires. To see some templates you can use to construct your connection string from you can visit www.connectionstrings.com , they maintain a list of various template connection strings to access most of the popular databases. For this example I will be accessing an Access database, and the connection string will look like this: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;" . If you compare this to the template from www.connectionstrings.com you will notice I have left out the User ID and password sections, this is because I have not setup a user id/password on my particular access database. Once you have figured out what connection string you need to use, you set the .ConnectionString property of your ADODB.Connection object. You then call the .Open method of your Connection object to tell ADO to open the connection to your database. So far the code should look as follows:
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;"
MyConn.Open
Now we need to actually retrieve some data from the database. To do this you should have a basic understanding of SQL (Structured Query Language) as this is what I use to communicate with the database (it is possible to use ADO without using SQL at all, but I highly recommend using SQL statements as they give you better control and understanding of the communications taking place between ADO and your database). If you have never had to deal with SQL there is a good tutorial at www.w3schools.com/sql (and I plan on writing my own SQL tutorial in future). Learning and using simple SQL statements should be very easy for anybody, but SQL is also capable of doing VERY complex and powerful operations. So it is one of those things that is easy to learn, but difficult to master. For this tutorial I will only be using very basic SQL statements, and you can probably figure out what they are doing even if you have never seen SQL before in your life.
So to retrieve data from a database you have to use a SQL SELECT statement. Once you have figured out what the appropriate SQL statement should look like that will return the data you want, you send it to the database using the .Execute method of your Connection object. For this example the SQL statement I will be using will be "SELECT CustomerName, CustomerAddress, CustomerPhoneNumber FROM Customers". This will retrieve the Name, Address and Phone Number of all records (customers) in the table called Customers.
When executing SQL statments that return data (such as a SELECT) statement, the .Execute method will return an ADODB.Recordset object which will contain the data you requested. So the first thing you need to do is Dim a variable of type ADODB.Recordset, but you do NOT need to instantiate a new instance (using the New keyword) as the .Execute method will create it for you. Once you've done that, you use the Set keyword to set your recordset object equal to the result from the .Execute method. So far our code should look like this:
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;"
MyConn.Open
Set MyRecSet = MyConn.Execute("SELECT CustomerName, CustomerAddress, CustomerPhoneNumber FROM Customers")
MyConn.Close
After you have retrieved your data you should probably close the connection to the database (if you aren't planning on using it again in the near future). That is what the MyConn.Close line is doing there. As you can see, this code will create a connection to the database, execute a SQL statement to retrieve some data, and stuff the data into the MyRecSet object. It then closes the connection to the database.
Now we want to display the retrieved data on the form. I am just going to use a simple listbox to do this. I have placed a listbox on my form and called it lstData, I will put this data into it with 1 list item per customer, and I will display the data in the format: Name - Address - PhoneNumber. In order to do this we will have to loop through each record in the RecordSet object, and call .AddItem on the listbox. To do this we have to know a little bit about how the recordset works. The Recordset object is like a big list of data records. There is a current record that the recordset is "at" and it provides methods to allow you to move to the next record, the previous record, the first or the last record. It also provides methods to allow you to retrieve data from the current record. One important property is the .EOF property of the Recordset, this returns true if the current record is not valid, and false if it is valid. You can use this to check if there is any data in the recordset initially, if there was no data returned from your .Execute() call, then the Recordsets .EOF property will initially return True. Once we know we are on a current record we retrieve data from the Recordset by accessing the Fields collection. The Fields collection is used the same way as a standard VB collection, and it contains a member for each field (column) in the current record (row). You can access a particular member by using the .Item() property, and giving either a number representing the index of the field, or a string representing the name of the field. In my example the valid names would be "CustomerName", "CustomerAddress", and "CustomerPhoneNumber", or we could alternativly use the numeric indexes of 0, 1, and 2 respectively (although using the numeric indexes is generally considered a bad practice). This will get us an object representing the specified field of the current record, we can then use the .Value property to get the actual data out of it (if this seems long-winded there are coding shortcuts you can take which I will demonstrate in a minute). You will have to do this for every field you are interested in, and then when you have retrieved all the data you want from this record, you can move the current record using .MoveNext, .MovePrevious, .MoveFirst, or .MoveLast methods of the Recordset object. Once you have moved the current record don't forget to check the .EOF property again to make sure you have not gone past the end (or the beginning if you are moving backwards, which can be checked using .BOF) of the available data. So the code to retrieve our data and place it into a listbox will look like this:
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim strName As String
Dim strAddress As String
Dim strPhone As String
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;"
MyConn.Open
Set MyRecSet = MyConn.Execute("SELECT CustomerName, CustomerAddress, CustomerPhoneNumber FROM Customers")
Do Until MyRecSet.EOF
strName = MyRecSet.Fields.Item("CustomerName").Value
strAddress = MyRecSet.Fields.Item("CustomerAddress").Va lue
strPhone = MyRecSet.Fields.Item("CustomerPhoneNumber") ;.Value
lstData.AddItem strName & " - " & strAddress & " - " & strPhone
MyRecSet.MoveNext
Loop
MyConn.Close
Now the line strName = MyRecSet.Fields.Item("CustomerName").Value seems very long-winded and there are many ways that you can shorten that syntax. The following lines all do the exact same thing and are just coding shortcuts, you can use whichever one you prefer:
strName = MyRecSet.Fields.Item("CustomerName").Value
strName = MyRecSet.Fields.Item("CustomerName")
strName = MyRecSet.Fields("CustomerName")
strName = MyRecSet("CustomerName")
I would like to take a moment to mention another available syntax. The following is a valid syntax, and will do the exact same thing as the 4 lines above, however it is an obsolete syntax that is a left over from long ago. I would recommend not using it, and instead using one of the options from above.
strName = MyRecSet!CustomerName
You can also use similar code to update the data in your database. All you need to do is determine what the appropriate SQL statement would be. It will most likely be an UPDATE or an INSERT statement, and you pass it to the .Execute method of your connection object. ADO will send the SQL statement to the database, and the database will do the appropriate actions as specified by your SQL statement. You usually will not be expecting data to be returned from an UPDATE or INSERT SQL statement, so you will not need to store the result from the .Execute statement. This is a simple example of adding a new customer:
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;"
MyConn.Open
MyConn.Execute("INSERT INTO Customers(CustomerName, CustomerAddress, CustomerPhoneNumber) VALUES('Dylan Smith', '123 Fake Street', '(204) 888-8888')")
MyConn.Close
You do the exact same thing for updating existing records, except you replace the SQL statement passed to .Execute with the appropriate UPDATE SQL statement.
This hopefully has taught you the basic concepts you need in order to use ADO to interact with databases. ADO will allow you to interact with any database which has an ODBC or OLE DB compatible driver (which is pretty much every current database), and that driver is installed on the current PC. If you wish to do more complex database interaction, all you need to do is pass in more complex SQL statements to the .Execute method. ADO will also allow you to do things such as execute stored procedures on the database server (if your target database supports them), and retrieve metadata describing the structure of the database.
