VBA to Query Database and Spread the Records in Excel

.

In continuation to one of my article which shares the connection strings to connect to different data bases. In this article I explained and shared every possible VBA code to make connection with many databases like Oracle, SQL etc. but does the Story ends there?? NO WAY !! That’s the starting point. You are going to make connection to Database ONLY when you need to execute any kind of query query. It could be a SELECT, UPDATE, INSERT etc.

Therefore in this article I am going to complete my pending story by sharing the Excel Macro to Query a Database and fetch records and populate them in WorkSheet. A BIG thanks to one of my friend Anil and an avid reader of my blog, for reminding me to complete this article.

Though Queries (SQL Statements) are categorized in many different categories like DDL (Data Definition Language), DML (Data Manipulation Language) etc. based their way of functioning.
Here to execute a Query in excel macro, Queries can be categorized in to two categories:

1. Queries which returns NO records like DELETE, INSERT, UPDATE, ROLLBACK, COMMIT… etc.

2. Queries which are tend to return records like SELECT queries.

Method of executing both the queries, in excel macro, are different which are explained with example below.

1. Excel Macro to execute an INSERT, UPDATE, DELETE Statement in DB

Queries which does not return any record after running, can be executed by “YourConnectionObject”.Execute “Your Query”.
This statement does not return anything. It simply executes your query and performs the respective operation.

Following is the Excel Macro Code which connects to the data base and execute your given query. It could be like INSERT Query, DELETE, UPDATE, COMMIT, ROLLBACK etc.


Function ExecuteQuery()
	Dim DBcon As ADODB.Connection
	Set DBcon = New ADODB.Connection
	Dim DBHost As String
	Dim DBPort As String
	Dim DBsid As String
	Dim DBuid As String
	Dim DBpwd As String
	Dim DBQuery As String
	Dim ConString As String
	On Error GoTo err
' DB connectivity details. Pass the correct connectivity details here
	DBHost = "Host Address"
	DBPort = "Port Number"
	DBsid = "SID to Connect DB"
	DBuid = "User ID"
	DBpwd = "Password"
'Connection string to connect to Oracle using SID
	ConString = "Driver={Microsoft ODBC for Oracle}; " & _
	"CONNECTSTRING=(DESCRIPTION=" & _
	"(ADDRESS=(PROTOCOL=TCP)" & _
	"(HOST=" & DBHost & ")(PORT=" & DBPort & "))" & _
	"(CONNECT_DATA=(SID=" & DBsid & "))); uid=" & DBuid & "; pwd=" & DBpwd & ";"
'Open the connection using Connection String
	DBcon.Open (ConString) 'Connecion to DB is made
	DBQuery = "Your Query" 'like UPDATE, DELETE, INSERT etc.
'below statement will execute the query
	DBcon.Execute DBQuery
	MsgBox ("Query is successfully executed")
'Close the connection
	DBcon.Close
	Exit Sub
	err:
	MsgBox "Following Error Occurred: " & vbNewLine & err.Description
	DBcon.Close
End Function

2. Excel Macro to execute a SELECT Statement which returns Records

In this case when you run your query then you expect a set of recordset returned by the query and ofcourse!! you want to capture them and spread them in your workSheet.

Following statement is used to execute a query to get get the recordset. RecordSetObject.Open Your query, DB Connection Object.
Refer the below excel macro which does following tasks:

1. Makes connection with Oracle Database

2. Executes your Query to get Record Set as a result of the Query

3. Spread all the records in to your Excel Sheet named “Data”

Statement used to spread all the records of a recordSet in Excel Sheet (Without Loop)

Sheets(“data”).Range(“A2”).CopyFromRecordset RecordSetObject

 


Function FetchRecordSetQuery()
	
	Dim DBcon As ADODB.Connection
	Dim DBrs As ADODB.Recordset
	Set DBcon = New ADODB.Connection
	Set DBrs = New ADODB.Recordset
	Dim DBHost As String
	Dim DBPort As String
	Dim DBsid As String
	Dim DBuid As String
	Dim DBpwd As String
	Dim DBQuery As String
	Dim ConString As String
        Dim intColIndex as Integer
	On Error GoTo err
' DB connectivity details. Pass the correct connectivity details here
	DBHost = "Host Address"
	DBPort = "Port Number"
	DBsid = "SID to Connect DB"
	DBuid = "User ID"
	DBpwd = "Password"
'Connection string to connect to Oracle using SID
	ConString = "Driver={Microsoft ODBC for Oracle}; " & _
	"CONNECTSTRING=(DESCRIPTION=" & _
	"(ADDRESS=(PROTOCOL=TCP)" & _
	"(HOST=" & DBHost & ")(PORT=" & DBPort & "))" & _
	"(CONNECT_DATA=(SID=" & DBsid & "))); uid=" & DBuid & "; pwd=" & DBpwd & ";"
'Open the connection using Connection String
	DBcon.Open (ConString) 'Connecion to DB is made
	DBQuery = "Your Query" 'like UPDATE, DELETE, INSERT etc.
'below statement will execute the query and stores the Records in DBrs
	DBrs.Open DBQuery, DBcon
	If Not DBrs.EOF Then 'to check if any record then
' Spread all the records with all the columns
' in your sheet from Cell A2 onward.
		Sheets("data").Range("A2").CopyFromRecordset DBrs
'Above statement puts the data only but no column
'name. hence the below for loop will put all the
'column names in your excel sheet.
		For intColIndex = 0 To DBrs.Fields.Count - 1  ' recordset fields
			Sheets("data").Cells(1, intColIndex + 1).Value = DBrs.Fields(intColIndex).Name
		Next
	End If
'Close the connection
	DBcon.Close
	Exit Function
	err:
	MsgBox "Following Error Occurred: " & vbNewLine & err.Description
	DBcon.Close
End Function

Above codes are examples to show, how to execute a query and spread the records across the Excel Sheet without using a Loop. I have taken an example of connecting Oracle Database using SID connection. If you have to connect to some other database using Service etc then go through my previous articles to get the Connection String for such connections. If you still face any issue regarding connecting to your DB or querying the DB kindly mail me.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

9 Comments

  1. Arun MC

    when I run the 2. code i got below error and I suspect the
    ” Dim DBcon As ADODB.Connection
    Dim DBrs As ADODB.Recordset
    Set DBcon = New ADODB.Connection
    Set DBrs = New ADODB.Recordset ”

    what these from where it getting the values.

    Compiler error:

    User-defined type not defined

    Reply
    • Arun MC

      and can not follow these steps because I don’t find the ” Tools –> References” please step by step procedure to Add reference for ADODB Connection.

      1. Go to VB Editor Screen (Alt+F11)
      2. Tools –> References…
      3. From the List of Available References Select “Microsoft ActiveX Data Objects 2.0 Library” You can select 2.0 Version or any other higher version of this reference.
      4. Click OK

      Reply
      • Arun MC

        I came to know How to add Microsoft ActiveX Data Objects 2.0 Library.

        But when I try to Run the code. It says

        Following Error Occurred
        [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

        Please do the needful.

        Thanx,
        Arun

        Reply
        • sonam

          sFollowing Error Occurred
          [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

          Reply
  2. Arul

    Hi,

    I was able to connect to database using connection string. but when i try to query and store in the record set and getting the following error.

    query=”SELECT * FROM .”
    rs.Open query, con

    Run-time error ‘-2147467259(80004005)’:
    Unspecified error

    Reply
    • Arul

      I get this error when i try to copy any DB columns of type TIMESTMAP. Dont get this issue when i select only VARCHAR2 columns.

      How to store the TIMESTAMP columns to a recordset.

      Reply
  3. Arul

    HI,

    Am using the code provided by you. I connected to the database successfully but when but am getting the below error when i try to extract the records to teh record set.

    query = “SELECT * FROM .”
    rs.Open query,con

    Run-time error-‘2147467259(80004005)’

    Can you please help..

    Reply
  4. Arjun Kumar

    How to convert this function in to a macro ?

    Reply
  5. Arun MC

    Yes, You correct, to practise that, error free right, how fix this error??

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro Different ways of Reading RecordSet Object in Excel Macro - [...] my previous article, I emphasized mainly on how to execute different kind of queries in database using Excel VBA.…
  2. Different ways of Reading RecordSet Object in Excel Macro - Welcome to LearnExcelMacro.com - […] my previous article, I emphasized mainly on how to execute different kind of queries in database using Excel VBA.…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest