Vba adodb in Excel

Before using ADO in Excel, let us see what is ADO?

ADO Stands for ActiveX Data Objects. ADO is used to exchange data between client and server.

But ADO cannot access data directly, it will do so with the help of OLEDB Provider.

OLEDB is a COM based interface that provides access to variety of data sources.

In simple terms, ADO is the object used to access data and OLEDB is the interface or bridge that connects ADO to the data sources.

The following figure illustrates this:

excel vba adodb

Here, the datasource may be anything, Access database, XML, MySQL or any other source of data.

The important things associated with ADO are

1.Data Source

2.SQL Commands

ADO uses SQL to communicate with the database.

SQL stands for "Structured Query Language".

The following steps are involved in accessing data using ADO in Excel

1.Open Connection to the database

2.Use SQL commands to manipulate data

3.Copy the result of the SQL in the worksheet.

4.Close the connection to database.

It is always a good programming practice to close the connection once the data is retrived.

For using ADO in VBA, a reference to the ADO library has to be added as shown below:

excel vba adodb

The following code shows how to connect to access database from vba:

  1. Sub ado_con()
  2. Dim con As New Connection
  3. Dim olecon,qry As String
  4. Dim rset as New RecordSet
  5. olecon="Microsoft.ACE.OLEDB.12.0; Data Source=D:\test.accdb;User Id=admin;Password=admin"
  6. con.Open(olecon)
  7. qry="SELECT * FROM table_name"
  8. rset.Open qry,con
  9. rset.Close
  10. con.Close
  11. End Sub


You can find similar Excel Questions and Answer hereunder

1) Want to use SQL to manipulate a database. Here some basic explanation to get you started.

2) How do I use Find to determine last occurrence of a string in a WorkSheet range using VBA?

3) What are the com addins in the developer tab of Excel

4) How to go quickly to a sheet when you have many sheets in a workbook. How to index your pages

5) Here some explanations about xml and how to use xml in VBA

6) How to create forms in Excel VBA

7) how do you enter multiple line in a cell in Excel

8) How can I set non-contiguous print area using VBA?

9) Here some explanations about the XML DOM node in VBA, DOM stands for Document Object Model

10) How can I filter a table to get all records that have less than a particular value in a specific column?


Here the previous and next chapter