Improve company productivity with a Business Account.Sign Up


alter more than one field at once access 2007

Posted on 2011-02-21
Medium Priority
Last Modified: 2012-06-27
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
Question by:drtopserv
LVL 77

Expert Comment

ID: 34942178
Don't really see the issue here.
What is stopping you changing the datatype?

Accepted Solution

jaiminpsoni earned 500 total points
ID: 34942261
You can not do it in a single statement, you need to run multiple statements...

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <Datatype>
LVL 77

Expert Comment

ID: 34942293
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.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!


Author Comment

ID: 34942301
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>
LVL 86

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34942376
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> "

Expert Comment

ID: 34942415
See if this helps....

Look at section....

Creating a Pass-Through Query and Creating a Data-Definition Query

See if that helps...

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
Are you working to mount the dismounted Exchange 2013 database? Then the best course of action is to analyze the causes of Database issue, their probable solutions and decide for the appropriate course of action.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question