Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008

    Talking Unanswered: Mining for gibberish symbols in an Access DB

    A little about the DB:

    1. Small to medium Sized, about 5,000 entries

    2. Built with Access '98 and hosted on a conventional server, later updated to Access '03 using a SQL server.

    A lot about the problem:

    Somewhere in the transition from older to newer DB, there were a lot of gibberish symbols that began popping up. I believe all of them are transformed from normally readable symbols like " and ? and spaces that become small boxes or lines or other gibberish. My job has asked me to systematically remove these from our Access DB. Apparently the symbols are visible on our website which is more or less all re-represented data from the DB.

    Now I'm totally game for going entry by entry (being paid by the hour) but I feel like I'd be doing them a disservice if I didn't check on an alternate method of mining and removing these symbols with something a little more strategic. Are there algorithms or other features built within '03 or '07 Access that can help with this?

    I have tried the extent of my technical skill, the 'find' tool and it seems that a gibberish symbol may transform into a " or ? when copied and pasted into the find tool field but it can't then find the gibberish back in the field. I do have Element K and have researching within it without any luck. I would appreciate any suggestions on this topic. Thanks all.


  2. #2
    Join Date
    Feb 2004
    One Flump in One Place

    Do you know the specific ASCI values of these symbols? Are you useing CHAR\ VARCHAR data types in SQL Server or Unicode (NCHAR\ NVARCHAR)?

    Anyhoo - if you know the ASCI values just run this (SQL Server syntax) on a COPY of the data:
    DECLARE @ascii_val_to_go AS INT
    @new_ascii_val AS INT
    SELECT @ascii_val_to_go = 199 --For example
    , @new_ascii_val = 32 --For example
    UPDATE mytable
    SET mycol = REPLACE(mycol, CHAR(@ascii_val_to_go), CHAR(@new_ascii_val))

  3. #3
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    I've got $10 that says this will have something to do with magic quotes. Where did you're original text come from?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    May 2008
    The entries are broken up into 15 varieties of forms with some overlapping data. There are 8 tables that the forms draw from. Probably a good 1/4 of the data is basic contact info and the rest is narrative. The narrative is from researchers so they use lots of fancy punctuation. As far as I know, all data was either typed in or copied and pasted from excel. Anything input after the switch over to '03/SQL has had no problems.

    I have no clue how to do what Pootle wrote, but I'll get on Element K and see if I can figure it out.

  5. #5
    Join Date
    Feb 2004
    Chicago, IL
    I wrote a little bit of code that might help. I am not sure how many tables you are searching but to use this code you will need to add a field call Questionable to each table you want to search (make it a Yes/No field).

    Then make a new Module and copy the code below into it. Then in the Immediate Window type this:

    Call CheckTable("tblTest")

    Make sure to replace tblTest with your table name. The Questionable field will be checked for any record that has non keyboard characters in it. Hopefully that will narrow down your searching.

    Public Sub CheckTable(strTableName As String)
        Dim rstTableData As New ADODB.Recordset
        Dim fldTableField As Field
        Dim booDataOK As Boolean
        rstTableData.Open strTableName, CurrentProject().Connection, adOpenDynamic, adLockOptimistic
        With rstTableData
            While Not .EOF
                For Each fldTableField In rstTableData.Fields
                    booDataOK = CheckFieldData(rstTableData.Fields(fldTableField.Name))
                    If Not booDataOK Then
                        !Questionable = True
                        Exit For
                    End If
                Next fldTableField
        End With
    End Sub
    Public Function CheckFieldData(varData As Variant) As Boolean
        Dim strData As String
        Dim strChar As String
        Dim lngCount As Long
        Dim lngASCII As Long
        CheckFieldData = True
        If IsNull(varData) Then Exit Function
        strData = CStr(varData)
        For lngCount = 1 To Len(strData)
            strChar = Mid(strData, lngCount, 1)
            lngASCII = Asc(strChar)
            Select Case lngASCII
                Case 32 To 126
                Case Else
                    CheckFieldData = False
            End Select
        Next lngCount
    End Function

  6. #6
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I'd do a comparison between the old data and the new data, and find what characters went wrong. I'd then either change those int he old data, or make the change in the new data
    depending oin the numbers and your familarity you could make the change in the live data using T-SQL (ie in/on the SQL Server data), or you could do it as a JET update query in Access if you are more familiar in Access, but updating the server data. I certainly wouldn't advocate this in a production environment but its acceptable in my books as part of data take on.

    Its so long since I've done T-SQL Id be mnded to do it in JET/Access usng ASC.. if you knwo your data well enough could identify rogue data (ie rows which have a character which is outside the normal range you'd expect (ie outside A-Z,a-z,0-9 & punctuation). dependign on how many of the beggars are I may then go in and direct edit out the offending rows, rather than rely on an update query. but once you know what the character groups are you coudl easily enough replace the rogue characters with the correct values.

    Of course its possible that your SQL Server table design may not support the characters Access is trying to insert. I could well believe you may have problems had the original db been developed not usin SQL compatablity mode.. so its possible that you may have problems because of " in the data. but itcould jsut be soemthign as dumb as you've got the wrong code page on the table or db

    I certainly wouldn't want to iterate through a recordset
    Last edited by healdem; 05-27-08 at 12:05.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts