VBA function to UPDATE date value for all tables where tables have same prefix

[skydivetom]

Hello:

I need some assistance with correcting an UPDATE function (i.e., VBA) which will update a date field with a specified date value.

Background:
- Attached DB contains six (6) tables.
- Out of those 6 tables, 5 tables have a prefix = "LK_"
- Each "LK_" table has different fields; however, each table includes date field [DATE_UPDATED].
- Table "Data_Dictionary" should NOT be updated as part of the VBA UPDATE routine. That is, the VBA routine must only update all tables where first 3 characters = "LK_".

Proposed Code:

Private Sub cmdUpdateDate_Click()

    Dim tdf As DAO.TableDef
  
        For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) <> "Msys" _
                And Left(tdf.Name, 3) = "LK_" Then
    
                CurrentDb.Execute "UPDATE " & tdf.Name & " SET " & tdf.Name & ".DATE_UPDATED = '07/30/2021';"
                
            End If
        Next tdf

End Sub

As of now, I'm getting run-time error "424 - Object Required". What am I missing? How does the VBA need to be corrected update all LK table's field [DATE_UPDATED]?



Thank you,
Tom

[skydivetom]

Never mind... the following mods fixed the issue:

Private Sub cmdUpdateDate_Click()


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef
  
    Set db = CurrentDb()
  
        For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) <> "Msys" _
                And Left(tdf.Name, 3) = "LK_" Then


                CurrentDb.Execute "UPDATE " & tdf.Name & " SET " & tdf.Name & ".DATE_UPDATED = '07/30/2021';"
                
            End If
        Next tdf


End Sub