By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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.



Nov 13 '05 #2

P: n/a
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" <j@b.com> 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.


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.