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.
- Open the sample database Northwind.mdb in Microsoft Access.
- 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
- 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])
- Quit Microsoft Access.
- In Microsoft Excel, create a new workbook.
- 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
- On the Tools menu, click References. Click to select the "Microsoft DAO
3.5 Object Library" check box, and then click OK.
- Run the macro titled "xlTest."
Note that you receive the error message noted in the "Symptoms" section
of this article.