469,280 Members | 1,823 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,280 developers. It's quick & easy.

How to insert new Field (column) in query

I have a Select query from two tables, I want in this query to add new column so that I can enter a new data in that column directly. Under normal situations I know its impossiple, but with some sort of Designed VBA or SQL it can be made possible.
I also know how to insert column as expression in query, tell me how can a new field (not expression) be inserted?
Please, any hint!!!!
Dec 8 '10 #1
5 20609
8,800 Expert 8TB
You can accomplish this through the combination of ALTER TABLE & ADD COLUMN Syntax. The following Code will add a Field named [Next_Of_Kin] to the Northwind Employees Table. It will be TEXT Data Type with a Maximum Length of 25:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  3. strSQL = "ALTER TABLE Employees ADD COLUMN Next_Of_Kin TEXT(25);"
  5. CurrentProject.Connection.Execute strSQL
Dec 8 '10 #2
I have ambitions of using the code but couldnt make it.
Where can I exactly past the code?. Am not much familiar with access!
Dec 8 '10 #3
Sorry it works!
I have created a Module and run! It has created a new column named Next_of_Kin as you have stipulated, But if you run the Code against Query it gives error!
"Cannot execute data definition Statements on linked data structure"
How to get rid of this error?
Dec 8 '10 #4
I have tried to past the code [ALTER TABLE Employees ADD COLUMN Next_Of_Kin TEXT(25)]on the data definition of the query DesignView but gives the same error as above and it works for tables as well!
Dec 8 '10 #5
Mr Key
132 100+
You may try to create table from query first and then insert a new column or field as you wish
Dec 9 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Larry R Harrison Jr | last post: by
1 post views Thread by johnb41 | last post: by
reply views Thread by Toni Pohl | last post: by
7 posts views Thread by BillCo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.