Dealing with Chinese Letters Due To DB Corruption (1 Viewer)

Learn2010

Registered User.
Local time
Today, 19:06
Joined
Sep 15, 2010
Messages
415
All of our databases, front-end and back-end, are on a shared network. I have to put in workarounds for the various interruptions, which occasionally corrupt the database. When it gets corrupted I get Chinese characters in some of the fields.

I would like to be able to use an If Then statement that says If it is not one of the letters (A thru Z) Then.........

Is there a simple way to say A thru Z in that statement?

Thank you.
 

isladogs

MVP / VIP
Local time
Today, 23:06
Joined
Jan 14, 2017
Messages
18,186
First of all, you need to sort out the reasons for the corruption and fix them. This should be a top priority to deal with

You haven't specified what the 'interruptions' are but if you keep getting corruption there is a fundamental problem with your network that will eventually mean your data is unusable.

Below is a function you can use - add / remove characters to the list by editing the LValid_Values line as required.
You will see 3 variations indicating how I've modified it to suit my own needs.

However it's a 'sticking plaster' and not a proper solution to your corruption problems

Code:
Public Function IsAlphaNumeric(pValue) As Boolean

   Dim LPos As Integer
   Dim LChar As String
   Dim LValid_Values As String

   'Start at first character in pValue
   LPos = 1

   'Set up values that are considered to be alphanumeric
   'allow a-z,0-9, underscore and space only
  ' LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789"
  ' LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
   LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789"

   'Test each character in pValue
   While LPos <= Len(pValue)

      'Single character in pValue
      LChar = Mid(pValue, LPos, 1)

      'If character is not alphanumeric, return FALSE
      If InStr(LValid_Values, LChar) = 0 Then
         IsAlphaNumeric = False
         Exit Function
      End If

      'Increment counter
      LPos = LPos + 1

   Wend

   'Value is alphanumeric, return TRUE
   IsAlphaNumeric = True

End Function

I also have similar functions IsNumeric / IsCurrency etc
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:06
Joined
May 7, 2009
Messages
19,169
you create a Public Function and call it to test your string variable/ or fieldname:
Code:
Function RegExpr( _
  StringToCheck As Variant, _
  PatternToUse As Variant, _
  Optional CaseSensitive As Boolean = True) As Boolean

    Dim m As Variant
    Dim Match As Variant
    'Dim re As New regexp
    Dim re As Object
    Dim rslt As Variant
    
    Set re = CreateObject("VBScript.RegExp")
    re.Global = False
    re.IgnoreCase = Not CaseSensitive
    
     re.Pattern = PatternToUse
 
     For Each m In re.Execute(StringToCheck)
         rslt = m.Value
     Next

     rst = "" & rstl
     If Len(StringToCheck) <> Len(rslt) Then
         RegExpr = False
     Else
         RegExpr = True
    End If
    Set m = Nothing
    Set re = Nothing
End Function


you test it like this:


If regexpr([fieldName or TextboxName], "[\w\s]{1,500}") then
' readable characters in the string

Else
'not readable character in the string
End If

'
 

Learn2010

Registered User.
Local time
Today, 19:06
Joined
Sep 15, 2010
Messages
415
Thank you for the code. I will try this. Also, I have no control over the network and Access users are at the bottom of the totem pole. I back up the data consistently.
 

isladogs

MVP / VIP
Local time
Today, 23:06
Joined
Jan 14, 2017
Messages
18,186
Thank you for the code. I will try this. Also, I have no control over the network and Access users are at the bottom of the totem pole. I back up the data consistently.

Sounds very familiar ....
 

missinglinq

AWF VIP
Local time
Today, 19:06
Joined
Jun 20, 2003
Messages
6,423
Colin is dead right...and you need no control over the network to set your database up correctly!

Having multiple users sharing a single Front End, on a network drive, is, as you've found out, the surefire way to repeated episodes of corruption, as well as speed and timing problems, and all manner of strange, odd and curious behavior!

Being in forced retirement, I've spent 8-10 hours a day here and on other Access forums/newsgroups, and over the past twelve years have seen literally dozens and dozens of reports of split databases, using links to a single Front End, causing these kinds of problems, as well as massive data loss! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

You've simply got to place a copy of the front end on each users PC.

Linq ;0)>
 

Users who are viewing this thread

Top Bottom