VB Functions not found

K

Kevin

I'm using Access as a database without a GUI. We are querying the data and
obtaining result sets from a Windows C# app. I've written some VB functions
in a code module and tried to use them in SQL stored procedures, viz

NSERT INTO <table>
SELECT <stuff>,
ParseMilePost(Milepost) AS Milepost,
<more stuff>
FROM <another table>

where ParseMilePost is my VB function. When this query runs I get
"Undefined function 'ParseMilePost' in expression".

The function is declared Public and lives in a code module in access. How do
I solve this problem? TIA,

Kevin
 
B

Bob Barrows [MVP]

Kevin said:
I'm using Access as a database without a GUI. We are querying the
data and obtaining result sets from a Windows C# app. I've written
some VB functions in a code module and tried to use them in SQL
stored procedures, viz

NSERT INTO <table>
SELECT <stuff>,
ParseMilePost(Milepost) AS Milepost,
<more stuff>
FROM <another table>

where ParseMilePost is my VB function. When this query runs I get
"Undefined function 'ParseMilePost' in expression".

The function is declared Public and lives in a code module in access.
How do I solve this problem? TIA,

Give up on trying to use custom VBA functions from external applications.
This has never been allowed, despite the grumbles from all the developers
over the last 20(?) yrs. You can only use code you wrote in VBA modules in
Access itself.

Even some builtin VBA functions (Nz, etc. - to see an entire list, search
the MS Knowledge base for "Jet" and "sandbox") are not accessible to
external apps, since MS considers them to be security risks.

You will either need to move your parsing code into the query itself, or use
a cursor and do the parsing in C# (ugh).
This would be a good argument for moving your database to SQL Express ...
 
K

Kevin

Bob,
First, thanks for responding. Second, my VB function lives in a code module
within MS Access, not in a VB6 or other compiled VB code module. So I can't
use custom functions written in Access VB inside Access?

thanks,
Kevin
 
B

Bob Barrows [MVP]

Oh, I understood. The problem is, you don't seem to understand the
distinction between Access and the database engine (Jet).

You are not using "... custom functions written in Access VB inside Access."
You are attempting to call a saved query that uses a custom VBA function
from an external application: C#. Access is not involved here.

"Give up on trying to use custom VBA functions from external applications."

Access needs to be involved when running code in Access VBA modules. Just
because your call to the custom function is in a saved query does not mean
Access is involved when your C# code executes the saved query.

Access is the front end development system. It is not the database engine.
Jet is the database engine.
C# interfaces with Jet via ADO.Net (I presume) _without involving Access_ .
It is the Jet query engine that is attempting to execute the saved query,
not Access. Therefore, the code in the VBA modules is not accessible.

I repeat: we have been complaining about this for years.
 
K

Kevin

"I see", said the blind carpenter as he picked up his hammer and saw.

Thanks for clearing that up. I guess I need a more robust database engine.

Kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top