VBA: Run-Time Error 3085 Using DAO

Last reviewed: February 16, 1998
Article ID: Q180810
The information in this article applies to:
  • Microsoft Visual Basic for Applications version 5.0
  • Microsoft Access versions 7.0, 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you use data access objects (DAO) in a Microsoft Office program to open a Microsoft Access query, you receive the following error message:

   Run-time error '3085':
   Undefined function <function name> in expression.

CAUSE

You are using DAO to open a query in Microsoft Access that contains a user- defined function.

MORE INFORMATION

You can only refer to Microsoft Jet objects that are only dependent on other Microsoft Jet objects when using DAO in other Microsoft Office programs.

The following steps demonstrate this problem by creating a query in Microsoft Access that calls a user-defined function and then attempting to open a recordset on that query using DAO in Microsoft Excel.

Steps to Reproduce Behavior

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

  1. Open the sample database Northwind.mdb in Microsoft Access.

  2. Create a module and type the following procedure:

          Function IsInternational(strCountry As String) As String
    
             If strCountry = "USA" Then
                IsInternational = "Local"
             Else
                IsInternational = "Intl"
             End If
          End Function
    
    

  3. Create a new query based on the Customers table as follows:

          Query: qryExcelTest
          ---------------------------------------------------------
          Type: Select Query
    

          Field: CompanyName
    
             Table: Customers
          Field: Country
             Table: Customers
          Field: Expr1: IsInternational([Country])
    
    

  4. Quit Microsoft Access.

  5. In Microsoft Excel, create a new workbook.

  6. On the Tools menu, point to Macros, and then click Visual Basic Editor. In the Visual Basic Editor, insert a new module sheet by clicking Module on the Insert menu. Type the following sample macro (Sub procedure).

          Sub xlTest()
             Dim db As Database, rs1 As Recordset
             Dim qry As QueryDef
             Set db = Workspaces(0).OpenDatabase _
             ("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
                ' Replace the above path with the correct path to the
                ' sample Northwind.mdb database on your computer.
             Set qry = db.QueryDefs("qryExcelTest")
             Set rs1 = qry.OpenRecordset
             rs1.MoveLast
             rs1.Close
             db.Close
          End Sub
    
    

  7. On the Tools menu, click References. Click to select the "Microsoft DAO 3.5 Object Library" check box, and then click OK.

  8. Run the macro titled "xlTest."

    Note that you receive the error message noted in the "Symptoms" section of this article.


Additional query words: prb
Keywords : kbcode kbprg
Version : WINDOWS:5.0,7.0,97
Platform : WINDOWS
Issue type : kbprb


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 16, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.