Avatar of siva_gk
siva_gk asked on

add column to access table

i am having a table in MS Access as USER_MASTER with fields
1. USER_ID
2. USER_NAME
3. USER_PASSWORD
now I want to add a column USER_INFO in position 3 through a query statement.
ALTER TABLE USER_MASTER ADD USER_INFO VARCHAR(250) <in position 3>

how can it be possible?
Microsoft AccessVisual Basic ClassicSQL

Avatar of undefined
Last Comment
MikeToole

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
MikeToole

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
James Murrell

do it in design table in access?
ASKER
siva_gk

yeah i can do it in design. but my requirement is like that. i am adding a column through VB program by passing the statement.

anyway now i will go for droping and creating a new one and copying the records...

but anybody aware of doing this in ALTER TABLE command kindly let me know....
SOLUTION
Arthur_Wood

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
siva_gk

Mike I passed the statement CREATE TABLE USER_MASTER_OLD AS SELECT * FROM USER_MASTER which is giving error (syntax error in CREATE TABLE statement)

how to create table as like existing one in Ms Access?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
siva_gk

yeah thats right Arthur...
Dana Seaman

Try this to see if it adds a new column. Assumes your Connection is Conn, change acordingly:

Conn.Execute "ALTER TABLE USER_MASTER ADD COLUMN USER_INFO TEXT(30)",,129
James Murrell

personally i would use the ALTER TABLE USER_MASTER ADD COLUMN USER_INFO TEXT(25);

and then use a view with the correct postioning
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
MikeToole

Is there a particular reason for wanting the columns in this order? SQL is neutral about column order - it doesn't have any design or performance impact.
You can create the new table and copy data in one step:

DoCmd.RunSQL "Select  User_ID, User_Name, "" as User_INFO, User_Password INTO tblNew From tblOld"
MikeToole

siva_gk
<CREATE TABLE USER_MASTER_OLD AS SELECT * FROM USER_MASTER which is giving error >
As in my last post, use Select ... INTO ... FROM
Mike