String Replace using SQL/TADOQuery

I am working on an application developed in Delphi 2006 (for Win32 platform) that uses MS Access database as a backend. I need to write a query along the lines of:

UPDATE tblPlans SET tblPlans.AccessList = REPLACE(AccessList,"OldValue","NewValue")
WHERE (tblPlans.AccessList LIKE "%OldValue%")

The query works perfectly in MS Access (ie after substituting '*' for the '%' etc), but when run from the Delphi app, results in a message like "Undefined function 'REPLACE' in expression." So it looks like TADOQuery doesn't support the REPLACE function.

Can anyone suggest an alternate approach for replacing text that will work with an UPDATE query (via TADOQuery)?

PS Stored procs aren't an option in the context of this particular application and the nature of it's deployment. (Otherwise I'd happily use them!)

PPS I can get around this problem to a degree with a long formula that uses LEFT, INSTR and RIGHT (ie to grab the strings to the left and right of the "OldValue" and join these with the "NewValue"), but I'd prefer something a little more efficient.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike LittlewoodEngineerCommented:
Ive just tried this using ADO, and it works fine for me.
Mike LittlewoodEngineerCommented:
I better put into context, I am using Delphi 7 and MSSQL.
I recommend you avoid TADOQuery for this.  Instantiate an MSAccess object.  You can then run a query through the .DoCmd method.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Mike LittlewoodEngineerCommented:
Sounds like its not an ADO problem to me, but the interaction with ADO and MSAccess.
Im using MSSQL so I cannot guarentee that what I have found is correct for MSAccess.

On a side note ... MSAccess ... yuck  :o)
DavidLeedingAuthor Commented:
Hi mikelittlewood
I'm afraid I'm stuck with MS Access for this project. Sigh. I agree: MSAccess = YUCK. Migrating to MSSQL right now is not possible/practical (and also brings with it a whole heap of corporate politics I can do with out for the moment). We intend to do so...eventually.

I suspected the problem has to do with the ADO-MSAcess interaction...I've come up against a few strange things in the past. Do you know of any reference/documentation on the net that highlights the ways in which ADO + MSAcess can go pear-shaped?

Hi aikimark
Thanks for your suggestion. However, for this app (and for a number of reasons), I'm required to stick with TADOQuery. Also, this particular query sits in the middle of a sequence of UPDATE queries (all using TADOQuery). I'd rather not treat this problematic query in a different way to the others.

Looks like I'll have to stick with my *ugly* query based on LEFT, INSTR and RIGHT. Sigh.
Mike LittlewoodEngineerCommented:
Hi again David. You are correct about the ADO - MSAccess issue.
I did a quick test this morning with a new access database and ADO with the replace function, and I get the same error as you.
I think you are going to have to use your *ugly* query.
Is there a way to prevent any syntax checking by the TADOQuery layer?  Maybe force a pass-thru condition.

Maybe you could add a public function to the database that does the Replace.

Are there any parameters you've set on your TADOQuery that might be more appropriate to a Select statement than an Update statement.  Your query doesn't return records.

You might try running this query in an .Execute method off the database connection object.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidLeedingAuthor Commented:
Thanks for your suggestions, mikelittlewood and aikimark.

I've checked the settings of the query; everything's as it should be. I tried ".Execute" on the database connection object and I get the same error message.  I'm requried to avoid using the backend for anything but data, so can't used stored procs or functions in the database. So I think I'm stuck with the inefficient "ugly" UPDATE query until we're ready to migrate to a "real" database. Oh well.

I'll split the points because you've both put time and thought into this...and you've confirmed for me that there's no "easy" way around this anomaly!

If you create a separate MSAccess database with attached tables, you can store queries for use with your program.
DavidLeedingAuthor Commented:
Hi aikimark
Yes...I've done that with other applications. Unfortunately, it's not approriate to do that for this particular project for several long-winded and not entirely rational reasons (which I'll spare you!). But thanks for the suggestion.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.