Adding fields to a table through a query

L

laurenm

Is it possible to add fields to a table through a query? So not new records,
but new fields. For example, if I want a user to add in field name "C", I
would want this user to go to a form with a text box in a form enter "C" and
have that new field appear in my existing table. Is this possible using an
update query or append query. Please let me know.

Thanks,
Lauren
 
R

Rick Brandt

laurenm said:
Is it possible to add fields to a table through a query? So not new records,
but new fields. For example, if I want a user to add in field name "C", I
would want this user to go to a form with a text box in a form enter "C" and
have that new field appear in my existing table. Is this possible using an
update query or append query. Please let me know.
Click to expand...


No, and a requirement to do so almost certainly suggests your design is
flawed. One should never have to modify table structures as part of normal
usage.
 
D

DBS

Yes, it is possible. Just use a Data Definition Query

- Create a new query, but don't add any tables.
- On the Menu Bar, select Query | SQL Specific | Data Definition
- Write your query to modify the table, using the ALTER TABLE syntax.

For example: "ALTER TABLE Tablename ADD COLUMN NewField TEXT(25);"

For more information about writing an ALTER TABLE query, search Help.

Hope that helps!

DBS (David Staas)
 
V

Van T. Dinh

It is possible using the ALTER TABLE ... SQL syntax or you can use DAO code
or ADO (ADOX?) code. However, you need to consider why you need to do so.
In my production databases, users *never* add Fields to Tables. The need to
do so often indicate that the Database is not correctly structured. You
need to consider how to display these values on the Forms (which were
designed) before the new Fields were created.
 
L

laurenm

Thanks! This will be very helpful.

Lauren

DBS said:
Yes, it is possible. Just use a Data Definition Query

- Create a new query, but don't add any tables.
- On the Menu Bar, select Query | SQL Specific | Data Definition
- Write your query to modify the table, using the ALTER TABLE syntax.

For example: "ALTER TABLE Tablename ADD COLUMN NewField TEXT(25);"

For more information about writing an ALTER TABLE query, search Help.

Hope that helps!

DBS (David Staas)
Click to expand...
 
L

laurenm

Thank you for your response.

This is not something I really want to do but I am getting a request from
someone in my office to see if it is possible. Now I know that it is, I can
at least answer his question correctly while trying to steer him away from
this idea.

Thanks,
Lauren
 
L

laurenm

Hi David,

Thanks for your advice. It worked like a charm. I have one more question
now. Is there a way to set a parameter/criteria through this Alter Table
Query that will let me put the specific name of a field in? For example, one
day I want to add one field named "Patch1" the next day I want to add another
field to that table called "Patch2". Without have to go in and change the
query every time. Please let me know.

Thanks,
Lauren
 
Top