Adding columns through commands in access

[dylanreid]

Hello and thank you for the help!

I have two questions about commmands:

1) I would like to execute a sql command that adds a column to a particular table. Can anyone give me the syntax to do this?

2) How can I check if a column exists with a sql command?

Thank you in advance,
Dylan

 

[foundryqa]

dylanreid,

You can use a make table query and add an additional field to the query that does not already exist in the table you are querying. The SQL looked like this for a table "Employees" that I added one field to called "New Field"

SELECT Employee.dept, Employee.classcode, "" AS [New Field] INTO [New By Part]
FROM By_Part__, Employee;

The Employee.dept and Employee.classcode were existing fields in the table. The "" is so that when the field "New Field" is created, there will be no entries in it. The INTO [New By Part] is the table name that will be created when the query is run. The FROM clause states from which table the original two fields came.

As for your second question, maybe someone else can answer.

--Foundry

 

[cheerio]

Data definition queries are used to alter existing tables and can be used to create new ones.

Go to Queries, New, Design View.
Close the Show table dialog withouth selecting a table.
Go to Query, SQL Specific, Data Definition

You now type your command as a SQL string.
For example, this will add a new field called newcol as a character field of width 5 to the table called TABLE1:

ALTER TABLE TABLE1 ADD COLUMN newcol text(5)

 

[cheerio]

I should have mentioned that this is not the best place to ask this question. There are a range of Tek-Tips furums devoted to Access such as Microsoft Access Queries and Jet SQL.

 

[RoyVidar]

You'll have to pardon my conservatism, but whenever someone wishes to add fields/columns at run time, I'm suspecting there are some flaws in the design. I've never seen any need for it, and design all tables at design time, and when adding new fields, it's performed in a planned update/maintainance session.

You may want to have a look at some info on normalisation, for instance Fundamentals of Relational Database Design by Paul Litwin.

For the first question, I think both of the given suggestions should work, for the second question, I haven't yet come accross a way to check that through SQL, but there are various ways of checking it through VBA coding.

Welcome to Tek-Tips!
As cheerio said, there are some dedicated Access fora here at Tek-Tips (seven, to be accurate), that you may find through the forum search on the top of the page. Here's also a faq on how to get the most out of the membership faq181-2886.

Try the advanced search function with regards to your second question, for instance in the Access VBA forum (forum705), and if you don't find anything, try posting there - but as said, I think you'll be looking for some coding to achieve this (would be happy to be proven wrong on this, though;-))

Good Luck!

Roy-Vidar