Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 627
  • Last Modified:

Access 2007: SQL Syntax error for Alter Table query

Hello, I am working in Access and want to use the query here to modify the contents of several tables.  The syntax seems to be a bit off and I was hoping someone here could help.  After the first line of code I get an error 'Syntax error in ALTER TABLE statement'.

Any help would be greatly appreciated!

Thanks so much in advance,
Bevo
ALTER TABLE tblPtCharacteristics  ALTER COLUMN PtCharacteristics_ID COUNTER (100000, 1)
ALTER TABLE tblTreatmentCharacteristics  ALTER COLUMN Group_ID COUNTER (100000, 1)
ALTER TABLE tblIntermediateOutcome  ALTER COLUMN Intermediate_ID COUNTER (100000, 1)
ALTER TABLE tblHealthOutcomeMorbidity  ALTER COLUMN Morbidity_ID COUNTER (100000, 1)
ALTER TABLE tblAdverseEvents  ALTER COLUMN AE_ID COUNTER (100000, 1)
ALTER TABLE tblHealthOutcomeMortality  ALTER COLUMN Mortality_ID COUNTER (100000, 1)
ALTER TABLE tblHealthOutcomeFunctionQOL  ALTER COLUMN Function_ID COUNTER (100000, 1)
ALTER TABLE tblQuality  ALTER COLUMN Quality_ID COUNTER (100000, 1)

Open in new window

0
Bevos
Asked:
Bevos
  • 7
  • 6
  • 3
4 Solutions
 
Rajkumar GsSoftware EngineerCommented:
Put a semi-colon at the end and try.
ALTER TABLE tblPtCharacteristics  ALTER COLUMN PtCharacteristics_ID COUNTER (100000, 1);
ALTER TABLE tblTreatmentCharacteristics  ALTER COLUMN Group_ID COUNTER (100000, 1);
ALTER TABLE tblIntermediateOutcome  ALTER COLUMN Intermediate_ID COUNTER (100000, 1);
ALTER TABLE tblHealthOutcomeMorbidity  ALTER COLUMN Morbidity_ID COUNTER (100000, 1);
ALTER TABLE tblAdverseEvents  ALTER COLUMN AE_ID COUNTER (100000, 1);
ALTER TABLE tblHealthOutcomeMortality  ALTER COLUMN Mortality_ID COUNTER (100000, 1);
ALTER TABLE tblHealthOutcomeFunctionQOL  ALTER COLUMN Function_ID COUNTER (100000, 1);
ALTER TABLE tblQuality  ALTER COLUMN Quality_ID COUNTER (100000, 1);

Open in new window


I am more experienced in SQL Server. Just give it a try
Raj
0
 
Pratima PharandeCommented:
syntax looks correct
add semicolon and try

 ALTER TABLE tblPtCharacteristics  ALTER COLUMN PtCharacteristics_ID COUNTER (100000, 1);
ALTER TABLE tblTreatmentCharacteristics  ALTER COLUMN Group_ID COUNTER (100000, 1);
ALTER TABLE tblIntermediateOutcome  ALTER COLUMN Intermediate_ID COUNTER (100000, 1);
ALTER TABLE tblHealthOutcomeMorbidity  ALTER COLUMN Morbidity_ID COUNTER (100000, 1);
ALTER TABLE tblAdverseEvents  ALTER COLUMN AE_ID COUNTER (100000, 1);
ALTER TABLE tblHealthOutcomeMortality  ALTER COLUMN Mortality_ID COUNTER (100000, 1);
ALTER TABLE tblHealthOutcomeFunctionQOL  ALTER COLUMN Function_ID COUNTER (100000, 1);
ALTER TABLE tblQuality  ALTER COLUMN Quality_ID COUNTER (100000, 1) ;
0
 
Pratima PharandeCommented:
sorry my bad , not refersh the page befor posting :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BevosAuthor Commented:
Hi, I tried this with the semi colon but it still will not work.  The syntax works fine for a single alter table entry, but afterwards the error prompt points the the second Alter statement.

Anyone have an idea?
0
 
Rajkumar GsSoftware EngineerCommented:
When you execute the second statement alone, is it working ?
ALTER TABLE tblTreatmentCharacteristics  ALTER COLUMN Group_ID COUNTER (100000, 1);

Open in new window


Raj
0
 
BevosAuthor Commented:
Hi Raj, yes the statement works on its own.
0
 
Rajkumar GsSoftware EngineerCommented:
Then can you try by execute each statement one-by-one ?

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
If it is SQL Server, you may try this way. Not sure whether MS Access 2007 supports it
ALTER TABLE tblPtCharacteristics  ALTER COLUMN PtCharacteristics_ID COUNTER (100000, 1);
GO
ALTER TABLE tblTreatmentCharacteristics  ALTER COLUMN Group_ID COUNTER (100000, 1);
GO
ALTER TABLE tblIntermediateOutcome  ALTER COLUMN Intermediate_ID COUNTER (100000, 1);
GO
ALTER TABLE tblHealthOutcomeMorbidity  ALTER COLUMN Morbidity_ID COUNTER (100000, 1);
GO
ALTER TABLE tblAdverseEvents  ALTER COLUMN AE_ID COUNTER (100000, 1);
GO
ALTER TABLE tblHealthOutcomeMortality  ALTER COLUMN Mortality_ID COUNTER (100000, 1);
GO
ALTER TABLE tblHealthOutcomeFunctionQOL  ALTER COLUMN Function_ID COUNTER (100000, 1);
GO
ALTER TABLE tblQuality  ALTER COLUMN Quality_ID COUNTER (100000, 1);
GO

Open in new window

Anyway you can try by executing each statement one-by-one, I believe it works

Raj
0
 
BevosAuthor Commented:
Yes, this process works.  Can I not have multiple Alter Table statements in the same query?  I have to run this on several databases and I'd rather not have eight queries.

Thanks again,
Bevo
0
 
BevosAuthor Commented:
Sorry the Go command doesn't seem to work, cool trick though if I ever start using SQL Server.
0
 
Rajkumar GsSoftware EngineerCommented:
I believe ms access not supports executing multiple queries at once.
Raj
0
 
Rajkumar GsSoftware EngineerCommented:
If my above statement is right, right option is to prepare the update queries that need to be execute and execute it to ms access 2007 db one-by-one in a loop.

Raj
0
 
BevosAuthor Commented:
So you're saying make 8 queries and then have a loop execute each query?  How would I do that if you don't mind me asking?

Thanks,
Bevo
0
 
Pratima PharandeCommented:
You can't put multiple SQL statements under one named query (with the exception of UNION queries).

If you need to run several queries in sequence, then run them thru VBA code. Either create named queries and run them using the DoCmd.OpenQuery method, or generate the code within the VBA module and run using CurrentDB.Execute.
0
 
Rajkumar GsSoftware EngineerCommented:
I meant what pratima_mcs mentioned above.

Raj
0
 
BevosAuthor Commented:
Thank you everyone who helped with this question!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now