×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Append multiple tables?

thread700-1652099

Append multiple tables?

Append multiple tables?

(OP)
Hi there,
   I have roughly 150 tables and want to add a date field to each table. Is there a fast way to do this or do i have to do each table individually?
Thanks!

RE: Append multiple tables?

Using DAO you could loop through each tabledef in the tabledefs collection and use the createfield method to add the field (it has been a while you may have to create the field and then append it to the fields collection)...

RE: Append multiple tables?

I need to do this also but don't understand what lameid is saying. I need to add 8 fields to nearly 150 tables and i don't want to go through them one by one! Is there an easy way of doing it?

I'm quite new to this.

RE: Append multiple tables?

This is off the cuff and may need a little debugging but I mean code similar to the below.


CODE

Sub Example()
'Requires Reference to DAO object model
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim tbls As DAO.TableDefs
Dim fld As DAO.Field

Set db = CurrentDb()

Set tbls = db.TableDefs


For Each tbl In tbls
     With tbl
        Set fld = .CreateField("Created", dbDate)
        fld.DefaultValue = "Now()" 'code not tested... may need Equal sign before
        .Fields.Append fld
    End
Next tbl

Set tbl = Nothing
Set tbls = Nothing
Set db = Nothing
End Sub

RE: Append multiple tables?

(OP)
That doesn't seem to be working..I know its very close though! Lets say im trying to add the following fields to "Table1", "x9", "x10", "x11" ..."x16".. Im new to dao!
thanks

RE: Append multiple tables?

(OP)

This is the code i have however it still will not add the fields to my table2. Im just throwing this in with the rest of the code, should i use a button and macro to run the code or where should i put it? THANKS FOR THE HELP!

Private Sub AddColumn()
Dim curDatabase As Database
Dim Table2 As TableDef
Dim x9 As Field
Dim x10 As Field
Dim x11 As Field
Dim x12 As Field
Dim x13 As Field

' Get a reference to the current database
Set curDatabase = CurrentDb

' Get a reference to a table named TestTable - NOTE: The table MUST exist
Set Table2 = curDatabase.TableDefs("Table2")


'define the fields using the CreateField method
' Syntax: .CreateField("FIELD NAME", TYPE, [Length])
' I used the predefined types already in Access
Set x9 = Table2.CreateField("x9", dbDouble)
Set x10 = Table2.CreateField("x10", dbDouble)
Set x11 = Table2.CreateField("x11", dbDouble)
Set x12 = Table2.CreateField("x12", dbDouble)
Set x13 = Table2.CreateField("x13", dbDouble)

'use the APPEND method to add the fields to the table
With Table2.Fields
.Append x9
.Append x10
.Append x11
.Append x12
.Append x13
End With

End Sub

RE: Append multiple tables?

loop your table names and pass to the following

CODE

Public Sub addFields(tblName As String)
  Dim strSql As String
  Dim I As Integer
  Dim colName As String
  For I = 9 To 13
    colName = "X" & I
    strSql = "ALTER TABLE " & tblName & " ADD COLUMN " & colName & " Double"
    CurrentDb.Execute strSql
  Next I
End Sub

RE: Append multiple tables?

Joe,

Often code is run via the On Click event of buttons on forms or one offs like this directly from the immediate window (ctrl+G)...

So with your procedure, I would just put your procedure name in the immediate window and run it.

MajP's example is another way to go.  He is using the SQL statements to perform the same action...  It is probably easier to grasp than my example but you seem to have understood it.

If you run your code and it doesn't work, I've probably missed some nuance somewhere of how this works in DAO.

RE: Append multiple tables?

Your code looks correct, I ran a slightly modified version of your code and get the appended fields.

CODE

Public Sub addFieldDAO(tblName As String)
 Dim tdf As DAO.TableDef
 Dim fld As DAO.Field
 Dim fldName As String
 Dim db As DAO.Database
 Dim I As Integer
 Set db = CurrentDb
 Set tdf = db.TableDefs(tblName)
 For I = 1 To 13
    fldName = "X" & I
    Set fld = tdf.CreateField(fldName, dbDouble)
    tdf.Fields.Append fld
 Next I
End Sub

RE: Append multiple tables?

(OP)
Thanks guys that's great!
Stupid Q, How do i loop the tables?
heres where i am.....


Private Sub addFieldDAO_Click(tblName As String)

 Dim tdf As DAO.TableDef
 Dim fld As DAO.Field
 Dim fldName As String
 Dim db As DAO.Database
 Dim I As Integer
 Set db = CurrentDb
 Set tdf = db.TableDefs(tblName)
 For I = 9 To 16
    fldName = "x" & I
    Set fld = tdf.CreateField(fldName, dbDouble)
    tdf.Fields.Append fld
 Next I

End Sub

     Seeing as this would be a "one off" then i think ill try and execute my code in the immediate window(As lameid suggested) however I'm still getting a Compile error
   this is what I'm passing into the window..
   
   Form_Calculations.addFieldDAO_Click(tblName As String)
(Calculations is the name of my form)

The Compile error I'm getting is:::: "Expected: List separator or )"


This is driving me mad, can anybody see where im going wrong?
Thanks again!

RE: Append multiple tables?

Do you tables have some kind of common naming convention? Need some way to loop through the table definitions and exclude the tables (especially the system tables) that we do not want to add fields to.

Excluding the hidden system tables is easy because they all start with "MSys", but if you have other tables you do not want to include need some way to identify those or the ones to include

to run this from the immediate window just do

addFieldDAO "SomeTableName"

RE: Append multiple tables?

CODE

Public Sub addFieldDAO(tblName As String)
 On Error GoTo errLabel
 Dim tdf As DAO.TableDef
 Dim fld As DAO.Field
 Dim fldName As String
 Dim db As DAO.Database
 Dim I As Integer
 Set db = CurrentDb
 Set tdf = db.TableDefs(tblName)
 For I = 9 To 13
    fldName = "X" & I
    Set fld = tdf.CreateField(fldName, dbDouble)
    tdf.Fields.Append fld
 Next I
 Exit Sub
errLabel:
  If Err.Number = 3191 Then
    MsgBox "Field " & fldName & " already exists in " & tblName
    Resume Next
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

Public Sub loopTables()
  Dim tdf As TableDef
  Dim tdfName As String
  For Each tdf In CurrentDb.TableDefs
     tdfName = tdf.Name
     'probably need another rule to check for non system tables
     'that you do not want to add a field to
     If Not Left(tdfName, 4) = "MSYS" Then
        addFieldDAO tdfName
     End If
  Next tdf
End Sub

RE: Append multiple tables?

Joe,
  Any chance you could post some of your data?  My control limits data base is a pretty robust application, but I like to populate it with real data.  I have the following features
1) Can handle multiple processes
2) each process can have a user defined sample size (K).
3) Chart resizes based on range of data
4) Display out of limit values
5) choose which samples are included in the parameter estimates

I plan to add some of the other control limits analysis and charts
http://www.statsoft.com/textbook/quality-control-charts/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close