Introduction to ADO and ADOX

Microsoft ActiveX Data Objects

Introduction

Microsoft created DAO in the beginning to provide a programming library for Microsoft Access. To allow other environments, including those that were not from Microsoft, to create and manage databases that could communicate with Microsoft Access, the company created a library called ActiveX Data Objects or ADO. ADO was meant to create, administer, and manipulate databases.

Although treated as one entity, Microsoft ActiveX Data Objects is really many libraries grouped under one name. Starting with Microsoft Access 2000, the ADO library is included, or "loaded" by default when you start a new database. To check this, you can open the References dialog box:

Notice that a check mark is already placed in the Microsoft ActiveX Data Objects Library check box. You should check the Microsoft web site regularly to find out if the company has released a new version of the library. If it has, you should download it to update yours.

Before using ADO, you should first create a database using an application such as Microsoft Access or by other related means. Among the limitations of ADO in the beginning, there were security and even the ability to create a database without necessarily using a product from Microsoft. To address these issues, Microsoft created an additional library called  Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security, abbreviated ADOX. Before using ADOX, you must reference it in Microsoft Visual Basic. To do this, you can open the References dialog box from  the Tools menu and select your latest version of Microsoft ADOX.

Like Microsoft Access' own library and like DAO, ADO relies on objects for its functionality. The most fundamental object used in ADO databases is ADOX. To use this object, you must create a reference to it. To do this, first declare a variable of type ADOX.

To support the creation of a database, the ADOX library provides the Catalog object. To access this object, the ADOX object is equipped with a property called Catalog. This property defines most of the objects you will need to use in your databases. To use the Catalog property, specify it when declaring the ADOX variable. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
End Sub

Before using the Catalog property of the ADOX object, you must specify that you need a reference to it, this can be done by initializing your variable with ADOX.Catalog using the New operator. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
End Sub

You can also refer to this property when declaring the variable. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As New ADOX.Catalog
End Sub

Whenever you create an ADO or ADOX object and while it is being used, it consumes resources. Therefore, after using the object, you should/must remove it from memory (if you don't, you may start seeing abnormal behavior on your computer after a while, as resources become scarce for other objects or applications are trying to use the same resources). To remove a resource from memory, after using it, assign it the Nothing constant.

1. On the main menu of Microsoft Access, click Tools -> Macro -> Visual Basic Editor
2. On the main menu of Microsoft Visual Basic, click Tools -> References...
3. In the Available References list check box of the References dialog box, click the check box of Microsoft ADO Ext. 2.7 For DDL And Security
    
   
4. Click OK
5. To return to Microsoft Access, on the Standard toolbar, click View Microsoft Access
6. In the Database window, click the Forms button then right-click Fundamentals and click Design View
7. Right-click the Create Database button and click Build Event...
8. In the Choose Builder dialog box, double-click Code Builder
9. To get a reference to ADOX, declare the following variable and make sure you set it to Nothing before the closing of the procedure:
    

   Private Sub cmdCreateDatabase_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = Nothing End Sub

10. Save all

Once you have gotten a reference to the object you want to use, you can perform a related operation. The primary operation you can perform consists of creating a database. This is done using the Catalog property of the ADOX object. To create a database, you can call the Create() method of the Catalog. This would be started as follows:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create
End Sub

This method takes as argument a string, also called a connection string, that defines the assignment to perform. The string itself is internally made of different sections separated by  semi-colons.

The first part of the connection string is called the provider. It specifies the platform used to handle the database. To specify this factor, assign the desired name to the provider word. This would be done as:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider="
End Sub

Although there are various providers in the industry, there are two primary providers used in ADO. One of them is Microsoft SQL Server and it is represented by SQLOLEDB. Therefore, if your database will reside on a SQL Server, you can specify the provider as follows:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider=SQLOEDB"
End Sub

The most common provider we will use is the Microsoft JET database engine. It is represented as Microsoft.JET.OLEDB.4.0. You can use it to specify the provider as follows:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider=Microsoft.Jet.OLEDB.4.0"
End Sub

You can also include the name of the provider as its own string. To do that, you can include it in single-quotes:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider='Microsoft.Jet.OLEDB.4.0'"
End Sub

If you are creating a SQL Server type of database, there are various other details you must provide to the connection string. For the rest of our lessons, we will use Microsoft JET. When using Microsoft JET, the second part of the connection is referred to as the data source. If you are creating a database, you can specify the path and the name of the database to the Data Source factor. The path must consist of the drive and the folder(s). After the last folder, the name of the database must have the .mdb extension. For example, to create a database called Exercise that would reside in a folder called Exercises of the C: drive, you can specify the connection string as follows:

Instead of directly passing a string to the Create() method, you can first declare a String variable, initialize it with the necessary provider/data source, and then pass that string variable to the Create() method. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    Dim strCreator As String
    
    Set objCatalog = New ADOX.Catalog
    strCreator = "provider=Microsoft.Jet.OLEDB.4.0;'"
    strCreator = strCreator & "Data Source=C:\Exercises\Exercises.mdb'"
    
    objCatalog.Create strCreator
End Sub

Practical Learning: Creating a Database

1. To create a new database, change the event as follows:
    

   Private Sub cmdCreateDatabase_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Microsoft Access Database Development\Fundamentals.mdb'" MsgBox "A new Microsoft JET database named Fundamentals.mdb has been created" Set catConnection = Nothing End Sub

2. Return to Microsoft Access and switch the form to Form View
3. To create the database, click the Create Database button
4. Open Windows Explorer and open the C:\Microsoft Access Database Development folder to check that the database has been created
5. Return to Microsoft Access
6. Save and close the form