Using the replace function with ADO and an Access db from Visual Basic 6

[Ermi]

Hi,
i have this problem....
From a visual basic application I must replace the value contained in a
column with another one....for example

Field1
------
Alfa
Beta
Gamma

If i want to replace "a" with "b" from the query pane of access i can write:
UPDATE Table1 SET Table1.Field1=Replace([Field1],"a","b")
and everithing works fine.

But using the same syntax via a command with ADO I get that error:
"Undefined function Replace"

maybe the syntax with ADO as some difference with the Access sql?

Can someone help me please?
thx

[Justin Hoffman]

"Ermi" <ba*******@libero.it> wrote in message
news:Bn*******************@twister1.libero.it...

Hi,
i have this problem....
From a visual basic application I must replace the value contained in a
column with another one....for example

Field1
------
Alfa
Beta
Gamma

If i want to replace "a" with "b" from the query pane of access i can
write:
UPDATE Table1 SET Table1.Field1=Replace([Field1],"a","b")
and everithing works fine.

But using the same syntax via a command with ADO I get that error:
"Undefined function Replace"

maybe the syntax with ADO as some difference with the Access sql?

Can someone help me please?
thx

The ability to use functions like Replace within an update query is a
'special trick' which MS Access can do, but you cannot use this from vb/ado.
Your options are:
If you are replacing something easy like the first letter in the word, then
you can use functions like left, right, mid, etc which will work from
vb/ado.
If the replace is more complicated, you will need to create an updateable
recordset, looping through and updating each record. Depending on how many
records you have, you may notice a drop in speed with this approach.
However, if it's only a few thousand records, I guess you'll hardly notice
the difference.

[Ermi]

Thanks Justin,
unfortunately I ve seen that this "trick" is unavaible from VB even using a
parametric update query.
I'll iterate trough a recordset and update each record as u have been
suggesting me...
Thanks again
Ermanno

"Justin Hoffman" <[e-mail]> wrote in message
news:d9**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"Ermi" <ba*******@libero.it> wrote in message
news:Bn*******************@twister1.libero.it...

Hi,
i have this problem....
From a visual basic application I must replace the value contained in a
column with another one....for example

Field1
------
Alfa
Beta
Gamma

If i want to replace "a" with "b" from the query pane of access i can
write:
UPDATE Table1 SET Table1.Field1=Replace([Field1],"a","b")
and everithing works fine.

But using the same syntax via a command with ADO I get that error:
"Undefined function Replace"

maybe the syntax with ADO as some difference with the Access sql?

Can someone help me please?
thx

The ability to use functions like Replace within an update query is a
'special trick' which MS Access can do, but you cannot use this from
vb/ado. Your options are:
If you are replacing something easy like the first letter in the word,
then you can use functions like left, right, mid, etc which will work from
vb/ado.
If the replace is more complicated, you will need to create an updateable
recordset, looping through and updating each record. Depending on how
many records you have, you may notice a drop in speed with this approach.
However, if it's only a few thousand records, I guess you'll hardly notice
the difference.