ADO Tutorials - Lesson 2: Object-Oriented ADO

	Object-Oriented ADO

Connections

Introduction

To make database programming more professional and constructive, ADO uses a series of objects. The objects mimic their concept from real life. Consider an object like a house. It has some characteristics like the number of rooms, the color of the roof, the type of house, etc. In the programming world, a characteristic of an object is called a property. A property is a word that describes an object. An object can also carry assignments. For example, one of the assignments of a house is to protect from rain the people who live in it. In the programming world, an assignment carried by an object is called a method.

Practical Learning: Introducing ADO

If you want to follow the lessons on this site, open Windows Explorer and, on the C: drive, create a folder named ADO Lessons
Open Microsoft Visual Basic 6 or the programming environment you will use for these lessons (remember that, and for the rest of our lessons, we will use Visual Basic 6)
On the opening dialog box, click Standard EXE and click Open
Start saving the project. Create a new folder inside of the ADO Lessons folder and name it Fundamentals:
Display the new folder in the Save In combo box
Change the name of the form file to Main and click Save
Save the project as Fundamentals

ADOX Fundamentals

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. The ADOX object is equipped with the Catalog property. This property defines most of the objects you will need to use in your databases, the objects we will start introducing in the next lesson. 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 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.

Practical Learning: Getting a Reference to ADO

If you are using Visual Basic 6, on the main menu, click Project -> References...
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
Click OK
On the Toolbox, double-click the CommandButton
Change its Caption to Create Database and its Name to cmdCreateDatabase
Double-click the button

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 catConnection As ADOX.Catalog
    
    Set catConnection = Nothing
End Sub

Save all

Database Creation

Introduction

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 a semi-colon.

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

Probably the most used provider in Visual Basic 6 databases 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 "provide=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 in the C: drive, you can specify the connection string as follows:

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

To be safer, you can also include the value of the data source in single-quotes:

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

Practical Learning: Creating a Database

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

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

To execute the application, press F5
To create the database, click the Create Database button:
Open Windows Explorer and open the folder that contains the current project to check that the database has been created:
Return to MSVB


Previous	Copyright © 2001-2005 FunctionX, Inc.	Next