Calling Access QueryDefs with Functions from Excel

[PatiTozer]

hello,

I am really stuck on this problem, and this is my first attempt at using forums, so I am hoping someone will take pity on me.

here is the situation:

I have, in Access, created a Query that calls a function. this function resides in the Global module of the Access database. From Access, this Query works just fine.

Now, i want to use the query in an SQL Select statement in Excel. eg: "SELECT * from Query1;"

When I try to open a recordset with this statement, i get an error '3085' that says that there is an undefined function in the statement. I tried copying the function into excel, but that didn't work.

Does anyone have a suggestion for me? I am really lost.

Thanks!

Pati Tozer

 

[Databaseguy]

You can get there. You'll have to make a reference to the Access Type Library in your VBA module in Excel. Feom the VBE window, Tools/References/Microsoft Access

You get to the QueryDef like this

Dim DBS as database
Dim Qdef as QueryDef
Set DBS = currentdb()
Set qdef = dbs.QueryDefs("YourQueryName&quot
With qdef
MyStr = .sql (That's the SQL statement&quot
--------More code down here.