Access Query Which Calls VBA Function

[Matthew C. Harad]

Hello,

I have an Access Query which has a column which is a value
returned by a function that I have written in VBA in the
associated Access project. This query works fine when I
run it within Access.

However, my application is using Excel as a front end. In
VBA code associated w/ the Excel file I am executing the
query with a recordset ADODB object. I cannot get this to
work. I get an error message "Run-time error '-2147217900
(80040e14)': Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', 'UPDATE
'.

What is going on here? I am assuming that I cannot
execute from the Excel VBA project a query in Access which
in turn calls a function written in the associated Access
VBA project. Is this true?

Much appreciated.

Matthew

 

[Cindy Meister -WordMVP-]

Hi Matthew,

What is going on here? I am assuming that I cannot
execute from the Excel VBA project a query in Access which
in turn calls a function written in the associated Access
VBA project. Is this true?

That is correct. User-defined VBA functions need to have the
Access interface in order to execute. If we were talking
mailmerge in Word, I'd say you need a DDE connection to the
Access data.

In this case, I guess you'd have to query the data without
the function, and use the function in your Excel code on the
ADO dataset.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail