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:
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:
The following code shows how to connect to access database from vba:
- Sub ado_con()
- Dim con As New Connection
- Dim olecon,qry As String
- Dim rset as New RecordSet
- olecon="Microsoft.ACE.OLEDB.12.0; Data Source=D:\test.accdb;User Id=admin;Password=admin"
- con.Open(olecon)
- qry="SELECT * FROM table_name"
- rset.Open qry,con
- rset.Close
- con.Close
- End Sub