Editing underlying SQL in queries

[Guest]

Hi all,

this may be a stupid query (sorry... couldn't resist!). How does one get
to the underlying SQL in a query without having to pass through design view?


I have some old queries referring to renamed tables and I don't particularly
want to have to recreate the queries if I can simply edit the table names in
the SQL. Opening the queries in Design View destroys the joins and renames
the fields - an irritation...

One option is to use VB to print/display the SQL but I'm hoping there may be
a simpler method.

Thanks!
Zanuck
So many places, so little time.

 

[Duane Hookom]

You can try open the debug window and enter something like:
? currentdb.QueryDefs("qselMyQuery").SQL
Then take the result into Word and use search and replace on table names.
Then go back to the query design/SQL view and paste in the SQL from Word.

 

[Guest]

dim qdf, ssql,db
set db = currentdb

for each qdf in db.querydefs
ssql = qdf.sql
ssql = replace(ssql,"oldtable","newtable")
qdf.sql = ssql
next qdf

I have some old queries referring to renamed tables and I don't particularly
want to have to recreate the queries if I can simply edit the table names in
the SQL. Opening the queries in Design View destroys the joins and renames
the fields - an irritation...

For backward compatibility, create a new query with the old table name.
If the tables are linked tables, create a duplicate link with the old table
name.

(david)

 

[Guest]

Thanks Duane... a nice quick 'n dirty way to do a single query.
Z

 

[Guest]

Thanks David - I'll probably end up using the code for a related problem I
didn't raise.

Thanks!
Z