replace function in SQL

[H. B.]

Hi,
I have a problem using the Replace function in an SQL
statment (in a VB project). I kept the code as simple as
possible:

strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
glbVBdbPath & ";Persist Security Info=False"

AdodcLet.ConnectionString = strCnn
AdodcLet.RecordSource = "SELECT Replace(
Letters.Condition,' ','-') as ee From Letters ;"
AdodcLet.Refresh

The error message says that 'Replace' is not defined in
the expression.
But running the select statment in Access 2000 works.

can somebody explain?

Thnaks alot

H

 

[Douglas J. Steele]

Unfortunately, when you connect to a Jet database from outside of Access
(eg. from VB), your only connection is through the Jet engine, which doesn't
know about most VBA functions.

The only way around this would be to use Automation to instantiate an
instance of Access in your VB program, and run the query through that
instance. (This would, of course, require that Access be installed on the
workstation along with your program)

 

[nieurig]

-----Original Message-----
Unfortunately, when you connect to a Jet database from outside of Access
(eg. from VB), your only connection is through the Jet engine, which doesn't
know about most VBA functions.

An other way to solve this problem was storing your query
as a view into the access-database (e.g. as "SavedQuery")
an getting the data by using

select * from SavedQuery

to your extenal programm.

Hope it helps.
Niels

 

[H.B.]

Thanks a lot guys.
Good suggestions.
At least I know it's not my fault!
I think I'll replace the data in the table before using
any program.