Recordset not updating data in table (1 Viewer)

roscoe10s

New member
Local time
Today, 11:36
Joined
Jun 14, 2021
Messages
6
Having to step back into VBA programming on a couple database after several years away. Rusty!!!

Dataset: data files from global business partners, some of whom send data using foreign characters.
Situation: I've done my research and programming and have built a procedure to scan the necessary fields character by character, do a lookup against an ASCII table to replace English equivalents, and then update the field with the new data.

I've referenced some old code on using .Edit and .Update in other dbs and that was working just fine. This is not translating so well.

I have stepped through a test table of 10 lines and the debug.print details the correct(ed) English character data. However, upon execution and completion of the code, and opening the table, the foreign characters still exist.

Like I said - rusty. This is undoubtedly something simple but I'm just not seeing it. Any help is appreciated!

Ross

Code:
Public Sub TransForeign()

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strOld As String
Dim strNew As String
Dim strCNChar As String
Dim strNewChar As String
Dim N As Integer
Dim X As Integer
Dim rCount As Integer
Dim NewChar As Variant

Set db = CurrentDb
strSQL = "SELECT [Bill_To_Name] FROM tblPOS_Import_Prelim Order by [Bill_To_Name];"
Set rs = db.OpenRecordset(strSQL)

rs.MoveLast
rCount = rs.RecordCount

rs.MoveFirst

For X = 1 To rCount

    strOld = rs("Bill_To_Name")
    N = Len(strOld)
    For N = 1 To N
        strCNChar = Mid(strOld, N, 1)
        
        NewChar = DLookup("[Replace_ASCII]", "t_ASCII", "[ASCII_Code] = " & Asc(strCNChar))
        If IsNull(NewChar) Then
            strNew = strNew & strCNChar
        Else
            strNew = strNew & Chr(NewChar)
        End If
    Next N

    rs.Edit
    rs![Bill_to_Name] = strNew
    rs.Update
    
    strNew = ""
    strOld = ""
    
    rs.MoveNext
Next X

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Exit Sub

Exit_TransForeign:
    Exit Sub
Err_TransForeign:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_TransForeign

End Sub
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:36
Joined
Jan 23, 2006
Messages
15,364
Ross,

Perhaps you could post a copy of your test table to illustrate the issue in context.
 

roscoe10s

New member
Local time
Today, 11:36
Joined
Jun 14, 2021
Messages
6
Ross,

Perhaps you could post a copy of your test table to illustrate the issue in context.
Here's an Excel dump of the customer info. Once I get past an update of the customer name, updating the other fields as necessary should be simple...right??
 

Attachments

  • sample_data.zip
    6.6 KB · Views: 627

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:36
Joined
May 21, 2018
Messages
8,463
Sheet1 Sheet1

Bill_To_NameBill_To_Street_AddressBill_To_City
S&T Consulting Hungary Kft.Puskás Tivadar út 14Budaörs
COMTRADE SYSTEM INTEGRATIONSavski nasip 7Beograd
DATA4M sp. z o.o.Podleśna 3Białystok
E-Direct Cloud Solutions Sp z o.o. ul. Odrowąża 15Warszawa
GIGASERWER SPEŁKA Z OGRANICZONĄ ODPOWIEDZIALNOŚCIĄ Bielowicza 4Świątniki Górne
ITNS Polska Sp. z o.o. ul. Pogodna 7Czerwonak
S&T Consulting Hungary Kft.Puskás Tivadar út 14Budaörs
Nowire s.r.o.Višňová 331/4Praha
S&T Consulting Hungary Kft.Puskás Tivadar út 14Budaörs
S&T Consulting Hungary Kft.Puskás Tivadar út 14Budaörs

Code:
UPDATE tbldata
SET    tbldata.bill_to_name = Replaceinternationalcharacters([bill_to_name]),
       tbldata.bill_to_street_address =
       Replaceinternationalcharacters([bill_to_street_address]),
       tbldata.bill_to_city = Replaceinternationalcharacters([bill_to_city]);
tblData tblData

IDBill_To_NameBill_To_Street_AddressBill_To_City
1​
S&T Consulting Hungary Kft.Puskas Tivadar Ut 14BudaOrs
2​
COMTRADE SYSTEM INTEGRATIONSavski nasip 7Beograd
3​
DATA4M sp. z o.o.PodleSna 3BiaLystok
4​
E-Direct Cloud Solutions Sp z o.o. ul. Odrowaza 15Warszawa
5​
GIGASERWER SPELKA Z OGRANICZONa ODPOWIEDZIALNOSCIa Bielowicza 4Swiatniki GOrne
6​
ITNS Polska Sp. z o.o. ul. Pogodna 7Czerwonak
7​
S&T Consulting Hungary Kft.Puskas Tivadar Ut 14BudaOrs
8​
Nowire s.r.o.Visnova 331/4Praha
9​
S&T Consulting Hungary Kft.Puskas Tivadar Ut 14BudaOrs
10​
S&T Consulting Hungary Kft.Puskas Tivadar Ut 14BudaOrs
not sure the issue of the capital "O" and small "a". For some reason they switch.
Code:
Public Function ReplaceInternationalCharacters(ByVal strText As String) As String
   
    Dim i As Integer
    'Big A
    For i = 192 To 197
            ReplaceInternationalCharacters = Replace(strText, Chr(i), "A")
    Next i
    'little a
     For i = 224 To 229
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "a")
    Next i
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, ChrW(261), "a")
    'Big E
     For i = 200 To 203
            ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "E")
    Next i
    'little e
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(130), "e")
     For i = 232 To 235
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), Chr(101))
    Next i
    'Big I
    For i = 204 To 207
            ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "I")
    Next i
    'little i
     For i = 236 To 239
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "i")
    Next i
    'Replace Big O
    For i = 210 To 214
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "O")
    Next i
    'Replace little o
    For i = 242 To 248
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "o")
    Next i

    'Replace Big U
    For i = 217 To 220
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "U")
    Next i
    'Replace little u
    For i = 249 To 252
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "u")
    Next i

    'Replace Big Y
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(152), "y")
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(236), "y")
    'Replace Big N
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(209), "N")
    'Replace little n
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(241), "n")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, ChrW(328), "n")
    'Replace Big S
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, ChrW(346), "S")
    'Replace little s
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, ChrW(353), "s")
    'Replace Z
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, ChrW(380), "z")
     'Replace L
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, ChrW(321), "L")
    End Function
 

roscoe10s

New member
Local time
Today, 11:36
Joined
Jun 14, 2021
Messages
6
Excellent - I thought my start at a reference table with Latin equivalents was going to be my key but I like this method much better.

Q1 - when I copy the "UPDATE" code into my module, I'm getting an "expected: end of statement" error at the end of the first line (where the comma is located). Formatting issue? I tried to add a line feed carry-over ("& _") but that did not resolve. -- correction! Just figured it out - I have to do "SET" on each statement and eliminate the comments, so I actually have 3 lines.

Q2 - In doing a debug, the error is "tbldata = variable not defined". Not sure if I am supposed to insert the code into some piece of my original code, or if this is a stand-alone routine.

Q3 - If I place this update code as a public sub, I assume I can simply call this from an command button code on a form. True?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:36
Joined
May 21, 2018
Messages
8,463
The below is not code for a module. That is SQL from a query.
Code:
UPDATE tbldata
SET    tbldata.bill_to_name = Replaceinternationalcharacters([bill_to_name]),
       tbldata.bill_to_street_address =
       Replaceinternationalcharacters([bill_to_street_address]),
       tbldata.bill_to_city = Replaceinternationalcharacters([bill_to_city]);

The easiest to build a query/ies like above. Then in your code you can just call that query.
currentDB.execute "qryYourUpdateQueryName"

Having a table would not be a bad idea, because I thought I had most of the characters but did not. You had characters that I did not already have so I had to add some more lines. Example you had a strange L with a line in it.
ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, ChrW(321), "L")

With a table you could add to it if necessary. You could also have a language field. So if you knew what language you could loop only the appropriate language. This could speed things up because my code tries any possible character I could think of.
 

roscoe10s

New member
Local time
Today, 11:36
Joined
Jun 14, 2021
Messages
6
Ah, got it! Totally misunderstood what that was. The L with a line through it (a "strike" as it's apparently called) is one of several Polish characters I have to deal with which cause headaches as they do not have ASCII codes, as you are aware. I am intrigued by your note of looping a language - I do in fact know what languages cause issues. I'll experiment.

I too liked the table idea and was going to a standard loop routine to examine text - everything was fine until it came to actually maintaining the .Update on the field data. Still don't know why that wouldn't work as I have used it in a different routine doing essentially the same thing (eliminating all spaces in a particular field).
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:36
Joined
May 21, 2018
Messages
8,463
So I looped the characters and built a large table following table. I cannot post the whole thing because it is too large
tblCharacters tblCharacters

CharacterIDCharacterASCI_WReplace
175​
Š
138​
S
177​
Œ
140​
OE
179​
Ž
142​
Z
191​
š
154​
s
193​
œ
156​
oe
195​
ž
158​
z
196​
Ÿ
159​
Y
198​
¡
161​
i
229​
À
192​
A
230​
Á
193​
A
231​
Â
194​
A
232​
Ã
195​
A
233​
Ä
196​
A
234​
Å
197​
A
235​
Æ
198​
A
236​
Ç
199​
C
237​
È
200​
E
238​
É
201​
E
239​
Ê
202​
E
240​
Ë
203​
E
241​
Ì
204​
I
242​
Í
205​
I
243​
Î
206​
I
244​
Ï
207​
I




I the built this function
Code:
Option Compare Binary
Option Explicit

Public Function ReplaceCharacter(strText As String) As String
  'Module must be set Compare Binary to get proper upper and lower case
  Dim rs As DAO.Recordset

  Dim toReplaceChar As String
  Set rs = CurrentDb.OpenRecordset("tblCharacters")
  Do While Not rs.EOF
    toReplaceChar = ChrW(rs!ASCI_W)
    strText = Replace(strText, toReplaceChar, rs!Replace)
    rs.MoveNext
  Loop
  ReplaceCharacter = strText
End Function

Seems to work on all of those cases in the table. So you can use this is in a query. Probably not as efficient since you are opening a recordset each time, but easy to maintain. If you added a language field then you could modify the function to select only characters from that language, because this is obviously overkill with small chance of seeing most of these characters.
 

Attachments

  • CleanCharacters.accdb
    436 KB · Views: 582

roscoe10s

New member
Local time
Today, 11:36
Joined
Jun 14, 2021
Messages
6
Awesome! I'll take a look.

It's so odd that importing data into a table preserves the characters, even the Unicode-only, and you can program based on the codes, but displays outside of the imported data (e.g., code/immediate window) will not display the characters. I know it's about language settings and I saw some posts about how to customize and change, and that's just too much work for the small batches of data (but very important!) I have to work with monthly.

Thanks!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:36
Joined
May 21, 2018
Messages
8,463
I believe that Access can handle unicode, but the vba immediate window can only handle the lower ASCII (1-255). So if you do this
Code:
Public Sub testChar()
  Dim i As Integer
  For i = 255 To 400
   Debug.Print ChrW(i)
  Next i
End Sub
It looks to me like it gives the best guess in the lower 255, and cannot print these upper characters.

I was dealing with other unicode beyond just international characters. I learned some interesting things, but do not fully understand how all of this works.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:36
Joined
May 21, 2018
Messages
8,463
Looking at this page
I would think my english equivalents would need to be updated for Polish. For example it looks to me like
Ł would be replaced with a W and not L
Ć would maybe be CH
The others are not as clear what would be the best substitution.
But this makes the table idea flexible, so you can tailor your outputs as needed.
 

Isaac

Lifelong Learner
Local time
Today, 09:36
Joined
Mar 14, 2017
Messages
8,738
@roscoe10s

Going back to the question of why your original code didn't work. I just wanted to let you know that I set up a scenario exactly as you have it - same tables, exact same code you were using.

For the replacement strategy, I decided to go with an original value of Ó to be replaced with a value of regular O.
I used this site to help me find the correct codes. According to it, the Ó was 224 and the O was 79.

I then stepped through your code. The mechanisms of the code were working perfectly. However, Access did not see Ó as 224--it saw it as 211.

I then changed my [t_ASCII] accordingly...."replace 211 with 79", re-ran the code, and it worked perfectly. The [Bill_to_Name] values were updated as desired.

Other than knowing that Unicode is the code page of the future (and why I like the idea of switching from 'defaulting' to varchar, to nvarchar, in SQL if possible)........I actually am not very knowledgeable on this ASCII vs. Unicode subject, but perhaps someone else will jump in and explain why.

I like to go to the 'earliest' point of failure in an original post and see what went wrong - so hopefully this helps simply in confirming that your original code was perfectly fine - (at least for me it runs fine) - you only need to get the right codes. You could probably solve that by simply looping through a field that contains every oddball character and writing the data to a text file for your own reference: i.e.

Textstream.Writeline mid(rs.fields("FieldName").value,lngPosition,1) & "=" & asc(rs.fields("FieldName").value)

....which would leave no doubt in your mind how Access will, in fact, see each oddball character.

PS Edit: The way you used the variable N did catch my eye, as something I would never do. (defining N as a local variable, but also using it as the For loop construct, and then using it again inside the For loop as its variable identity). It is so confusing that I thought VBA might not be able to handle that, but I guess it can...interesting.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:36
Joined
May 21, 2018
Messages
8,463
Textstream.Writeline mid(rs.fields("FieldName").value,lngPosition,1) & "=" & asc(rs.fields("FieldName").value)
As I pointed out ASC can only be used from 1 to 255, and a lot of the characters in the users set are above 255 requiring AscW or ChrW.

If you look at the table I made it is correct because I reversed engineered it. I looped the counter and inserted into the table the ChrW and the counter or the correct Ascii value

However I found a bizarre issue for characters from 128 to 159. See this article
In this range ChrW AscW and Chr ASC do not coincide and you have to use Chr and Asc

The CHRW set includes values from -32768 to 65535 (korean, chinese, japanase, arabic and other non latin alphabets and characters)

So here is a slightly modified version that accounts for the few values in the 128 to 159. I stopped my table at 400, but there could be a few above. I also put the giant table from -32768 to 65535 if interested
 

Attachments

  • CleanCharactersV2.accdb
    4.7 MB · Views: 417

Isaac

Lifelong Learner
Local time
Today, 09:36
Joined
Mar 14, 2017
Messages
8,738
As I pointed out ASC can only be used from 1 to 255, and a lot of the characters in the users set are above 255 requiring AscW or ChrW.
Yes that's a good point about c h r w. The point I was making was just get insight into however access sees it, no matter how inaccurate it may technically be. And the fact that the code worked fine
 

Users who are viewing this thread

Top Bottom