In the Access object pane I have qryDenialsForThisEob. I am trying to run it from Csharp using code something like this:

SQL = "SELECT * FROM qryDenialsForThisEob";

da = new OledDBDataAdapter(SQL, cn)
da.Fill(dataTable1);

This isn't working. I have other queries that work fine from csharp, for instance:

SQL = "SELECT * FROM qryAllDenials"

Anyway the error I'm getting is this:

Undefined function 'GetTheLetters' in expression.

This refers to a UDF used in the query, called Public Function GetTheLetters residing in standard module.

The odd thing is that this query runs fine when run from Acess VBA or the Access object pane. (Literally I can close the .Net app, open the Access object pane, and run the query without incident). It is only when running it from Csharp that I have this error. So the question is whether UDFs can be invoked from VB.Net and Csharp.Net.

How are you connecting to the Jet? I suspect this is a problem of the provider not understanding the UDF, which is to be expected, of course, as the provider doesn't necessarily have the knowledge of Jet Expression Service nor does it know about VBA modules within Access database file.

I tried a couple of different ways. The first was something like

cn = new OledBconnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Balance.MDb")


The second attempt was this code (this was a last resort where I tried using DAO from Csharp):


Object oMissing = System.Reflection.Missing.Value;
DAO.DBEngine engine = new DAO.DBEngine();
DAO.Database db = engine.Workspaces[0].OpenDatabase(@"C:\Balance.Mdb", oMissing, oMissing, oMissing);
DAO.Recordset rs = db.OpenRecordset("qryDenialsForThisEob", oMissing,oMissing,oMissing);

I get the same error message either way. I'm guessing a UDF can't be used from .Net?

In December 2001, one guy commented on another forum: "Unfortunately user-defined functions in queries are only supported if the queries are executed within Access. Neither DAO or ADO or SQL support them on their own."

That's an old post, but I fear it is still effective.

I assume I wouldn't have this problem using SQL server? That is, I wonder if a SQL Server UDF can be invoked from .Net? I would certainly hope so.

Well, sort of in a indirect way.

You certainly can do a .NET's equivalent to passthrough queries, and execute stored procedures as well.

With Jet, there is support, albeit largely undocumented, for stored procedures. I'd look into this and see if it can execute UDF for you.

Thanks Banana, I'll take a look at Jet support for stored procedures, although I'm not sure it will apply to my current situation, as I can only use Jet, not SQL server.

Banana, I'm looking at this article, hoping it's pointing me in the direction you suggested, not sure as yet:

http://www.devcity.net/PrintArticle.aspx?ArticleID=18

The article shows that I can create Access stored procedures that execute INSERT and DELETE statements, but I'm not having success with anything more sophisticated, i.e. I can't seem to even declare variables as I would do in a VBA UDF.

This would be a minor problem if my intended UDF merely manipulated data from a single table. Unfortunately I am doing something more complicated - I do JOINs on my UDFs such as the following, in this case the UDFs address a situation where two tables are supposed to have the same patient name but in fact, in one table, the name tends to be slightly misspelled. The UDF uses fairly extensive logic to deduce that the two version of the name actually refer to one and the same patient.

SELECT * FROM table1 as T1
INNER JOIN table2 as T2
ON SameFirstName(T1.FirstName, T2.FirstName)
AND SameLastName(T1.LastName, T2.LastName)

In other words if I can't use the UDF, I can't do the inner join, and therefore there isn't much point in even using a database - I'll probably have to do everything using pure code (very frustrating, firstly because I already have this project working fine in Access but need to migrate it to .Net), and secondly because I can't imagine how much code it could take to do my own joins. Joins are hard enough to replicate on their own, but when the basis of the join is a UDF, it's even harder.

Too bad. I was hoping that Jet's stored procedures would at least allow you to access the UDFs.

Well, if you're doing it in .NET and not in Access and you need some kind of store, why not use SQLite? It's free and has many of Jet's advantages.

And if you don't mind me asking, but why there is a perceived need to migrate from Access to .NET?

Thanks for the suggestion about SQlite. I've used it once in the past but never learned how to create a UDF in it. I think I heard something about SQLite UDFS in a programming language unfamiliar to me such as PHP. I am not eager to learn a whole new language at the moment.

I was migrating to .Net for the following reason. Some of the users don't have Access and I can't install the runtime as I don't have the support of the IT dept. Would create too much of a conflict. All the users have the .Net framework installed.

It's okay, though, I can get it done in .Net. Just wil take a little more time.

But yes, when I tried to invoke my UDFs from the Access stored procedure, I get the same error, "Undefined function in expression."

I find it odd that they wouldn't support runtime. What kind of conflicts would happen? I will understand if you can't say much, but my curiosity has been piqued because I'd expect runtime to be easy to install and get running compared to a in-house application which would almost certainly require IT's support and resources anyway and more so compared to runtime.

The comment from 2001 is still valid - largely because nothing's changed.
Jet 4 remained essentially as it was until ACCDB's release. Nothing too radical happened then other than the new data types.

The external APIs simply don't have any access to the UDF because Jet's expression service doesn't - not without Access there to resolve the call to VBA. (VBA requires a host application after all).
An instance of Access is vital in such cases.
Indeed - queries exected through DAO have amazing access to Access resolved function calls when there's an instance of Access running them.
The expression service takes on whole new powers ;-)
For example, from an Access application, you can open a DAO database to an external application (i.e. not using CurrentDb). Refering to Access functions (such as Nz for example) is still supported in that external database.

If you automate Access (say from VB or another Office app to open an instance of it through code) then you can again acquire access to extended functions beyond Jet's own shared ones by accessing the CurrentDb object of that opened instance.
It still carries the same overhead though as it's an instantiated Access instance running.

ADO's implementation is less amazing - but still only effective from within Access.


Jet's Stored Procedures are really just action queries.
The act of calling it a stored procedure really doesn't change anything.
Jet acquires no new abilities through execution of its SPs (why would it... it would be a huge addition to the language to be able to use variables and multi statement procedures. There's no way MS would have kept that quiet as a concept - none of us would).
You can create and use action queries just the same through the Access UI as you can Jet's Stored Procs through code.

>> "I assume I wouldn't have this problem using SQL server? That is, I wonder if a SQL Server UDF can be invoked from .Net?"

This is completely different as SQL Server UDFs are created in T-SQL.
So as long as they're valid functions they'll work and return exactly as expected from whatever the calling application.


>> "I find it odd that they wouldn't support runtime."

A well made Runtime install could be a pretty painless install.
There can be issues - but of course, this is software... so of course there can be issues. :-)
But the reticense of the IT dept may very well be more due to policits and the poor (largely ignorant) perception of Access in organisations. Even the runtime (which would protect them from the usual bugbear that is having to support or deal with end users problem efforts).

Aye well.
Must crack on.