Using ADO in Functions

Normally, Data > Get External Data is a fine option for using data from an external source in you worksheets. There are situations, though, where the structure of an external data table isn’t what you want. You may want to use pieces of the data in various places, rather than in a tidy table.

In a situation I recently encountered, I needed the user to specify a key field of a recordset and have certain information returned. Normally, I would create a hidden worksheet and put an external data table on it with the whole recordset. Then, I would use VLOOKUPs to get at the data based on the user’s entry. The problem was that the recordset was more than 65,000 rows. I tried to think of a way to limit the data in the recordset, but I couldn’t predict what the users would enter, and therefore needed access to the whole thing.

I ended up using a user-defined function with ADO automation. Here’s generally what it looked like. This example uses the Products table of Northwind.mdb.

Dim adoCn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Function GetFields(sKey As String, lField As Long) As Variant
    Dim sCon As String, sSql As String
    ‘Create recordset if first call
   If adoCn Is Nothing Or adoRs Is Nothing Then
        sCon = “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;”
        sSql = “SELECT ProductID, ProductName, QuantityPerUnit, Products.UnitPrice “ & _
            “FROM Products”
        Set adoCn = New ADODB.Connection
        adoCn.Open sCon
        Set adoRs = New ADODB.Recordset
        adoRs.CursorType = adOpenDynamic
        adoRs.CursorLocation = adUseClient
        adoRs.Open sSql, adoCn
    End If
    adoRs.Find “ProductID=” & sKey
    If adoRs.EOF Or adoRs.BOF Then
        GetFields = “Not found”
        GetFields = adoRs.Fields(lField).Value
    End If
End Function

And it was used, more or less, like this:


Posted in Uncategorized

11 thoughts on “Using ADO in Functions

  1. Dick, Because your cursor is client-side, you could disconnect your recordset and close the connection e.g. put these lines after the adoRs.Open:

    adoRs.ActiveConnection = Nothing

    I prefer to use Filter rather than Find but that’s just a lifestyle choice (mine’s a Steeleye Stout, cheers).



  2. What if the user doesn’t have MS Access? Would it still work?

    For instance, when I crunch my UPS files (50,000+ lines per month) I can’t ever do it on a quarterly basis. I have Access, but one of the managers who would like to see the data summed by quarter doesn’t.

    Could I use this to get the quarter sums to them?


  3. Stacie

    Yes, it will work fine. MDAC / ADO contains everything you need to read/write to databases (except a user interface, that is). I have lots of users that use Access mdb files from Excel and Word, without knowing it and without Access installed on their systems.

  4. “MDAC / ADO contains everything you need to read/write to [Jet] databases”

    I that is not 100% correct:

    ‘the Jet database engine is included in versions earlier than MDAC 2.6. MDAC also includes several ODBC drivers and OLE DB providers for common data sources, including … Jet (earlier than MDAC 2.6).

    In MDAC versions that are later than MDAC 2.6, Jet is not included with the MDAC components set; therefore, the Jet engine core components, the Jet ODBC driver, and the Jet OLE DB provider are no longer included as a part of MDAC.’

    However, there is still hope:

    ‘The latest Jet components are available as a part of MDAC 2.5 SP2. If you want to use MDAC 2.6 or later (including the latest Jet components), first install MDAC 2.5 SP2, and then upgrade to the latest Jet components.’

    Extracts from:



  5. … although I’ve just noticed there may be a problem for Windows XP.

    The table says in the first article reads:

    Windows XP, MDAC 2.5 SP2: Cannot be installed

    I can’t get Microsoft’s DLL database to confirm but I still think it is a safe bet that if a user has Excel then they will have ADO and a Jet version that can be updated to the latest version of Jet 4.0 via a Windows Update.



  6. Hi Jamie
    I wasn’t aware of this, thanks. 2.5 is definitely a good version anyway, does the job and more. (2.7.0 is btw known to be a buggy monster)

    I think that Paradox and MySql are not in the drivers package, so “databases” is also below 100%. Oh well.

    Best wishes Harald

  7. I receive a compilation erro: Type not declared on the first line:
    “Dim adoCn As ADODB.Connection”.

    Could anybody explain if I need to set a reference to a library ( I already tried setting reference to Microsoft ADO Ext. 2.8 for DDL and Security) but that doesn’t help).

    Thanks for any help!


    P.S. I Use excel 2003

  8. Martin,

    You need to connect to the ‘Microsoft ActiveX Data Objects 2.X Library’ where X might be 2.5, 2.6, etc – whatever is the latest installed on your system.

    This is not the same library as Microsoft ADO Ext. 2.8 for DDL and Security.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.