EDIT SQL without going into design view first?

[S Davis]

Is there any way in Access (2003) to edit a query's SQL statement
without first going into design view?

Some linked tables on our main database have inherited more than the
maximum number of indexes that Access can support, and as a result
pretty much any query that references these linked tables has to be
reanalysed any time the query is opened for editing. The friendly
database folk have created a View for me that I can link to instead of
the troubled tables, but I still need to push this into my Access
queries, and opening them in design view now takes AGES. Unfortunately
for me, the only way I know how to get to SQL view is to go through
the Design view, which really doesn't help me

So, is there a shortcut? Thanks!

 

[S Davis]

Is there any way in Access (2003) to edit a query's SQL statement
without first going into design view?

Some linked tables on our main database have inherited more than the
maximum number of indexes that Access can support, and as a result
pretty much any query that references these linked tables has to be
reanalysed any time the query is opened for editing. The friendly
database folk have created a View for me that I can link to instead of
the troubled tables, but I still need to push this into my Access
queries, and opening them in design view now takes AGES. Unfortunately
for me, the only way I know how to get to SQL view is to go through
the Design view, which really doesn't help me

So, is there a shortcut? Thanks!

2 minutes later I've found a solution that will work for me, but I am
still very curious about the answer to my question above.

(My solution was to create a new blank database and import all the
queries into that database... since there are no tables or linked
tables, it opens instantly and I can edit the sql from there, then
export it back into my main Access database)

 

[John Spencer]

Well, you could use a form to get the SQL statement and put it into a
control, edit the statement there and then modify the query SQL.

All you would need is a list of the saved queries which you should be able
to get from the system tables. And the code to populate a control with the
SQL of the query and then code to write the modified SQL back to the saved
query.

I see no reason that all that couldn't be done with some VBA and an unbound
form.

I would probably create a method to test the query also - perhaps using a
dummy query created just for the purpose of testing.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..