Editing queries in SQL view

[yvette]

I'm updating an Access database with many existing queries and was wondering
if there is any way I can view ALL the queries at once in a SQL view? I
think it would be much easier than clicking on each Access query one-by-one
to edit.

Thanks,
Yvette

 

[Marshall Barton]

I'm updating an Access database with many existing queries and was wondering
if there is any way I can view ALL the queries at once in a SQL view? I
think it would be much easier than clicking on each Access query one-by-one
to edit.

You could write some code to extract each query's Name and
SQL properties and put them in a temp table. Something like
this air code should give you the idea:

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef

Set db = CurrentDb
Set rs =db.OpenRecordset("temptable")

For Each qdf In db.QueryDefs
If Left(qdf.Name) <> "~" Then
rs.AddNew
rs!QueryName = qdf.Name
rs!SQL = wdf.SQL
rs.Update
End If
Next qdf

rs.Close : Set rs = Nothing
Set db = Nothing

Then you could use that table as a form and/or report's
record source. After you finish editing a query on the
form, you can use the sql text box's AfterUpdate event to
reset the query's SQL property.

 

[John W. Vinson]

I'm updating an Access database with many existing queries and was wondering
if there is any way I can view ALL the queries at once in a SQL view? I
think it would be much easier than clicking on each Access query one-by-one
to edit.

Thanks,
Yvette

I don't know any way to do that, but you might want to consider one of the
third-party search and replace tools:

Free: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

Last I checked Speed Ferret (otherwise my favorite) was available only up
through Access2002.