Add a new field in every table in a database (100 tables)

[hrprabhu]

Hi All,

I inherited an old database. Not designed too well. Nearly 100 tables with different types of names. Some table names start with and "_", some with "tbl" and some just names.

I want to add a new field called "TimeStamp", date/time, and default value Now() to all the tables. Any pointers would be of help.


Thanks

Regards

Raghu

[Seth Schrock]

If you are looking for a VBA approach, then it should be fairly simple to loop through all your tables and add the field that you want then.

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fl As DAO.Field
 
Set db = CurrentDb()
 
For Each td In TableDefs
    Set fl = td.CreateField("TimeStamp", dbDate)
    fl.DefaultValue = "=Now()"
    td.Fields.Append fl
Next

[hrprabhu]

It is even trying to add the field to the MSys tables too. Otherwise this is good.

Thank you Seth Schrock

[zmbd]

Enclose lines 8 thru 10 in conditional if-then
check left-4 of name for MSYS Ucase(left(td.name,4))<>"MSYS" and if not equal then change the table otherwise go to the next object.

[hrprabhu]

Hi All this is working fine. I am able to amend the tables by adding a field.

How do I put default value as Now() and caption as "Time Stamp"

Public Sub AddDate2AllTbls()
Dim tdf As TableDef
Dim fld As DAO.Field
Dim strTableName As String
For Each tdf In CurrentDb.TableDefs   'scan each tbl
 
   strTableName = tdf.Name
 
       If Left(strTableName, 4) = "~TMP" Then GoTo SkipTable
       If Left(strTableName, 4) = "ztbl" Then GoTo SkipTable
       If Left(strTableName, 4) = "MSys" Then GoTo SkipTable
       If Left(strTableName, 4) = "Usys" Then GoTo SkipTable
       If Left(strTableName, 2) = "f_" Then GoTo SkipTable
 
   tdf.Fields.Append tdf.CreateField("TimeStamp", dbDate)
'  set defaultvalue = "=Now()"
'  set caption = "Time Stamp"
 
SkipTable:
Next
Set tdf = Nothing
MsgBox "done"
End Sub
 
 

Thanks you

[zmbd]

Go back to Seth's code,
Note how the field is created in lines 8 thru 10.
That is how you will need to set the default.

Not sure what you are referring to as "Field Caption"

[twinnyfo]

hrprabhu,

Are you trying to add the value of Now() to the field as well? That would require accessing each table and cycling through all records.

Modifying Seth's code:

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fl As DAO.Field
 
Set db = CurrentDb()
 
For Each td In TableDefs
    If Not (Left(strTableName, 4) = "~TMP" Or _
        Left(strTableName, 4) = "ztbl" Or _
        Left(strTableName, 4) = "MSys" Or _
        Left(strTableName, 4) = "Usys") Or _
        Left(strTableName, 2) = "f_" Then
 
        Set fl = td.CreateField("TimeStamp", dbDate)
        fl.DefaultValue = "=Now()"
        fl.Caption = "Time Stamp"
        td.Fields.Append fl
Next

should do the trick.

Hope this hepps!