Replace function not working in Access

I dont want some of my fields to contain characters like [ * ;
Hence I am using 'replace' function in access database query to remove those characters.

I was successful in writing the same and it worked.

But since now I have changed my PC the same query is not working.

I have windows 2000 server as OS and VB6 and installed on my new machine.

I found on microsoft site that this is due to having vbajet32.dll with version 5.0 or more installed on my machine and I need to have vbajet32.dll with version (which I was unable to find anywhere) It has been mentioned that this version ships with MDAC 1.5 and I have installed MDAC 2.8

The mdac 1.5 is no longer available on any site.

I guess MDAC 1.5 may be coming with IE 4 or Win98 but not sure.

Please help. I want the query to run anyhow from my machine only.

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.

Can't help you with the dll, but you might rewrite your own version of Replace, using:

    Replace(pstrString, ";", "")

This response will be of no help to you but I'm just clarifying the situation.
From the wording of your Q I am assuming that you have a vb6 application which is using a Jet (Access, if you like) database.

If you are referring to this article:;en-us;189448
then it clearly states that the problem is resolved in VB6.
It also indicates that the problem is not specific to the Replace function but applies to all functions and that it is the use of functions which is the problem, not the use of special characters.


rajasachinAuthor Commented:
Dear harfang
One of my query is like this,

UPDATE Chemistlist SET Address2 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace([Address2],"-"," "),"("," "),")"," "),"."," "),"`"," "),"'"," "),"*"," "),"["," "),"]"," "),","," "),"#"," "),"'"," "),"$"," "),'"'," "),"®"," ");

and I have nearly 25 similar queries.
The way you told me to change the queries will take a lot of time.

Can u suggest some shortcut

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

You will need a custom VB function. There are several ways to do this, of course. The following is one I used to create file names from various string keys. (One client *did* in fact have a function key "CON" and "con.html" is a very bad name for writing to :)

''CleanName function''
' Converts a string from the standard ascii table (commented) to a simplified
' 7-bit table with all "dangerous" characters removed and in lower case.
Function CleanName(ByVal pstrName As String) As String
    ' "________________________________ !"#$%&'()*+,-./0123456789:;<=>?"
    ' "@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~"
    ' "€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿"
    ' "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ"
' just clean:
'Const cClean As String _
    = "____________________________________S_-_()___-__0123456789______" _
    & "_ABCDEFGHIJKLMNOPQRSTUVWXYZ(_)___abcdefghijklmnopqrstuvwxyz(_)-_" _
    & "E__f______S_O_________---_s_o__Y__cLxY-S_Ca_(-R-o-23-mP__10)____" _
    & "AAAAAAACEEEEIIIIDNOOOOOxOUUUUY_saaaaaaaceeeeiiii_nooooo-ouuuuy_y"
' also lcase:
Const cClean As String _
    = "____________________________________s_-_()___-__0123456789______" _
    & "_abcdefghijklmnopqrstuvwxyz(_)___abcdefghijklmnopqrstuvwxyz(_)-_" _
    & "e__f______s_o_________---_s_o__y__clxy-y_ca_(-r-o-23-mp__10)____" _
    & "aaaaaaaceeeeiiiidnoooooxouuuuy_saaaaaaaceeeeiiii_nooooo-ouuuuy_y"

    Dim strClean As String
    Dim intPos As Integer
    ' Convert all chars:
    For intPos = 1 To Len(pstrName)
        strClean = strClean _
            & Mid$(cClean, Asc(Mid$(pstrName, intPos, 1)) + 1, 1)
    Next intPos
    ' MS-DOS won't like those very much...
    Select Case UCase$(strClean)
        Case "CON", "NUL" _
            , "LPT1", "LPT2", "LPT3", "LPT4" _
            , "COM1", "COM2", "COM3", "COM4"
            strClean = strClean & "_"
    End Select
    CleanName = strClean

End Function
rajasachinAuthor Commented:
I really appreciate your efforts to help me out. I am really amazed to see the kind of help EE is offering.
Thanks a lott Dear "harfang",
I will just try this solution right now.

Before you loose any time: in VB, open "Tools / References". If one of them is marked as MISSING, this could be the cause of the problem. Of course I still suggest a custom replace function in your case :)
rajasachinAuthor Commented:
The Clean Name function is not working instead it corrupted my data.
Dont Mind I had taken backup before using that.
Its really "dangerous" yaar.
But cant the system be corrected to use the existing function.
Ok, i guess you could not configure it the way you need it. You do realize that it was written for a different case, right? Anyway, here is a function that does a multiple character replace (slower but perhaps easier to understand):

Function ReplaceChars(pvarString, pstrChars As String, pstrWith As String)

    Dim lngPos As Long
    Dim strC As String
    Dim strResult As String

    If IsNull(pvarString) Then Exit Function
    For lngPos = 1 To Len(pvarString)
        strC = Mid$(pvarString, lngPos, 1)
        If InStr(pstrChars, strC) Then strC = pstrWith
        strResult = strResult & strC
    Next lngPos
    ReplaceChars = strResult

End Function

Call it like this:
SELECT Address2, ReplaceChars(Address2, "-().`'*[],#'$""®"," ") FROM Chemistlist;

And later:
UPDATE Chemistlist SET Address2 = ReplaceChars(Address2, "-().`'*[],#'$""®"," ");

Just for the record, the CleanName does not corrupt the data :) Maybe you don't like the output, and maybe I should have added a few comments:

The first area is a full ASCII table (iso-8859-1 or Windows Western) with only the control characters masked. The second (the commented out definition of the constant cClean is a manual rewriting of the first, where every unwanted character has been replaced by its replacement. For example, "à" by "a", "Ý" by "Y", "®" by "R", etc. All unwanted punctuation has also been replaces by an underscore "_". In your case, you prefer a space, but the ideas is the same. The last (active) definition of cClean also tranforms the input to lowercase (why call and additional LCase() function if it can be done at the same time.

Anyway, if you want to customize the CleanName function, copy the strings from the full ASCII table to the constant strings and simply replace any unwanted character with what you want to appear (in your case, spaces)... The important point is that the length of each string must remain the same... In other words, use this constant instead:

Const cClean As String _
    = "________________________________ !"#$%&'()*+,-./0123456789:;<=>?" _
    & "@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~" _
    & "€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿" _
    & "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ"

And replace the characters you want changed by the function...

Sorry it didn't work out, I wish you better luck this time :)

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
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
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.