Undefined Function 'Replace'

[SusanV]

Hi all and thanks in advance to any who can help me with this!

Access 2000 db on server share, Win XP and 98 clients all running Office
2000 Pro with all patches and SPs. Had several workstations throwing this
error after using Replace function in the On Open event of a form (never got
the error on the dev box):

Runtime Error 3085
Undefined Function 'Replace'

Verified all had correct references, refreshed references, repaired office,
installed patches etc and got all but ONE machine running the function
without error. The one that STILL won't run the function is Win98, and I've
tried all these things:

1. Verified references, added and removed a reference to refresh
2. Repaired Office
3. Office update - no missing patches
4. Verified all jet dlls are same version as those on other win98 PCs that
work, reregistered - no errors
5. Replaced all jet dlls from working PCs and reregistered them - no
registration errors
6. Uninstalled and reinstalled Office 200 Pro, reapplied service packs.
7. Rinse and repeat all above steps.

The only thing I can think of that may set this machine apart is that I
*think* it was upgraded from Office 97 to 2000, but then again, one of the
ones I was able to get working by reregistering dlls I know was an upgrade.
Everything else in the db works fine on this PC except this one function,
and I'm ripping my hair out.

Anyone have any ideas of something I may have missed?

SusanV

 

[Douglas J Steele]

Can you use Replace in the immediate window?

 

[SusanV]

Thanks for replying Doug,

I can run the function on any form from any computer in the company except
for that one. Module compiles on that machine with no trouble. Just won't
run on that one machine.

SusanV

 

[SusanV]

How would I run it in the immediate window? Still learning...

Thanks again,

SusanV

 

[Alex White MCDBA MCSE]

CTRL-G in any coding window

and type the command in to the window.

replace( as soon as you have typed the ( options should appear this is a
good sign.

 

[SusanV]

Ok, thanks Alex - I've only used the immediate window for debug.print
before.

Yes, on the problem PC, typing in ? Replace( DOES bring up the correct
options. Enter the options, close paren, enter, no errors

BUT - on the problem PC, the Replace function is still throwing the same
error on all 5 forms where it's used.

An example of the actual code where I'm using the replace is:
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],' usns ', ' USNS ')"

Again, this works fine on any other computer...

 

[Joerg Ackermann]

SusanV wrote:

this depends on which Service-Pack is used.

Replace() works in some SPs only as VBA-function,
not in SQL.

Update to latest Jet-Service-Pack.

Acki

 

[SusanV]

Thanks Acki,

That was the first thing I did - updated to jet SP 8 (Jet40SP8_9xNT.exe).
This fixed the problem on most of the other Win98 boxes, that and
reregistering dlls.

Fortunately the user with the problem PC does not often use the application,
and when he does it's usually a different area. Still frustrating for him
though - when he does need to use the forms in the narrative part of the
database he has to move to a different workstation.

Any other ideas? I've been struggling with this one PC for a couple of
weeks. =/

 

[Van T. Dinh]

Replace() was not recognised by Access Erpression Service (the interface
that interpret the Access / VBA functions for JET) in some (earlier) version
of Access 2000.

Write a "wrapper" function (UDF) around the Replace() function and then use
the wrapper function in your Query / SQL.

 

[SusanV]

All the workstations are running the same version of Access though - service
packs and all. And only this one machine doesn't get it. It shows the syntax
options when typing into the immediate window though - which really has me
confused...

 

[Van T. Dinh]

Check the version number of MSAccess.exe and see if all machines have the
same version.

 

[SusanV]

Good morning Van and thanks for continuing to beat this dead horse.

His file is version 9.0.6620, whereas mine is 9.0.0.6620. Minor difference
(extra 0.), renamed his to OLDmsaccess.exe, copied my msaccess.exe file to
his PC, same difference - immediate window works, form throws runtime error
3085 undefined function 'Replace'

Why would the function be recognized in VBA's immediate window but not when
called by a form? I know I can create a function to do the replace such as
MyReplace, but I hate to use a workaround when it ought to be fixable...

SusanV

 

[Van T. Dinh]

My understanding is that the problem mainly when you use Replace() (and some
other new functions introduced in Access 2000). Someone explained
previously that the Expression Service which interprets VBA functions for
JET was not updated to recognised these new functions (at least in the
original release of Access 2000). I am not sure whether this "Expression
Service" is included in MSAccess.exe or it is one of the DLL files used by
Access.

I don't have this problem anymore since my clients are on A2002 or A2003
nowadays since Microsoft mainstream suuport for A2000 finished Jun 2004.

 

[SusanV]

Huh, interesting. I'm going to repeat all my previous steps next time that
user is out of the office - completely remove and reinstall then service
pack, check dll versions etc. If that doesn't do it then I may end up
writing a custom function, but I'm hoping I won't have to, if only so that
when I include other recent functions I won't have to worry about running
into this again.

Thanks again for your and everyone else's help, it's much appreciated.

SusanV

 

[SusanV]

In case anyone is curious, I somewhat resolved this by replacing the
msjtes40.dll on his machine with a newer version found on all other
workstations and reregistering it - now the Replace function works so long
as it is NOT in an SQL Update statement - which I believe may have something
to do with nested parens...

Thanks to all who helped, it's workable now. You guys ROCK.

SusanV

 

[Van T. Dinh]

I guess you meant the file "msjet40.dll"?

Thanks for letting us know ...

 

[SusanV]

No, I had already replaced all the msjet*.dlls. The msjtes40.dll is the dll
which provides the Expression Service you referenced in your last post. The
link below lists all components of Microsoft Jet stack as well as Oracle,
SQL and other database technologies. Worth bookmarking for troubleshooting
purposes:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/mdac25.asp

 

[Nick]

Prior to a company wide upgrade to XP there was no problem with access
databases, so I am not sure if this would be a solution to your issue. But
I had a problem similar to yours after an upgrade to XP from 2000 with some
access databases. Undefined Function "Replace" errors started to pop up.
At the end of the day it turned out to be the version of VBA installed. 6.0
versus 6.3. The problem machines had 6.0 installed. Start access Help ->
About MS Access -> SYSTEM INFO -> APPLICATIONS -> Microsoft Access 2000 ->
SUMMARY. The VBA version of 6.00 produced the error, VBA version 6.03 no
problem.
Take Care,
Nick