Replacing text in a field within a query

[MJG]

I have an existing mdb where text within a field is replaced using VB code.
(The replacement is vbCrLf with "<br>" & vbCrLf)

I want to achieve the same effect using ADO.NET and an ExecuteNonQuery which
essentially feeds a "dynamic SQL query" to the Jet database.

I could achieve what I want by programmatically changing each row.

It seems more elegant to construct an update SQL query that does it all.
Can anyone suggest how to do that?

Mike.

 

[JohnFol]

Forget ADO for the minute, as the end result is the same SQL, just the
calling method is different.

You need something like

Update MyTable Set MyField = "NewValue"

Use the Query grid to get the syntax right, and then call it from ADO.

 

[MJG]

Update MyTable Set MyField = "NewValue"

Hi John

Thanks.

Yes I've got 11 transforms that are required. As you point out the SQL is
the same. In fact I'm simply copying from the code inside Access to the
..NET system.

My issue is one Query (sproc is you like) that currently calls code in a VBA
module:

The current code is like:
UPDATE Table SET Table.Field2 = MultiLineToWebFormat([Field1]);
Where the MultiLineToWebFormat is defined in VBA.

What I want is something like
UPDATE Table SET Table.Field2 = Replace([Field1], vbCrLf, "<br />" &
vbCrLf);
(this makes NO calls to VBA code)
My issue is I don't know the sysntax that works within the Access SQL
processor to achieve this, or even whether suitable functionality actually
exists.

As I said there is another way to do this but I believe using sprocs/queries
is the better way to go (for several reasons).