ADO Excel VBA – SQL Connecting to Database Example Macros helps to connect the different data sources from Excel VBA. Select, Delete,Update Records set.
In this Section:
- What is ADO?
- What is Database?
- What is SQL?
- adodb.connection VBA Reference
- Practical Learning: Using ADO and SQL with VBA
- Example File
What is ADO?
ADO Stands for ActiveX Data Objects, is Microsoft’s Client-Server technology to access the data between Client and Server. ADO can’t access the data source directly, it will take help of OLE DB Provider to communicate with the data source. Most of the times OLE DB providers are specific to a particular Data Source Type. However, we have an OLE DB provider for ODBC, it is a general purpose provider with help of this ADO can access any Data source which can understand ODBC.
What is Database?
Database (DB) is a collection of information organized in such a way that a computer program can easily understand and read the data. And the Database Management System (DBMS) are designed to understand and interact with other computer applications to perform the different operations on the data. MySQL, Microsoft SQL Server, Microsoft Access, Oracle, and IBM DB2 are some of the well know DBMS.
Generally the information stored in the data in the form of tables, and a table is designed with set of records (rows) and fields (columns).
You can use Microsoft Excel to store some data, where an Excel workbook will act as a data source, worksheet will be a table and the rows and the columns of the worksheet will be records and the fields of the table.
What is SQL?
SQL Stands for Structured Query Language, ADO use SQL commands to communicate with the databases. Following are the most commonly used SQL commands to deal with the databases:
|SELECT command used to retrieve the data from a data source|
|INSERT command used to insert the records to a data source|
|UPDATE command used to modify the existing records of the data source|
|DELETE command used to delete the records from a data source|
adodb.connection VBA Reference
adodb.connection VBA Reference helps as to refer ADO in Excel VBA. We can use ADO in Excel VBA to connect the data base and perform data manipulating operations. We need add ‘Microsoft Activex Data Objects Library’ from References to reference the ADO in VBA. Here is the adodb.connection VBA Reference screen-shot.
ADO in Excel VBA – Practical Learning: Using ADO and SQL with VBA
To retrieve the data from any data source into Excel using ADO:
1. We have to Open the connection to the Data Source
2. We need to run the required SQL command
3. We have to copy the resulted record set into our worksheet
4. We have to close the record set and connection
We will consider the Excel workbook as data source and we will connect to the worksheet (table) to retrieve the data. In this example we will get the data from Sheet1 to Sheet2 using ADO.
Assuming you have an excel workbook with the following data in Sheet1, as shown below.
Step 1:Add reference for Microsoft Activex Data Objects Library
1. Go to VBE (Alt+F11) and Select References.. from Tools Menu.
2. Then select ” Microsoft Activex Data Objects Library” from the list.
3. And Create sub procedure to write the code:
Sub sbADOExample() 'We will write the code here End Sub
Step 2: Create the Connection String with Provider and Data Source options
Dim sSQLQry As String Dim ReturnArray Dim Conn As New ADODB.Connection Dim mrs As New ADODB.Recordset Dim DBPath As String, sconnect As String DBPath = ThisWorkbook.FullName 'Refering the sameworkbook as Data Source 'You can provide the full path of your external file as shown below 'DBPath ="C:\InputData.xlsx" sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';" 'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB: 'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Step 3: Open the Connection to data source
Step 4: Create SQL Command String
sSQLSting = "SELECT * From [Sheet1$]" ' Your SQL Statement (Table Name= Sheet Name=[Sheet1$])
Step 5: Get the records by Opening this Query with in the Connected data source
mrs.Open sSQLSting, Conn
Step 6: Copy the reords into our worksheet
Step 7: Close the Record Set and Connection
'Close Recordset mrs.Close 'Close Connection Conn.Close
So, the final program should look like this:
Sub sbADOExample() Dim sSQLQry As String Dim ReturnArray Dim Conn As New ADODB.Connection Dim mrs As New ADODB.Recordset Dim DBPath As String, sconnect As String DBPath = ThisWorkbook.FullName 'You can provide the full path of your external file as shown below 'DBPath ="C:\InputData.xlsx" sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';" 'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB: 'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" Conn.Open sconnect sSQLSting = "SELECT * From [Sheet1$]" ' Your SQL Statement (Table Name= Sheet Name=[Sheet1$]) mrs.Open sSQLSting, Conn '=>Load the Data into an array 'ReturnArray = mrs.GetRows ''OR'' '=>Paste the data into a sheet Sheet2.Range("A2").CopyFromRecordset mrs 'Close Recordset mrs.Close 'Close Connection Conn.Close End Sub
You can download the example file here and explore it.
Download – Example File: ANALYSIS TABS – Getting Data Using ADO (Using MSDASQL Provider)