Using ADO in Excel VBA to retrieve records from databases.

ADO refers to ActiveX Data Objects and is one of the languages used by the industry to pull data from databases. ADO is readily available in Excel VBA. All you have to do is to make reference to the objects the VBA editor. There are a number of versions in VBA but I recommend using version 2.5 if your client could be using an older verion of Excel. If not, I recommend using version 2.8 which I did in one of my projects. I am using Excel 2002/XP and version 2.8 is already available in my VBA. To activate the reference, go to the menu bar in the Visual Basic Editor (VBE) and select Tools -> References. In the pop up window, look for Microsoft ActiveX Data Objects 2.8 Library. Check the box on the left and click OK. You are now use ADO to pull data from databases such as Microsoft® Access, DBASE, SQL Server, IBM AS400.

Here are some sample codes you can use to retrieve databases. The very first thing you must do is to establish a connection to the database. You can use the following module to help you connect to an SQL server.

Public Sub sqlConnOpen(myServerName, MyDatabaseName)

If sqlConn.State = 1 Then sqlConn.Close
sqlConn = "Provider=sqloledb;" & _
            "Data Source= " & myServerName & ";" & _
            "Initial Catalog=" & MyDatabaseName & ";" & _
            "Integrated Security = SSPI;" & _
            "ConnectionTimeout = 120;" & _
            "CommandTimeout=120"

sqlConn.Open
            
End Sub

To connect using the above sample codes, you must create another sub procedure e.g. "Demo"

Sub Demo ()
myServerName = "sql_server"
MyDatabaseName = "sql_database"

'Open the connection.
    Call sqlConnOpen(myServerName, MyDatabaseName)
End Sub

After establishing a connection to the database, you can then use another module to retrieve the records from the data table(s) in the database.

Public Sub sqlRSRun(ByVal SqlStr As String)
On Error GoTo Er

    Set sqlRS = Nothing
    sqlRS.Open SqlStr, sqlConn, _
		adOpenStatic, adLockOptimistic

Exit Sub
Er:
    MsgBox Err.Description, _
		vbCritical, "Error # " & Err.Number
    
End Sub

You can then add the following command in the main module to retrive the records. This must be in the same module where you have establish the connection to the database.

Call sqlRSRun("Select * FROM ABC_Table")

Sub Demo ()
myServerName = "sql_server"
MyDatabaseName = "sql_database"

'Open the connection.
    Call sqlConnOpen(myServerName, MyDatabaseName)
	
'Get Sql Recordset
Call sqlRSRun("Select * FROM ABC_Table")
	
End Sub

To put the records in an Excel worksheet, the fastest way is to use the CopyFromRecordset command. This is how you use it

Thisworkbook.Worksheet("Sheet1").Range("A2").CopyFromRecordset sqlRS

Add this into the main Code, you will have the following set of code:

Sub Demo ()
myServerName = "sql_server"
MyDatabaseName = "sql_database"

'Open the connection.
    Call sqlConnOpen(myServerName, MyDatabaseName)
	
'Get Sql Recordset
Call sqlRSRun("Select * FROM ABC_Table")

Thisworkbook.Worksheet("Sheet1").Range("A2"). _
	CopyFromRecordset sqlRS
	
End Sub

You can add in the column headers using the following code. This is why I started my range in A2 and not in A1. The reference to cells is another way to make reference to a cell in Excel, by using row and column number. Cells(1,1) refers to A1.

For iCols = 0 To sqlRS.Fields.Count - 1
    Thisworkbook.Worksheet("Sheet1"). _
		Cells(1, iCols + 1).Value = _
		sqlRS.Fields(iCols).Name
Next

Learn how to use ADO to join 2 or more data tables together




New! Comments

Have your say about what you just read! Leave me a comment in the box below.