January 13, 2008 at 9:14 pm
This is my query.. I need to alter all three tables in the same query. Why does it give me a syntax error? If I use each query and execute it separately, it works fine. Can anyone help me?
ALTER TABLE Campaigns ADD COLUMN ExportDate DATETIME;
ALTER TABLE Locations ADD COLUMN ExportDate DATETIME;
ALTER TABLE Survey ADD COLUMN ExportDate DATETIME;
January 15, 2008 at 1:27 am
Hi, do you open a connection and execute it? Such as
cnn1.open "Provider=Microsoft.Jet.OLEDB......
cnn1.execute "ALTER TABLE etc.....
January 15, 2008 at 2:55 am
Good question - how are you executing the command? I don't think you can use Access DoCmd.RunSQL instruction with Alter Table.
January 15, 2008 at 9:14 pm
Its a query in Access SQL View!
January 16, 2008 at 1:22 am
I don't think you can cram multiple ALTER TABLE statements in the same SQL view, you have to prepare one query for each one.
January 16, 2008 at 4:05 am
That sounds right to me. I have only ever seen one sql statement inside an individual Access query. I have never seen three individual sql statements chained together, delimited by semi colons, in one Access query before.
I guess if you really want to do this using Access queries then you could save them as three queries and execute them one after another.
January 16, 2008 at 4:13 am
Oh! Ok! I don't have much experience with Access.. so I thought I was probably doing something wrong!
Thanks, guys!
January 25, 2008 at 9:30 am
In Access you can only have one statement per query. The way you run multiple statements is to create one query for each statement and then create a macro where each step (action) is one of those queries.
Steve
January 25, 2008 at 9:53 am
Of course - if these are SQL tables linked in, you can't alter them at all from within Access. The most you could do is to "ask SQL Server" to alter them for you, through the use of a stored procedure or a pass-through query.
Again if this is the case, once you alter them you will need to drop and relink the linked table definitions since they don't automatically refresh on DDL changes on the backend.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply