VBA @ Access on replacing values on a huge list

Question

I have a question (that's why I'm here actually :P ) about VBA @ MS Access.

So, I have a huge database (well, for me it is huge, about 23K+ ID) on shoes. It has 5 columns: ID, Article number, size (mm),pairs (avaliable on stock) and EAC13 codes. It is updated daily. Everyday I get the update on it, on CSV (all those ID all over again) and create new tables, although I just need to update stock availability. However there is one column that I have to change in order to get reports: Sizes. I have to get 2 reports, one where I get the size of the shoe in mm and the other where the size is on European measure. For example: 245 mm is equal to a 35. Simple as that. I have a VBA that I thought it could help me to replace all those values (yes, all the 23k on the "size" column), that goes like this

Public Sub Substitui()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT * FROM BS_2704"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF
For Each fld In rst.Fields
If fld.Value = "150" Then
rst.Edit
fld.Value = "24"
rst.Update
End If

If fld.Value = "160" Then
rst.Edit
fld.Value = "25"
rst.Update
End If

(...)

If fld.Value = "325" Then
rst.Edit
fld.Value = "50"
rst.Update
End If


Next fld
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Anyway...this only changes 150 entries (more or less). The million dollar question is, is there any kind of line that I am missing here in order to get this VBA to change ALL the 23k entries?

I am a n00b on this fields, so I would appreciate some n00b therms for a start. Thanks for the patience.

Answer 1

I think I can guess why your VBA is only changing around 150 records instead of all of them, but more importantly, you need to know that the specific method you're using--changing all the fields using VBA--can lead to incorrect data in your table. And, you can accomplish what you want much more easily with a SQL query ... even one made using the query designer. But for now I'll stick with plain SQL. There are three possibilities and corresponding approaches:

1 You just have to change a limited number of different sizes from mm to European. In that case use the switch function, e.g.:

update BS_2704
set Sizes = switch(
  Sizes = 150, 24
, Sizes = 160, 25
, ...
, Sizes = 325, 50
, true, Sizes
)

2 You can use a mathematical formula to convert from your mm size to your European measure, e.g. like http://en.wikipedia.org/wiki/Shoe_size#Europe:

update BS_2704
set Sizes = Sizes * 3 / 20

3 You can't use a mathematical formula, and you have such a large number of conversion mappings that putting them all in a single switch function is unwieldy: in this case you create a new table, e.g. named tblSizeMappings, with the mm sizes and the corresponding European sizes:

SizeMM SizeEuropean
   150           24
   160           25
   325           50

Then, do an update query like above but using the tblSizeMappings table to get the European sizes:

update BS_2704
inner join tblSizeMappings
on BS_2704.Sizes = tblSizeMappings.SizeMM
set BS_2704.Sizes = tblSizeMappings.SizeEuropean