473,848 Members | 1,544 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Replace function in Access

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
Jun 27 '08 #1
1 4637
<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

Jun 27 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
14956
by: Daniel | last post by:
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data in the format that I need it in order to create the reports that we use. So far this has proven to be successful for the reports that I am doing and the data that I am pulling into it. I just have one challenge that may require a lot of work and I...
8
20011
by: skinnybloke | last post by:
Hi - I am a newbie to Access & SQL and I would appreciate some help on this if at all possible. I have a query that contains the following code. SELECT DISTINCT Product. AS , Product. AS ; The problem I have is that the Product. contains characters that I do not want displayed in the query but are required
3
10515
by: asd987 | last post by:
Hi, Can anyone tell me if "replace" is supported by Access 97? I use the Dutch version and get the errormessage "sub or function not supported". Or is the Professional Edition needed? Thanks.
3
3787
by: Dave | last post by:
I have an Access 2K application that is distributed to about a dozen users (all with identical NT environments and identical Access versions, object libraries and service packs). I am using the VBA Replace function in a couple of queries to reformat some data. The application works flawlessly on 8 of the workstations, but on the other 4 throws a "Unrecognized function Replace" error when the queries in question are encountered. I have...
5
9246
by: galsaba | last post by:
I just found out that Replace Function exists in Access XP (as part of MS Office), but does not exist in Access 2000 (as part of access 2000). Any idea how I can resolve it? Does access 2000 has an alternative soulution, is there an add on tool for Replace Function? galsaba
2
12829
by: Ermi | last post by:
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
3
2552
by: mal_lori | last post by:
Hello, I have an ongoing problem with a single machine not recognizing the REPLACE function. This is a WinXP Pro machine, running Office 2000 (SP3) with MDAC 2.8 (SR1) and Jet 4 installed. The references are all identical to every other machine running this app.
4
23014
by: Lauren Wilson | last post by:
Hi folks, We have a need to replace sub strings in certain message text. We use the Office Assistant to display help and often use the imbedded formatting commands. Those of you who have used them know they look like this: "{cf 5}" or "{cf 0}" or "{ul 1}" or "{ul 0}", etc. The commonality they have is that they are always 6 charters long and always begin and end with curly brackets. This all works great if the user is running the...
1
2837
by: seanmatthewwalsh | last post by:
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...
0
9737
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11000
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10663
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10348
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9499
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7893
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7069
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5734
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4544
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.