Access query(with custom function) from Excel

  • Thread starter Thread starter LightLY
  • Start date Start date
L

LightLY

Dear Excel Gurus,

I am able to run an access query from Excel. However, if the access
query contains an Access custom function, I encountered problems
opening the RecordSet.

It seems that I am not able to retrieve data fields from Access which
are calculated from custom function using Excel. Is this a genuine
problem with Excel or did I miss out something?

I was working on this problem until 3am in the morning. Thank you very
much in advance if someone helps me.
 
You didn't specify what type of error you were getting. did you set these
references in the Tools - References menu in excel VBA

1) Microsoft Access 11.0 Object Library (or latest version on your PC)
2) Microsoft Active X data Object 2.8 Library (or latest version on your PC)
3) There are also two DAO libraries
 
You didn't specify what type of error you were getting.
Click to expand...

The error was "Type mismatch error". Under the watch window, inside
variable of type DAO.QueryDef, inside the property Parameters, there
was an undefined function.

The undefined function is a custom function used by the query in
Access. Thank you.
did you set these
references in the Tools - References menu in excel VBA

1) Microsoft Access 11.0 Object Library (or latest version on your PC)
2) Microsoft Active X data Object 2.8  Library (or latest version on your PC)
Click to expand...

Yes, I have already set above 2 references.
3) There are also two DAO libraries
Click to expand...

I also set Microsoft ActiveX Data Objects 2.8 Library.

Wouldn't the code fail compilation if I forget to set the references?
 
Back
Top