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.

The result, if you’re playing at home

AROUT,BSBEV,CONSH,EASTC,NORTS,SEVES

14 thoughts on “ADO Recordset Basics

  1. “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.

    –

  2. 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.

  3. 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

  4. 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 ?

  5. 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

  6. 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

Leave a Reply

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