Using COM Add-In functions in SQL statements with Access 2K

[andylec]

I've written a COM Add-In for Access 2000. While most of it seems to work OK, I'm having trouble executing any SQL statements (using a Command object) that contain custom functions, e.g.

strSQL = "UPDATE MyTable " & _
"SET MyField = StrConv(MyField, vbUpperCase)"

will work, as StrConv is a built-in function, but:

strSQL = "UPDATE MyTable " & _
"SET MyField = MyFunc(MyField)"

doesn't, and neither does:

strSQL = "UPDATE MyTable " & _
"SET MyField = Replace(MyField, "A", "a&quot"


If I set a reference to the COM Add-In from within Access, I can use the function in the immediate window, but not in any queries. If, however, I copy/paste the same code into a code module within the database, then I can use the SQL statements like the above from within my Add-In. I can also create a seperate 'Code Library' database and programatically set a reference to it when my Add-In connects. This enables me to use the functions as I intend, but relies on an extra file, and I am trying to keep the Add-In self-contained.

Currently, I am having to work around the issue by opening a recordset and updating each record individually, which takes far longer to run (minutes rather than seconds)

So, what I'm wondering is, is there a way to declare these functions within my Add-In, so that I can use them as intended.

rgds
Andy

 

[SemperFiDownUnda]

Ok first glance I'll fire off some info

ACCESS is NOT a database engine it is a front end to a database engine.
SQL gets executed by a database engine
ACCESS uses JET or MSDE (limited SQLServer 7 engine) for its database engine
ACCESS has (sorry for the pun) access to the functions you've created
JET or MSDE does NOT have access to these function
thus the SQL that is run by the database engine can not use these functions.

Sorry for the bad news. Others probably have work arounds but thats the basics

 

[andylec]

Bad news? Not at all. I'm no worse off for your comments - I can still do what I need to with the work arounds I'm currently using. Just much more slowly

But why can I use the very same functions in this manner if I copy/paste the code into a code module of a seperate .mdb and then set a reference to this .mdb in the original .mdb? Or create a module in the original .mdb.

What I am trying to achieve is to make the functions within the COM Add-In as 'visible' to Access as those techniques so that I can use them in the same way. I just don't have a huge amount of experience with COM Add-Ins, and wondered if I was missing something obvious.

rgds
Andy

 

[SemperFiDownUnda]

The key is that just because the code is in a MDB doesn't mean that the database engine can execute that code. It treats it just like every other piece of data it has....just 1's and 0's. The closest I know that can do kind of what you want is SQLServer (MSDE) can have what they call External Stored Procedures writen in languages like C++. What this is is the engine has been set up to let the user attach a normal DLL (not COM) and use them just like normal Stored Procedures (which often means you can not use them as Scalar functions). I'll cross my fingers for you that someone else knows a trick but I'm sceptical.

Another way of thinking about it is like this
Access is programmed using VBA.
JET and MSDE can uses SQL language.
VBA and Access has been designed to allow the use of COM object.
JET and MSDE have not so you can't use them.
In Access when you right a bit of SQL what happens is that Access passes that string to the DB Engine and interprets it and runs it (kind of like old basic or more closely to java in that it Parses, Resolves, Optimises, Compiles and Executes the code) then returns a result. Problem as I said above is that the compiler has not been set up to use COM objects.

 

[andylec]

I do understand all you say regarding the difference between Access and the database engine it is using, but say I have:

' Test function for illustration
Public Function MyFunc(varValue as Variant) as Variant

If Not IsNull(varValue) Then
varValue = Replace(varValue,"\","/&quot
End If

MyFunc = varValue

End Function

Then this will work as the CommandText of an ADO Command if MyFunc is in a code module within the .mdb (or a referenced one):

strSQL = "UPDATE MyTable " & _
"SET MyField = MyFunc(MyField)"

If you use the QBE window to build a query, for example, you can see which functions can be used inside the SQL statement by using the Expression Builder dialog. In there is a list of all built-in functions, and also a list of functions that are part of the current project. While functions that are in a referenced .mde, for example, are not shown here, they can also be used within the SQL statement.

So I'm still confused as to how to achieve the same thing with MyFunc declared inside the Add-In

rgds
Andy

 

[SemperFiDownUnda]

MyFunc might be stored in a MDB but (correct me if I'm wrong anyone).....

Never mind I proved my assumtion wrong
I see you can use functions in there...now how could you get functions in a com object to work.

Hmmm try using CallByName function in your SQL. It takes 3 or more parameters but basically lets you call a methods using strings for the object name and method name.

 

[andylec]

Nice idea

Unfortunately, I can't use CallByName in an SQL statement, at least not directly. If I wrap the call inside a function that resides in the .mdb (or referenced .mde), just like the MyFunc example above does with the Replace function, then it can be used, but that brings me back to the original problem.

It seems that only certain kinds of referenced files/objects can contain functions that can be used directly. I can't see any consistency in which functions can be used directly, and which can't. For example, StrConv is OK, but Replace isn't, and yet they are both members of VBA.Strings. Strange!

It looks like I'll have to put up with the massive speed penalty of the recordset approach if I want to keep all the code in 1 file.