Need to add a field to all tables in database

[kmclane]

I am trying to find a way to add a field to every table in my db. I get data every month in the same tables with the same fields. I would like to combine the data into a single database but I will still need to be able to pull say quarterly and YTD reports. Some tables have datetime fields but only a couple, and since some of the data used this month may have come from a previous month, I want a way to identify what data belongs with which month. I am thinking of adding a datefield that would just take a month and year so I could use that to filter for reporting. I'm not sure what type of field to use. Also is there a way to add and populate the field across every table in the db? Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[jadams0173]

You can use something like this.

Dim db as DAO.Database

set db=CurrentDB

db.Execute "ALTER TABLE " & TabName & " ADD COLUMN DataDate date"

and loop thru all the tables and use TabName as a variable each time. DataDate would be the name of the new field that you are inserting and it would be date/time field type.

Also you would need to make a reference to Microsoft DAO Ojbect Library in you references from the VBA editor.

 

[kmclane]

Thanks, I got that to work like this:

Function AddTable()
Dim rs As Recordset
Dim db As DAO.Database
Dim Tabname As String
Set db = CurrentDb
Set rs = db.OpenRecordset("listTables")
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
End If
Do While Not rs.EOF
On Error Resume Next
Tabname = rs(0).Value
db.Execute "ALTER TABLE " & Tabname & " ADD Column DateMY date;"
rs.MoveNext
Loop
End Function

Now I am trying to update the field with data. I basically want to have it contain last month, I don't really care about the time or even the day, I just want to be able to key on month and year. So I tried this:

Function AddDate()
Dim rs As Recordset
Dim db As DAO.Database
Dim Tabname As String
Dim strdate As Date
Set db = CurrentDb
Set rs = db.OpenRecordset("listTables")
strdate = DateAdd("m", -1, Date)
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
End If
Do While Not rs.EOF
Tabname = rs(0).Value
DoCmd.RunSQL "UPDATE " & Tabname & " SET " & Tabname & ".DateMY = " & strdate & ";"
rs.MoveNext
Loop
End Function

This populated the field, but I see only a time "12:00:30 AM" If I go into Design mode and set the format to "Shortdate" I see "12/30/1899". So I need to obviously fix the year problem, and I would like to set the format so it displays fine. I will need to use this field for monthly, quarterly and yearly reporting. Thanks.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[lesw1433]

Will this work?

DoCmd.RunSQL "UPDATE " & Tabname & " SET " & Tabname & ".DateMY = #" & strdate & "#"

Delimit using the # before and after your strdate