Add new field in every table (100 tables)

[raghuprabhu]

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 in advance.

Raghu Prabhu

 

[Ranman256]

1. you shouldnt have to put new fields in that many tables. That tells me you have a design flaw.

2. Here is the code to put a date field in all tables.
Paste the code into a module. Then press Ctl-G , and in the immediate window, type: AddDate2AllTbls

Public Sub AddDate2AllTbls()
Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs   'scan each tbl
   tdf.Fields.Append tdf.CreateField("TimeStamp", dbDate)
Next

Set tdf = Nothing
MsgBox "done"
End Sub

 

[raghuprabhu]

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

Thank you Ranman256

 

[gemma-the-husky]

fwiw, I don't see any problem with your idea.

indicators to store who/when last touched records are common practice.
It's easier to add them with code, than doing it manually. At least they are there, even if you never use them.

I think Ranman is suggesting you might have more tables than you need, which may well be the case. 100 tables is an awful lot.

 

[Solo712]

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

Thank you Ranman256

Hi there,
You are right. It's good to filter out the system, temps and utility tables before working on the tableDef collection. Example below:

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)
SkipTable:
Next

Best,
Jiri

 

[raghuprabhu]

Hi All,

What is the syntex for setting various properties such as default value, Caption etc

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

Thank you very much

Raghu