[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1385
  • Last Modified:

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 VS.net 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.

  • 4
  • 3
1 Solution
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:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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 :)

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now