Microsoft ActiveX Data Objects (ADO)

Microsoft ActiveX Data Object (ADO) is a Windows 2000 (or later) component for accessing databases. It doesn’t need to be distributed because it belongs to its core services.
ADO has the object name “Microsoft ActiveX Data Objects 2.x Library”. The “x” refers to the version number and it varies on every machine. Loading this, please see Referencing Object discussion.

The first step to making use of the ADO is connecting to the database. You need to have the correct “Connection String” prior to connecting to the database. If you don’t know exactly what Connection String you have to use, you can use the Connection String builder I discussed in Building Database Connection String Programmatically. Once you have your Connection String, you can store it in a variable or you can save it in worksheet cell for future use with your program.

Below is a sample code on how to make connection to the database:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.mdb;"

If your Connection String is in a cell in a worksheet, for example, it is in Cell A1 of Sheet1, the Connection String in the code is can be replaced with the following:

cn.Open Sheets(“Sheet1”).Cell(1,1)

or,

cn.Open Sheets(“Sheet1”).Range(“A1”)

Once connected to the database, you can now do the data manipulation. You can create your own table and columns. You can add, edit or delete a record to the database.

In my future posting, I will include the following topics to help you in learning ADO in details:
3 Main Objects of ADO

• What is a Recordset?
• What is a Cursor?
• Executing a Command with ADO
• Retrieving and Manipulating Data with ADO
• ADO – Open Method
• ADO – Close Method
• Updating Database using ADO

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.