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!
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?
RE: Append multiple tables?
I'm quite new to this.
RE: Append multiple tables?
CODE
'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?
thanks
RE: Append multiple tables?
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?
CODE
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?
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?
CODE
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?
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?
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
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?
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:/