Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Append multiple tables?

Status
Not open for further replies.

joebox888

Technical User
Jun 17, 2011
24
IE
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!
 
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)...
 
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.
 
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
 
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
 

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
 
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
 
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.
 
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
 
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!
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top