Access VBA

css navigation by Css3Menu.com

Recordset to update another Table

I had already set my Access form to add the time zone from a zipcode lookup table. It was taking too long to go through everyone in my database to force an update; so I wrote this ditty.
Sub FixTimeZones()
    Dim sqlStr  As String
    Dim LUstr   As String
    Dim DB  As Database
    Dim RS  As Recordset
    Dim LU  As Recordset
    Dim X   As Long
    Dim QUO As String
    Set DB = CurrentDb
    QUO = Chr(34)
    X = 0
    sqlStr = "SELECT tblAddress.PostalCode, tblAddress.Country, tblAddress.Prefered, tblAddress.TimeZone,tblAddress.quality" & vbCrLf
    sqlStr = sqlStr & " From tblAddress" & vbCrLf
    sqlStr = sqlStr & " WHERE (((tblAddress.Country) = 1) And ((tblAddress.Prefered) = True) And ((tblAddress.TimeZone) Is Null))" & vbCrLf
    sqlStr = sqlStr & " ORDER BY tblAddress.PostalCode;"    'Query table to be updated

    Set RS = DB.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)
    RS.MoveFirst
    Do While Not RS.EOF
        With RS
            LUstr = "SELECT TOP 1  [Zip-codes-Base].ZipCode, [Zip-codes-Base].TimeZone" & vbCrLf
            LUstr = LUstr & " From [Zip-codes-Base]" & vbCrLf
            LUstr = LUstr & "WHERE ((([Zip-codes-Base].ZipCode)=" & QUO & Left(RS!PostalCode, 5) & QUO & "));"
                'Query table to draw from
             Set LU = DB.OpenRecordset(LUstr, dbOpenDynaset, dbSeeChanges)
             RS.Edit
            RS!TimeZone = "UTC-" & LU!TimeZone  'Time zone
            RS!Quality = "RS- " & Format(Now(), "mm/dd/yyyy HH:mm:ss")  'When table updated
            RS.Update
            X = X + 1
        RS.MoveNext
        End With
    Loop
    Set RS = Nothing
    Set LU = Nothing
    MsgBox "Done with " & X & " updates", vbInformation
End Sub

Turned a multiple hour job into about 5 seconds. And now that is in my project, I can re-purpose the code for anything in the future.

© 2023-2024

Updated:  06/21/2024 07:42
This page added:  06 October 2023