Adding a Field using VB to an existing table.

[Bobbak]

Hello All,
I have these tables (lets call it ‘EmpCalls', ‘EmpOrders', and
‘Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?
Any suggestion will be greatly appreciated.

[Ben Eaton]

You could use DoCmd.RunSQL (or the DAO equivalent) to run an ALTER TABLE
statement which adds the field. Look up the syntax in Access help.

I'm curious though, why would you want to do this? I feel scary database
design in the offing...

"Bobbak" <bo****@ottawa.com> wrote in message
news:64**************************@posting.google.c om...

Hello All,
I have these tables (lets call it 'EmpCalls', 'EmpOrders', and
'Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?
Any suggestion will be greatly appreciated.

[Marshall Barton]

Bobbak wrote:

I have these tables (lets call it ‘EmpCalls', ‘EmpOrders', and
‘Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?

Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.

You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.

--
Marsh
MVP [MS Access]

[Bobbak]

Marshall Barton <ma*********@wowway.com> wrote in message news:<rt********************************@4ax.com>. ..

Bobbak wrote:

I have these tables (lets call it ?EmpCalls', ?EmpOrders', and
?Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?

Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.

You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.

Well the database file itself will only contain data for one month at
a time, so the 255 field limit is not an issue.
The reason for me to have the code to add a column instead of adding
the columns manually when designing the table is to save myself some
time every month from re-creating the tables.
But thanks again for your suggestion, if you have any other ideas on
how i can achieve this, I'd very much like to know.

[paii, Ron]

What are you planning to do when your boss wants a comparison of the last 2
months.

"Bobbak" <bo****@ottawa.com> wrote in message
news:64**************************@posting.google.c om...

Marshall Barton <ma*********@wowway.com> wrote in message

news:<rt********************************@4ax.com>. ..

Bobbak wrote:

I have these tables (lets call it ?EmpCalls', ?EmpOrders', and
?Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?

Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.

You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.

Well the database file itself will only contain data for one month at
a time, so the 255 field limit is not an issue.
The reason for me to have the code to add a column instead of adding
the columns manually when designing the table is to save myself some
time every month from re-creating the tables.
But thanks again for your suggestion, if you have any other ideas on
how i can achieve this, I'd very much like to know.

[Paul]

"Bobbak" wrote

Marshall Barton <ma*********@wowway.com> wrote in message news:<rt********************************@4ax.com>. ..

Bobbak wrote:

I have these tables (lets call it ?EmpCalls', ?EmpOrders', and
?Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?

Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.

You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.

Well the database file itself will only contain data for one month at
a time, so the 255 field limit is not an issue.
The reason for me to have the code to add a column instead of adding
the columns manually when designing the table is to save myself some
time every month from re-creating the tables.
But thanks again for your suggestion, if you have any other ideas on
how i can achieve this, I'd very much like to know.

Columns are added using SQL's ALTER TABLE, DAO's CreateField
or ADO's Fields.Append. However, your problem description
suggests this may not be the solution.

De-normalizing can be the thing to do - most often for boosting
performance in reporting systems. Tracking history (that's what
your after, or?) is hardly ever a good reason and de-normalizing
operational data is almost taboo.

Look again at Marshall's suggestion and consider crosstab queries for
lining up the dates. Why re-create tables or add columns at all?
Simply empty the tables each month, if you really must.

[Bobbak]

"paii, Ron" <pa**@packairinc.com> wrote in message news:<vp************@corp.supernews.com>...

What are you planning to do when your boss wants a comparison of the last 2
months.

That's not gonna happen, the purpose of this is to have a monthly
figure per employee, by day.

Now can we get back to my orginal question.

[Bobbak]

"Ben Eaton" <be************@hotmail.com> wrote in message news:<bn***********@news.wplus.net>...

You could use DoCmd.RunSQL (or the DAO equivalent) to run an ALTER TABLE
statement which adds the field. Look up the syntax in Access help.

I'm curious though, why would you want to do this? I feel scary database
design in the offing...

You have nothing to be scared of, this isn't your database that you
are doing this too.

[paii, Ron]

You will may not get much help doing what you propose. Along with what
Marshall Barton stated, I think you will have table corruption problems is
you try to add fields to a active database. You cannot add fields to a table
while you are viewing it with a form.

If you add a date field to your detail records, it will be easy to use a
cross tab query to extract dates from any month. The Cross tab will have the
dates as column headings. You can then empty the tables every month,
although that will not be necessary.

"Bobbak" <bo****@ottawa.com> wrote in message
news:64**************************@posting.google.c om...

"paii, Ron" <pa**@packairinc.com> wrote in message

news:<vp************@corp.supernews.com>...

What are you planning to do when your boss wants a comparison of the last 2 months.

That's not gonna happen, the purpose of this is to have a monthly
figure per employee, by day.

Now can we get back to my orginal question.

[Mike Painter]

"Bobbak" <bo****@ottawa.com> wrote in message
news:64**************************@posting.google.c om...

"Ben Eaton" <be************@hotmail.com> wrote in message

news:<bn***********@news.wplus.net>...

You could use DoCmd.RunSQL (or the DAO equivalent) to run an ALTER TABLE
statement which adds the field. Look up the syntax in Access help.

I'm curious though, why would you want to do this? I feel scary database design in the offing...

You have nothing to be scared of, this isn't your database that you
are doing this too.

We do get a bit scared and do have something to be scared of.
We might be hired after you leave and one person bad mouthing Access does
more harm than 1000 silent because they are happy people can overcome.

Mostly I suspect we are curious since writing code to add something that
will only be useful in a table after it has been created unless you plan on
writing a lot more code is a curious thing.

[Pieter Linden]

Might be a more useful table if it were something like

CREATE TABLE SomeTable(
EmployeeID Long,
ActionDate Date,
Amount Currency,
PRIMARY KEY (EmployeeID, ActionDate));

Sorry for the bad SQL, but you get the idea. This way, as long as you
add only one value per employee per day, who cares if you have a half
million records? You can filter, summarize, and all that good
stuff... PITA if you keep adding columns all over the place...

[Marshall Barton]

>> Bobbak wrote:

> I have these tables (lets call it ?EmpCalls', ?EmpOrders', and
>?Stats') that each contain the list of EmployeeIDs, I want to be able
>to create a Module in which I could call in my VB form (by clicking
>and command button)that will add a column (field) to each table and
>label it with the current date that is specified in my form. Does
>anyone know how I can go about doing this?

Marshall Barton wrote

Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.

You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.

Bobbak wrote:Well the database file itself will only contain data for one month at
a time, so the 255 field limit is not an issue.
The reason for me to have the code to add a column instead of adding
the columns manually when designing the table is to save myself some
time every month from re-creating the tables.
But thanks again for your suggestion, if you have any other ideas on
how i can achieve this, I'd very much like to know.

I'm pretty sure I understand what you're trying to do. It's
just that I think you are creating a host of problems for
the future. What you described sound like a one to many
relationship from the table to the dates and whatever data
you place in the date columns.

Oh well, it's your db and you can ignore advice if you want
to. I think an DAO air code outline of a routine to add a
column for the date in a text box on a form might look like:

Dim db As Database
Dim tdf as TableDef
Dim fld As Field

Set db = CurrentDb()
Set tdf = db.TableDefs("thetable")
Set fld = tdf.CreateField(Format(Forms!theform.thetextbox, _
dbInteger)
tdf.Fields.Append fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

--
Marsh
MVP [MS Access]