Programmatically add a VBA function?

[Dennis]

If this is not the correct group for this question, please suggest a
better one.

When running a SELECT query against an Access database in a VB.Net app I
am getting...

"Undefined function 'Replace' in expression."

Some digging around suggests that the Replace function is not available.

Is there a way to programmatically add my own version of a Replace
function (or any VBA function) to a database before executing a query?

 

[Dennis]

And there's the problem! You're not running a SELECT query against an Access
database because, pedantically, there is no such thing as an Access
database. Microsoft Access is a database application development tool, and
the database file format it uses by default is the Jet database:
http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine. Because
Microsoft Access was the first app to ship with the Jet database, people
often refer to Jet databases as Access databases - even Microsoft themselves
to this sometimes!

I understand that. If you want me to refer to it as Jet in this forum I
will.

Indeed not, because the Replace function is part of VBA in Access - nothing
to do with the underlying Jet database.

When you're programming against a Jet database with something like a VB.NET
app, Access doesn't even come into the equation.



What exactly are you trying to replace? Are you not simply building up your
SQL string to send to the Jet database via OleDb...?

I am trying to execute this query...

SELECT tblLR.Location, Len(tblLR.Location) -
Len(Replace(tblLR.Location],',','')) AS NumCommas FROM tblLR WHERE
(Len(tblLR.Location) - Len(Replace(tblLR.Location,',',''))) <> 2

All it does is display stuff when the number of commas in a field <> 2.

Apparently Jet understands Len, but not Replace.

If Jet has no knowledge of VBA, then the answer to my question is "it
can't be done".

 

[Dennis]

Correct:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_21513333.html

I can't see the answer (I don't have an account there), but I get the
drift. I was hoping that there might be a way to attach a dll (with user
defined functions) or something to Jet.

It just seems odd that Jet would include other functions but not
REPLACE. I guess my recent experience with MySQL has gotten me spoiled.
;-)

Thanks,

 

[Dennis]

http://support.microsoft.com/default.aspx?scid=kb;en-us;294698

Yes, I have seen that article. But it doesn't mention REPLACE anywhere.

 

[Mary Chipman [MSFT]]

Hi Dennis, Let me try to phrase what Mark is trying to tell you in a
different way. The Jet engine has been used standalone by a variety of
different client applications over the years, from VB6 to .NET. It has
its own object model (DAO) and SQL-based query language. However, when
it's used with Access as the client application, i.e., when the code
is running from inside Access, you get the added benefit of the Access
expression service, which enables you to embed expressions and even
call VBA functions from inside queries. These are not part of Jet, and
you don't get this functionality outside of the Access client. So when
you create a .NET application against Jet, you are restricted to using
only syntax that the Jet engine understands. Attempts to call
expressions and functions that only Access understands will
necessarily fail. Unfortunately, Jet SQL has never been well
documented, so this remains a subject of considerable confusion and
misunderstanding.

--Mary