Adding a Field using VB to an existing table.

  • Thread starter Thread starter Bobbak
  • Start date Start date
B

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.
 
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 said:
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?
Click to expand...

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.
 
Marshall Barton said:
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.
Click to expand...

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.
 
"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.
Click to expand...

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.
 
Ben Eaton said:
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...
Click to expand...
You have nothing to be scared of, this isn't your database that you
are doing this too.
 
Bobbak said:
"Ben Eaton" <[email protected]> wrote in message
You have nothing to be scared of, this isn't your database that you
are doing this too.
Click to expand...

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.
 
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...
 
Bobbak said:
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.
Click to expand...


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
 
Back
Top