Access 2007: SQL Syntax error for Alter Table query

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Pratima

sorry my bad , not refersh the page befor posting :)

ASKER

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?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Raj, yes the statement works on its own.

Then can you try by execute each statement one-by-one ?

Raj

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

ASKER

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

ASKER

Sorry the Go command doesn't seem to work, cool trick though if I ever start using SQL Server.

I believe ms access not supports executing multiple queries at once.
Raj

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

ASKER

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

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

I meant what pratima_mcs mentioned above.

Raj