Access functions from Excel

[cheese8000]

Hi, I have a query in MS Access which utilizes a VBA function saved in the same database. I can run the query fine and it all works.

When I call this query from Excel, I get the error "Undefined function 'getVariable' in expression." where getVariable() is the function. If I remove the function call the query from Excel works fine. For some reason excel can't see the Access function even though it shouldn't need to (or the access query can't see custom functions when called from Excel). I tried copying the function into Excel VBA on the off-chance, but it was never called when the query tried to run.

I am using Office 2003 on Windows XP.

How can I make this query execute when calling it from Excel VBA procedure?


If it makes a difference, here's the excel vba calling code snippet:

Dim qd As DAO.QueryDef
Dim db As Database
Set db = DAO.OpenDatabase(myDatabase)
Set qd = db.QueryDefs("my query name")
qd.Execute
....

[cheese8000]

Sorry, this should be in the office integration forum but I don't know how to move it.

[cheese8000]

Solved myself for anyone else who happens to google this same problem.

I placed the query I wanted to run into an Access macro, then just ran the macro from Excel. Not very streamlined, but it works.



Sub test()
Dim objAccess As Object

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase (MDADatabase)

objAccess.DoCmd.RunMacro "mcrWeeklyUpdate"

objAccess.Quit
Set objAccess = Nothing

End Sub