Query won't design or go into SQL

[Steve@trop]

I'm reverse-engineering an old Access database file I inherited. I'm basically just looking at the various components and trying to figure out what they do and how they do it. I've come across a couple queries that run fine (as far as I can tell) but won't let me go into design view to look at how they were built. Each time I try I get an error message that says there is a syntax error. That wouldn't bother me if I could go in and fix it but it won't let me!

So I can't open it in design view until I fix the error but I can't fix the error if I can't get into design view! Has anybody come across this chicken and egg problem before? Has anybody figured out how to get around it? I'd be fine with it if I could just get it into SQL view but I don't know how to go straight to that. Usually, if you try to open a query in design view and it can't open in that view it will open in SQL view instead but that's not happening in this case.

I think it might have something to do with the fact that it's an MDB file and I'm running Access 2010.

Thanks,

Steve

 

[sxschech]

You can extract the sql statement in vba immediate window. Ctrl+G will open the immediate window. in the code below, replace MyQuery with the name of the query you want to review.

set qd=CurrentDb.QueryDefs("MyQuery")
? qd.sql

 

[jleach]

There's also the undocumented SaveAsText function, but I'd prefer the SQL property approach as well.

Sometimes you have queries that are too complex to be correctly displayed by the designer, and will only open in SQL View.

In any case, once you get the SQL itself you can throw it in some other editor, work it up and update the .sql property of a querydef via more or less the same means you used to get it.

 

[Steve@trop]

Thanks! That worked great! I knew there had to be a way to get the SQL of the query.

Now I just have to figure out where the error is in this one and the other one that has the same problem.

 

[jdraw]

Perhaps you could post the sql.

 

[isladogs]

You might find this SQL to VBA converter helpful
https://www.access-programmers.co.uk/forums/showthread.php?t=293372

It also allows you to edit the SQL then create a new query in design view so you can test your changes