Force existing queries to open in SQL view

Ive not come across any, the only time Ive seen it go straight into sql view is if it has a union statement.
Its most annoying, access loves those crappy round brackets that are placed everywhere.

there is DoCmd.OpenQuery, but there seems to be no option to open in SQL View.
You may just well be stuck.

You can usecode to build a table with the queryname and the SQL text and view that from a form.

To create the table use e.g. a function like:

Function fncListQueries()

dim qd as DAO.querydef

for each qd in currentdb.querydefs
     if left(qd.name,1) <>"~" then
        currentdb.execute ("insert into tblQueries (QueryName, QuerySQL) Values ('" & qd.name & "'," & chr(34) & qd.SQL & chr(34) & ")")
     endif
next

QueryName will have to be a text field and QuerySQL a Memo field in table tblQueries.
Getting the idea ?

Nic;o)

I'm running A2K and found that when I saved the query from the SQL View, it would open in the SQL View.  Does that work for you?

Hello ryanstarret

I had this problem before, but could not find how and where Access stores the default view for queries. However, saving a query in SQL view is retained (as Ray already mentioned), so that this worked for me:

Sub OpenSQLView(pstrQuery As String)

    Dim strSQL As String
   
    strSQL = CurrentDb.QueryDefs(pstrQuery).SQL
    DoCmd.OpenQuery pstrQuery, acViewDesign
    RunCommand acCmdSQLView
    DoCmd.Save acQuery, pstrQuery
    DoCmd.Close acQuery, pstrQuery, acSaveNo
    CurrentDb.QueryDefs(pstrQuery).SQL = strSQL
    DoCmd.OpenQuery pstrQuery, acViewDesign

End Sub

Much like in Rick's solution, the original SQL string is preserved and written back into the query once it has been saved from the SQL view.

WARNING: the above actually saves the queries in their broken state (after the links removal), so make sure you have a backup.

Cheers!
(°v°)

Rick,

I"ve modified your code a bit... but for some reason sendkeys fails on a (... not exactly sure why.

R.

Sorry... meant to paste the code:

Sub ReOpenQueries()
    Dim strQuery As String
    Dim strSQL As String
    Dim lngCount As Long
    Dim strChr As String
    Dim qdf As QueryDef
   
    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 3) <> "~sq" Then
            strQuery = qdf.Name
            If Len(qdf.Name) Then
                strSQL = qdf.sql
                DoCmd.OpenQuery qdf.Name, acViewDesign
                DoCmd.RunCommand acCmdSQLView
                strSQL = Replace(strSQL, "[SELECT", "(SELECT")
                strSQL = Replace(strSQL, "].", ")")
                For lngCount = 1 To Len(strSQL)
                    strChr = Mid(strSQL, lngCount, 1)
                    Select Case Asc(strChr)
                    Case 10
                        'Do Nothing (Line feeds will produce double carriage return
                    Case Else
                        Debug.Print Asc(strChr)
                        SendKeys strChr
                    End Select
                Next lngCount
                DoCmd.Close acQuery, qdf.Name, acSaveYes
            End If
        End If
    Next
End Sub

OK... well.  Sendkeys just isn't sending the keystrokes to that window unfortunately... I'm not really sure what's going on.

Ok... got the problem solved... and here is the solution:

Private Sub cmdGo_Click()
   
    Dim strQuery As String
    Dim strSQL As String
    Dim lngCount As Long
    Dim strChr As String
    Dim qdf As QueryDef
    Dim Start As Long
    Dim PauseTime As Long

    If IsNull(cboQueryNames) Or cboQueryNames.Value = vbNullString Then Exit Sub

    MsgBox "Do not touch computer until this task completes!", vbExclamation, "Rexdale Loss Database"
   
    strQuery = cboQueryNames
    If Len(strQuery) Then
        strSQL = CurrentDb.QueryDefs(strQuery).sql
        DoCmd.OpenQuery strQuery, acViewDesign
        DoEvents
        DoCmd.RunCommand acCmdSQLView
        DoEvents
        strSQL = Replace(strSQL, "[SELECT", "(SELECT")
        strSQL = Replace(strSQL, "].", ")")
       
        'Insert a pause
        PauseTime = 20
        Start = Timer
        Do While Timer < Start + PauseTime
           DoEvents
        Loop
       
        'Copy the correct code
        For lngCount = 1 To Len(strSQL)
            DoEvents
            strChr = Mid(strSQL, lngCount, 1)
            Select Case Asc(strChr)
            Case 10
                'Do Nothing (Line feeds will produce double carriage return)
            Case Else
                Debug.Print Asc(strChr)
                If strChr = "(" Or strChr = ")" Or _
                        strChr = "+" Or strChr = "^" Or _
                        strChr = "%" Or strChr = "~" Or _
                        strChr = "[" Or strChr = "]" Or _
                        strChr = "{" Or strChr = "}" Then
                    strChr = "{" & strChr & "}"
                End If
                SendKeys strChr
            End Select
        Next lngCount
        DoEvents
        DoCmd.Close acQuery, strQuery, acSaveYes
    End If
End Sub


The delay in there was necessary because the queries are slow to open sometimes.

Thanks for the help.

Welcome, although you would have saved time by trying {http:#16786321} as well...
Good luck!
(°v°)

sorry.. I guess I was confused.  I got to the end where it said something about saving the queries in their broken state and I think I just skipped it.  The other solution worked out well though.  Made a reasonable form for the person to deal with after I leave this place (in two days!)

No problem. Good luck with you future plans!
(°v°)