Readers of this tutorial will learn how to make a Universal Data Link file which provides the basis for connecting to an MS Access database file. They will also go through the steps to be followed in writing VBA code to work with the database in accessing the Connection object.
Access 2000 was a major milestone in MS Access as Microsoft shifted gears to make the OleDb COM interface its core data connectivity technology. This new technology, ActiveX Data Objects, became the core for MS Access and other related Office products. OleDB continues to grow as a Universal Data Access vehicle to connect to a very large number of databases including the legacy data.
This tutorial is very basic, written with the very limited objective of encouraging readers who might be planning to use ADO (DAO, RDO, E-SQL and DB Library are going to be a part of the history). The tutorial explains how to make a Universal Data Link file which provides the basis for connecting to an MS Access database file, or for that matter any other database product that has a OLEDB driver. It also describes the steps to be followed in writing VBA code to work with the database in accessing the Connection object. A few more tutorials in the works will describe fully all aspects of ADO you need to know.
Microsoft Data Access Strategies and ADO
MDAC, or Microsoft Data Access Components, take care of connecting to a variety of relational and non-relational data sources. The components in this MDAC stack are:
ADO (including ADOMD and ADOX): This is a high-level language that is continuously being upgraded. Its performance is somewhat inferior to direct use of OleDb or ODBC. It has great scripting support with VB and JScript. ADOMD is for Multidimensional uses such as in an Analysis Server and OLAP. ADOX is an ADO extension to take care of DDL and security related aspects of data.
OleDb (SQL OleDb Provider, OleDb providers for Oracle, odbc drivers, data shape and remote data): This is the key to opening most databases of any form, including messaging, active directory, and so on.
ODBC (including SQL ODBC and Oracle ODBC drivers): This is a C language interface for relational data.
Each version of ADO gains a few new features. This tutorial gives the most essential items needed for the current discussions. Starting with ADO 2.5, the ADO object model consists of five separate parts, each of which concentrates on a single issue. The five objects of this model are the Connection, Command, Recordset, Record and the Stream. The last of these gives access to data stored on a local machine. In the next couple of tutorials I will be exploring each of these objects in greater detail, providing concrete and full examples of their usage.
The next picture shows the ADO object model. In terms of the number of objects, this model is very superior to the DAO of a bygone era. It could not get simpler than this. Think of collections, and the individual items in the collection, and you are already halfway into the guts of the object. In this tutorial I will be concentrating on the Connection, because everything else starts after the connection.
The tutorial steps
We will be following step-by-step the line items shown here for this tutorial. Well, although you will be opening and closing a connection, the correct order is to open a connection, open a record set, use the recordset for some purpose, close the recordset, and close the connection. We will not discuss the use of recordset in this tutorial; we'll cover it in part two.. The summary of steps you will be following are as follows.
Create a UDL file to connect to an MDB file.
Read the connection string from the file.
Create a MDB file, add a form, a button, and so on.
Establish a reference to ADO.
Write code to open and close the ADODB connection to the database.
For ODBC you straight away click on the Control Panel to access ODBC Data Sources in the Administrative tools. However, for ADO you adopt a different procedure. You create what is called a "UDL" file. You need this because it provides you with the mantra to connect to a database, called the connection string, the single most important thing for connectivity.
It's very easy to create a UDL file. Create an empty text file and save it with some (any) name but save it with the extension UDL. I created a text file adotest.txt and renamed it adotest.udl. Windows may warn you that it may not be usable but you may ignore the warning. When you place your mouse over it, the tool tip tells you it is a Microsoft Data Link file. Right click the file and click on properties; this pops up the window shown in the following picture. It opens with the General tab in view as seen here.
Click on the Provider tab, one of the important parts of the connection string. This opens up the tabbed window as shown in the next picture. You can see a whole lot of providers, and there are drivers written by many companies that you may not see here. Highlight the Microsoft Jet 4.0 OLE DB Provider and move over to the next tab.
This brings you to the Connection tab, where you indicate the source of your Jet database. In this case you browse to the location of the mdb file on your machine, perhaps C:/My Documents folder. In my case it is in the samples folder of Office 11 / MS Access 2003. The default permissions are set for Admin as User Name and a blank for the password.
Now you may test the connection as shown here. The Advanced tab is to configure the access permissions. It is set to share deny none as default, and the All tab lists the details and initialization chosen for this kind of source. It is possible to edit information in this window. The defaults were accepted for this tutorial. This completes the process of creating the UDL file.
In reality the UDL file that was created is a text file. Right click and opt to open with a text editor. For the adotest.udl file, I have the following information, shown in the next paragraph. It's really very simple, especially when the authentication information is set for the Admin.
[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\
Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;
Persist Security Info=False
Create an MDB file, create a new form and add module
Just open your MS Access application and choose to Create a New File... which prompts you for a number of options. Choose to create a blank database. For this tutorial, I created an AdoTest.mdb file. I added a form and button with the caption Open/Close a database, as shown in the next picture. Since I do not need navigation and other related items, I chose not to show them by choosing property values for the items in the form's properties page. Instead of the Command button's click event, you may also use other events of other controls or even a Module.
Open the form in design view by first highlighting Form1 and then choosing Design View. Right click on the Command button, click on Build Event... and in the Choose Builder pop-up that comes up, click on Code Builder.
You will get to the screen shown next. If you want to go back to the form just click on the MS Access icon on the tool bar. In addition to Form1, I also have a Form2, which is not used in this tutorial. The code page for Form1 is called AdoTest - Form_Form1(code). This is where the code for the form and for any controls on the form are written. Presently there is no code except an empty click event for the Command0 button.
This is an important step where you will establish a reference to the Microsoft ActiveX Data objects library, which allows you to use the methods, properties and events related to these objects. In the Microsoft Visual Basic screen go to Tools, click on it (do not right click), and from the drop down click References (it is usually the first one in the list). This brings up the window References - AdoTest as shown. You will see references to a whole lot of ADOs. Here the ADO 2.8 library is chosen; click OK. With this we can use the msado15.dll.
While you are in this screen, go and take a peek at the ADODB related objects in the Object Browser. I am showing the Connection property of ADODB. Items related to the connection are listed on the right hand side of this window. You can see how rich the interface is and how flexible it could be.
This is a very short script and you get the assistance of intellisense technology. Without this help, lazy as I am, I would not have taken to Microsoft technology; no need to thumb hefty volumes in the shadow of your manager hawk. Just type in the code window ADODB, and you will get a drop-down as shown, giving you all the object choices you can make: it could be a connection, it could be a command, and so forth. Insert the following code as shown in the next paragraph in the click event of the button.
Private Sub Command0_Click()
Dim conn As New ADODB.Connection
Dim prop As Property
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\
Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;
Persist Security Info=False"
conn.Open strg
MsgBox ("connection open")
conn.Close
MsgBox ("Connection closed")
End Sub
You created a new connection object called conn. The variable string is the one that you copied from the UDL's file while it is displayed as a text, the mantra. The connection can be opened, if you provide the connection string. The First message says the connection is Open. Trust me, the connection is really open. It is always a good practice to close the connection, and therefore you see the conn.close statement. The second message box shows that the connection is closed. If the connection is closed you have no access to the data. This is a basic step you need to take when you want to access data.
Review connection properties using code
You saw earlier in the Object Browser the driver specific connection properties for the referenced ADO library. You might also have seen some of them in the All tab of the data link window. We will look at the same using code since we have been able to open a connection.
Add some code to modify the earlier code as follows. In ADO it is all about collections. The property collection is one such collection (refer to the ADO Model). The variable prop is declared to be a property, conn.Properties gives you access to all the properties, and the For... Next logic block gives you all the properties pertinent to this connection. We will also find out certain other properties of the connection object, such as the Ado Version in use, the connection state, and the default connection timeout.
Just remember to add a textbox to the form before running the page and make sure the Text box number matches with what you are adding to the form. The message boxes when they appear should be clicked OK; they are left intentionally in code for you to follow the code as it gets executed. The variable strAdd just gathers up all the strings and finally places them in the text box.
Private Sub Command0_Click()
Dim conn As New ADODB.Connection
Dim strAdd As String
strAdd = ""
MsgBox ("ADO version used is: " & conn.Version)
strAdd = strAdd + "ADO version used is: " & conn.Version
Dim prop As Property
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\
Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;
Persist Security Info=False"
conn.Open strg
MsgBox ("connection open")
'
MsgBox (conn.State)
strAdd = strAdd & vbCrLf & "Connection status (open) is :
" & conn.State MsgBox ("The default connection timeout is: " & conn.ConnectionTimeout)
strAdd = strAdd & vbCrLf & "The default connection timeout is:
" & conn.ConnectionTimeout
'
For Each prop In conn.Properties
Debug.Print prop.Name, "=", prop.Value
Next prop
'
conn.Close
MsgBox ("Connection closed")
MsgBox (conn.State)
strAdd = strAdd & vbCrLf & "Connection status(closed) is:
" & conn.State
With Text7
.SetFocus
.Text = strAdd
.BackColor = RGB(0, 0, 0)
.ForeColor = vbYellow
End With
End Sub
When you run this code you will see the following window after you click OK to the various messages that show up.
In the immediate window you will see the message printed that lists all the properties for this source. Go to Microsoft Visual Basic Editor screen and open up the immediate window. The following is what you will see (only a part presented).
Summary
ADO is easier to handle than DAO. Anyway DAO is out and ADO is in. It has great scripting support and you have intellisense to make life a lot easier. The UDL file is a powerful means to connect to OLEDB sources, of which there are very many. In this tutorial only a couple of properties of the Connection object are described. There are other properties, and these will be presented after looking at some of the related other objects.
Microsoft Access 
/ 27 
Using the Recordset with MS Access and ADO
