By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,945 Members | 1,540 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,945 IT Pros & Developers. It's quick & easy.

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

P: 83
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
Apr 16 '15 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim td As DAO.TableDef
  3. Dim fl As DAO.Field
  4.  
  5. Set db = CurrentDb()
  6.  
  7. For Each td In TableDefs
  8.     Set fl = td.CreateField("TimeStamp", dbDate)
  9.     fl.DefaultValue = "=Now()"
  10.     td.Fields.Append fl
  11. Next

Share this Question
Share on Google+
6 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
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.

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim td As DAO.TableDef
  3. Dim fl As DAO.Field
  4.  
  5. Set db = CurrentDb()
  6.  
  7. For Each td In TableDefs
  8.     Set fl = td.CreateField("TimeStamp", dbDate)
  9.     fl.DefaultValue = "=Now()"
  10.     td.Fields.Append fl
  11. Next
Apr 16 '15 #2

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

Thank you Seth Schrock
Apr 17 '15 #3

zmbd
Expert Mod 5K+
P: 5,397
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.
Apr 17 '15 #4

P: 83
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"

Expand|Select|Wrap|Line Numbers
  1. Public Sub AddDate2AllTbls()
  2. Dim tdf As TableDef
  3. Dim fld As DAO.Field
  4. Dim strTableName As String
  5. For Each tdf In CurrentDb.TableDefs   'scan each tbl
  6.  
  7.    strTableName = tdf.Name
  8.  
  9.        If Left(strTableName, 4) = "~TMP" Then GoTo SkipTable
  10.        If Left(strTableName, 4) = "ztbl" Then GoTo SkipTable
  11.        If Left(strTableName, 4) = "MSys" Then GoTo SkipTable
  12.        If Left(strTableName, 4) = "Usys" Then GoTo SkipTable
  13.        If Left(strTableName, 2) = "f_" Then GoTo SkipTable
  14.  
  15.    tdf.Fields.Append tdf.CreateField("TimeStamp", dbDate)
  16. '  set defaultvalue = "=Now()"
  17. '  set caption = "Time Stamp"
  18.  
  19. SkipTable:
  20. Next
  21. Set tdf = Nothing
  22. MsgBox "done"
  23. End Sub
  24.  
  25.  

Thanks you
Apr 21 '15 #5

zmbd
Expert Mod 5K+
P: 5,397
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"
Apr 21 '15 #6

twinnyfo
Expert Mod 2.5K+
P: 3,487
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:

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim td As DAO.TableDef
  3. Dim fl As DAO.Field
  4.  
  5. Set db = CurrentDb()
  6.  
  7. For Each td In TableDefs
  8.     If Not (Left(strTableName, 4) = "~TMP" Or _
  9.         Left(strTableName, 4) = "ztbl" Or _
  10.         Left(strTableName, 4) = "MSys" Or _
  11.         Left(strTableName, 4) = "Usys") Or _
  12.         Left(strTableName, 2) = "f_" Then
  13.  
  14.         Set fl = td.CreateField("TimeStamp", dbDate)
  15.         fl.DefaultValue = "=Now()"
  16.         fl.Caption = "Time Stamp"
  17.         td.Fields.Append fl
  18. Next
should do the trick.

Hope this hepps!
Apr 29 '15 #7

Post your reply

Sign in to post your reply or Sign up for a free account.