Replace function in Access

[seanmatthewwalsh]

Hi

I have a website that uses an Access 2003 database. I have controls on
my pages that are bound to SqlDataSources that pull data from this
database. In a couple of them, I need to use the replace function to
replace some text in the results. Access' REPLACE function is only
available if executed within the environment, as it's a VBA function,
not a SQL function. So how can I implement this is the EASIEST
possible way? I don't to rewrite all my pages to first pull the data
into a DataSet and then do the replace, as I'll soon be back on SQL
Server, and can use the Replace function there.

Can I either do some sort of post-result transformation on the page
side, or how do I set up a macro / function in Access that the query
will be able to see? I created a MYREPLACE function in a Module, which
works fine if I run it in Access, but I still get the web error:
System.Data.Ole Db.OleDbExcepti on: Undefined function 'MYREPLACE' in
expression

thanks
Sean

[Mark Rae [MVP]]

<se************ **@hotmail.comw rote in message
news:43******** *************** ***********@m3g 2000hsc.googleg roups.com...

I have a website that uses an Access 2003 database.

Firstly, you don't. There is, strictly speaking, no such thing as an Access
2003 database. In fact, there's no such thing as an Access database of any
kind. Microsoft Access is a database application development tool, not a
database. Microsoft Access uses the Jet database natively, as do several
other Microsoft applications:
http://en.wikipedia.org/wiki/Microso...atabase_Engine

You may think this is extremely pedantic because I, and everyone else in
here, know perfectly well what you mean when you talk about an Access
database, but it's fundamental to the understanding of the problem you're
currently facing.

In a couple of them, I need to use the replace function to
replace some text in the results. Access' REPLACE function is only
available if executed within the environment, as it's a VBA function,
not a SQL function.

You are completely correct. Microsoft Access or, more correctly, the VBA
included with Microsoft Access, does indeed have a Replace() function. The
problem is, as you've discovered, is that you're not using Microsoft
Access - you're using a Jet database via ADO.NET - so none of the useful
time-saving stuff for which Access is so useful is available to you...

So how can I implement this is the EASIEST possible way?
I don't [want] to rewrite all my pages to first pull the data
into a DataSet and then do the replace, as I'll soon be back on SQL
Server, and can use the Replace function there.

OK.

Can I either do some sort of post-result transformation on the page
side, or how do I set up a macro / function in Access that the query
will be able to see? I created a MYREPLACE function in a Module, which
works fine if I run it in Access, but I still get the web error:
System.Data.Ole Db.OleDbExcepti on: Undefined function 'MYREPLACE' in
expression

That would be correct. Forget Microsoft Access completely, as you're not
using Microsoft Access.

Luckily, there is (probably) an extremely simple way round this.

You say that you are using databound controls. I'm assuming that you mean
webcontrols such as <asp:GridView /etc.

If so, then you can do what you want extremely simply by using the
RowDataBound event:
http://msdn.microsoft.com/en-us/libr...databound.aspx

This will allow you to write code which will inspect every row of data just
before it gets bound to the control, and will allow you to do any
manipulation of that data which you require.

Other databound webcontrols have similar functionality.. .
--
Mark Rae
ASP.NET MVP
http://www.markrae.net