ADO Recordset Basics

I get asked how to hold an Excel query table in memory from time-to-time, so it’s time for a post about it. You can use ADO (ActiveX Data Objects) to query a database object and get a recordset without having to write it to a worksheet. This post shows how I do it, and I’m no expert on the subject, so be sure to read the comments for errata and performance issues.

First, I create an Excel external data table normally through the Data > External Data menu. Then I copy the pertinent information for that query to my code. Finally, I delete the external data table. I do this because I don’t want to remember how to write connection strings and sql statements. I know, I’m lazy.

With the external data table created, I go to the Immediate Window to get what I need.

ADOBasics1

The CommandText property is the SQL statement (it was the SQL property in Excel97 and that still works in later versions). The Connection property is the connection string you’ll need to get access to the database. I paste these strings into variables in my code, clean the up a little, and add line continuation characters for easier reading.

Next, I set a reference (VBE – Tools > References) to the Microsoft ActiveX Data Objects x.x Library. Generally, pick the highest number for x.x that shows. Mine starts with 2.0 and goes to 2.8, but not every number in between is there.

Here’s the commented code to create and read the recordset. All it does is create a list of customer Ids for every customer that’s in London.

Sub GetRecordset()
 
    Dim adoConn As ADODB.Connection
    Dim adoRs As ADODB.Recordset
    Dim sConn As String
    Dim sSql As String
    Dim sOutput As String
    
    'Copied from the immediate window "Connection" and removed the
    'ODBC from the beginning
    sConn = "DSN=MS Access Database;" & _
        "DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;" & _
        "DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;" & _
        "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
    'Copied from the immediate window "CommandText" and cleaned it up a little.
    sSql = "SELECT CustomerID, CompanyName, Address, City, Region, PostalCode" & _
        " FROM Customers" & _
        " WHERE (City=’London’)"
    
    'Create a new connection and open it
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    
    'Create a new recordset and open it.  There are probably some
    'advantages to using different cursor types, but I generally use
    'the default (adOpenForwardOnly) unless I know I need to go backward
    'then I use adOpenDynamic
    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, _
        ActiveConnection:=adoConn
        
    If Not (adoRs.BOF Or adoRs.EOF) Then 'If there are no records, this will be false
        adoRs.MoveFirst 'This probably isn’t necessary, particularly with the default
                        'cursor type,but I always include it anyway
               
        Do While Not adoRs.EOF 'Start looping through the records
            sOutput = sOutput & adoRs.Fields(0).Value & "," 'Concatenate a string
                                                            'of the first field
            adoRs.MoveNext  'When you code has been running for 1/2 an hour, you
                            'probably forgot this line.  I know I forget it all the time.
        Loop
        
        'Close stuff
        adoRs.Close
        adoConn.Close
        
        'Get rid of the last comma
        sOutput = Left(sOutput, Len(sOutput) - 1)
    Else
        sOutput = "Empty Recordset"
    End If
    
    'Output
    Debug.Print sOutput
    
    'Clean up
    Set adoRs = Nothing
    Set adoConn = Nothing
    
End Sub

Sub GetRecordset()

Dim adoConn As ADODB.Connection

Dim adoRs As ADODB.Recordset

Dim sConn As String

Dim sSql As String

Dim sOutput As String

'Copied from the immediate window "Connection" and removed the

'ODBC from the beginning

sConn = "DSN=MS Access Database;" & _

"DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;" & _

"DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;" & _

"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

'Copied from the immediate window "CommandText" and cleaned it up a little.

sSql = "SELECT CustomerID, CompanyName, Address, City, Region, PostalCode" & _

" FROM Customers" & _

" WHERE (City=’London’)"

'Create a new connection and open it

Set adoConn = New ADODB.Connection

adoConn.Open sConn

'Create a new recordset and open it. There are probably some

'advantages to using different cursor types, but I generally use

'the default (adOpenForwardOnly) unless I know I need to go backward

'then I use adOpenDynamic

Set adoRs = New ADODB.Recordset

adoRs.Open Source:=sSql, _

ActiveConnection:=adoConn

If Not (adoRs.BOF Or adoRs.EOF) Then 'If there are no records, this will be false

adoRs.MoveFirst 'This probably isn’t necessary, particularly with the default

'cursor type,but I always include it anyway

Do While Not adoRs.EOF 'Start looping through the records

sOutput = sOutput & adoRs.Fields(0).Value & "," 'Concatenate a string

'of the first field

adoRs.MoveNext 'When you code has been running for 1/2 an hour, you

'probably forgot this line. I know I forget it all the time.

Loop

'Close stuff

adoRs.Close

adoConn.Close

'Get rid of the last comma

sOutput = Left(sOutput, Len(sOutput) - 1)

Else

sOutput = "Empty Recordset"

End If

'Output

Debug.Print sOutput

'Clean up

Set adoRs = Nothing

Set adoConn = Nothing

End Sub

The result, if you’re playing at home

AROUT,BSBEV,CONSH,EASTC,NORTS,SEVES

14 thoughts on “ADO Recordset Basics”

“ADO Recordset Basics:
I get asked how to hold an Excel query table in memory”

From that build up I was expecting something more like this:

Sub test7()
Dim rs As Object
Set rs = CreateObject(“ADODB.Recordset”)
With rs
.CursorLocation = 3
.Fields.Append “Querytables”, 13
.Open
.AddNew
.Fields(“Querytables”).Value = _
ThisWorkbook.Worksheets(1).QueryTables(1)
.Update

Dim qt As Excel.QueryTable
Set qt = .Fields(“Querytables”).Value
MsgBox qt.CommandText

End With
End Sub

Jamie.

Ooh, hierarchical recordsets.

rs.Fields(2).Fields(3).Fields(0).Value

My head is spinning.

When attempting to use the ADO in my VBA procedures, I would get an error about unknow object or some such. The solution was to include the ADO components in the project as follows:

On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.

I am having troubles with the connections

Recieving Error
>Runtime Error 13Mismatch Type

I have connected a SQL Server and also get value from a table of Database and now wants to access the value from database without using query,I meant to access value directly from the database table.

Pls tell me How it is possible bcs I ‘m so wory due to this problem If u help me then I will be great thankful to u for this consideration.

Thanks and Regards,

Amir Irshad Gondal
Email-irshad0207@hotmail.com
Email-irshad_207@yahoo.com
Email-irshad.gondal@gmail.com

I have one column in my Excel sheet, for which the recordset always returns value Nothing, although it clearly has (mixed texed and numeric) content.

Other columns (with text or numeric values) do not show this behaviour. Changing the format of the cell, manually typing the values again in Excel, … nothing helped. Only if the cell has text content, it is recognised. Except for when I would type into Excel ‘100 (including the quote), then it returns value 100.

Any idea why and how to solve this curious behaviour ?

Everything is ok byt I can’t find the ODBC driver for the database Postgres.

Just wondering, during the query, does it run in the background or will it run in the foreground and freeze everything until the results come out? I’m successful in doing this using MSSQL server as the backend, but when I use PostgreSQL and the mODBC driver or the psqlodbc driver, I’m faced w/ the query freezing that instance of excel until the time when the query results are returned to excel. This does not happen w/ MSSQL server.

Would really appreciate a response

Kristof Vydt wrote:
“I have one column in my Excel sheet, for which the recordset always returns value Nothing, although it clearly has (mixed texed and numeric) content…”

see: http://support.microsoft.com/kb/257819 add IMEX=1 to extended

even better is http://support.microsoft.com/kb/194124/EN-US/

I was trying to read some data from access to excel. One of the fields constains a string of 0s and 1s. When I used
Set AcsRst = New ADODB.Recordset
AcsRst.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
Cells(x,x).Value = AcsRst.Fields(“xx”).Value