Using VBA to modify normal alphabetic sort order in a table

[RHubbard]

Here is another off-beat problem that I need help with. I am working with a large Access database of foreign language (Greek) words that is being used to create a dictionary. The Greek alphabet is represented by translated western (“English”) letters. A True Type font makes the letters display correctly, however.

Here is the problem. The western letters do not correspond with the sort order of the Greek letters. As an example
A=Greek alpha =1 (in Greek alphabet)
B=Greek Beta =2
C=Greek Zeta=14
D=Greek delta=4
E=Greek epsilon=5
F=Greek phi =21
G=Greek Gamma=3
H=7

On my first go around with this data I copied all the words to a single column in an Excel spread sheet and then duplicated the column of words. Then I used Search and Replace to replace the alpha characters in the words in the second column with numbers using the following scheme:

All occurrences of “a” = 01-
All occurrences of “b” = 02-
All occurrences of “c” = 14-
All occurrences of “d” = 04-
Etc.

The end result of all this work was that I had 2 columns of about 18,000 rows on the spreadsheet that look like this

ColA=====ColB
Abcd=====01-02-14-04

Next I sorted both columns, using ColB as the sort key. All the words in ColA were then in correct (Greek) alphabetic order. Next, I inserted a column and sequentially numbered all the Greek words from 1-18,333, then I deleted the column containing 01-02-14-04, etc. After that I imported the spreadsheet into an Access table where it functions a sort table.

As you can imagine, this was an immense amount of work and I am now facing doing it again since I need to add another group of around 80,000 words to the database.

Is there a way to replicate what I did manually in Excel by using VBA in Access?

Thanks for the help.

Rick

 

[Bodisathva]

quick and dirty...two tables required. cypher (letter, value), words(orig, newVal)

Dim tmpVal as String
Set recWords = CurrentDB.OpenRecordset("words", dbOpenDynaset)
Set recCypher = CurrentDB.OpenRecordset("cypher",dbOpenSnapshot)

With recWords
    .movefirst
    While Not .EOF
          recCyper.moveFirst
          tmpVal = .newVal
          While not recCyper.eof
                 tmpVal= Replace(tmpVal, recCypher.letter, recCypher.value)
                 recCypher.movenext
           Wend
           .Edit
           .newVal = tmpVal
           .Update
           .moveNext
      Wend
End With