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.