• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

alter more than one field at once access 2007

Hi,
how can i alter more than one field in access 2007
 i need to change a fields in a table with number instead of text

example :
table1 , field1:TEXT filed2:TEXT
i need to alter table 1 changing field1 to NUMBER and field2 to NUMBER
0
drtopserv
Asked:
drtopserv
2 Solutions
 
peter57rCommented:
Don't really see the issue here.
What is stopping you changing the datatype?
0
 
jaiminpsoniCommented:
You can not do it in a single statement, you need to run multiple statements...

http://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <Datatype>
0
 
peter57rCommented:
I didn't see anything in the Q that says this must be done in code - is that what you meant drtopserv?
To me, it just looked like  case of opening the table in design view and doing the changes.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
drtopservAuthor Commented:
in data definition in the query ... should i write >? :
ALTER TABLE <TableName1> ALTER COLUMN <ColumnName1> <Datatype>
ALTER TABLE <TableName2> ALTER COLUMN <ColumnName2> <Datatype>
ALTER TABLE <TableName3> ALTER COLUMN <ColumnName3> <Datatype>
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To expand a bit on what jaiminpsoni wrote:

You'd have to issue individaul commands for EACH DDL query. You can't lump them together the way you can with other databses:

CurrentProject.Connection.Execute "ALTER TABLE <TableName1> ALTER COLUMN <ColumnName1> <Datatype> "
CurrentProject.Connection.Execute "ALTER TABLE <TableName2> ALTER COLUMN <ColumnName2> <Datatype> "
CurrentProject.Connection.Execute  "ALTER TABLE <TableName3> ALTER COLUMN <ColumnName3> <Datatype> "
0
 
jaiminpsoniCommented:
See if this helps....

http://www.ehow.com/how_13580_create-sql-query.html

Look at section....

Creating a Pass-Through Query and Creating a Data-Definition Query
http://www.ehow.com/how_13580_create-sql-query.html#ixzz1Eb76s3kR

See if that helps...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now