Force existing queries to open in SQL view

Here is the dilema:

I have copied a bunch of queries using a chunk of code.  All the copied queries want to open in design view.  This is no good because it damages the joins when it can't display them.

Can I write code to se all existing queries to open to SQL view so that my joins don't get damaged?

Thanks,

R.
LVL 1
ryanstarretAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
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.
0
rockiroadsCommented:
there is DoCmd.OpenQuery, but there seems to be no option to open in SQL View.
You may just well be stuck.
0
nico5038Commented:
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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GRayLCommented:
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?
0
Rick_RickardsCommented:
There is a solution to your dilemma if you're willing to build a simple form to get around this annoying limitation of Access.

It is true that if you open a query in design view and one of the tables referenced in the SQL is missing and/or the parent and/or foreign key is missing that the links will get broken as the query opens.  To get around this we will need to build a simple form with a list box displaying all of your queries.  Once done we'll put some code behind the list boxes double click event so that any time you double click one of the queries listed it will open the query and display it's original SQL code regardless of whether the tables, keys or anything else required for the query is still there.  This is how you do it....

1) Create a new form (call it anything you like)
2) Add a list box to the form named lstQueries
3) Set the rowsource for the form as follows...
       SELECT MSysObjects.Name AS Queries FROM MSysObjects WHERE  MSysObjects.Type=5 ORDER BY MSysObjects.Name;
4) Add the following code to the double click event of the list box "lstQueries"

Private Sub lstQueries_DblClick(Cancel As Integer)
    Dim strQuery As String
    Dim strSQL As String
    Dim lngCount As Long
    Dim strChr As String    
    strQuery = Me.lstQueries.Value
    If Len(strQuery) Then
        strSQL = CurrentDb.QueryDefs(strQuery).SQL
        DoCmd.OpenQuery strQuery, acViewDesign
        DoCmd.RunCommand acCmdSQLView        

        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
    End If
End Sub

'That's all there is to it.  In truth the query still breaks when it is opened but we get around this by storing the SQL string of the query before it is opened, then we change the view to SQL view.  We then use Send Keys to retype the query as it was originally.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
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°)
0
ryanstarretAuthor Commented:
Rick,

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

R.
0
ryanstarretAuthor Commented:
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
0
ryanstarretAuthor Commented:
OK... well.  Sendkeys just isn't sending the keystrokes to that window unfortunately... I'm not really sure what's going on.
0
ryanstarretAuthor Commented:
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.
0
harfangCommented:
Welcome, although you would have saved time by trying {http:#16786321} as well...
Good luck!
(°v°)
0
ryanstarretAuthor Commented:
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!)
0
harfangCommented:
No problem. Good luck with you future plans!
(°v°)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.