Programatically Checking Queries

[dandoescode]

My objective is to check a list of queries, about 350 and determine which of them are functioning, rather than go and copy and paste each one I've written some vba which I think should do the job, unfortunately it currently isnt.

The basic idea is to create a field called working, in a table which contains the sql I'm running as a string. Open this table as a recordset, and run each line using the Docmd.RunSql statement. If the statement fails it should generate an error, then by making my error handler set working to false I can determine which queries are functioining.

The issue I havent been able to figure out yet is how I can check queries which aren't action queries. I thought that DoCmd.OpenSQL should work for this but I've had no luck thus far. Here is what I have.

Option Compare Database

Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim strSql As String
Dim db As DAO.Database
Dim ID As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("dan_test")

'Turn off annoying warnings and set the working variable to true
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE dan_test set Working = True  ; ")

'Try the first case, if it succeeds do nothing, if it fails go to error handler
rst.MoveFirst


'Check the remaining cases, if it works do nothing, if it fails go to error handler
Do While Not rst.EOF
    strSql = (rst.Fields("strNewValue"))

'If code is a select statement it should be treated differently
If Left(strSql, 6) = "select" Then
    DoCmd.OpenQuery strSql
Else
    DoCmd.RunSQL (strSql)
End If
    On Error GoTo Err:
    rst.MoveNext
Loop

MsgBox "DONE"

Exit Sub

'Error Handler updates the table changing the working field to false, and prints the Id number to verify accuracy
Err:
Debug.Print Error$(Err) & crlf & "ROW:" & rst!tblChangesID
rst.Edit
rst!working = False
rst!strNote = Error$(Err)
rst.Update
Err.Clear
Resume Next

Set rst = Nothing
DoCmd.SetWarnings True

End Sub

[dandoescode]

For instance

 SELECT tblPersonnelID, strFullName FROM qryPersonnelMaster ORDER BY txtLastName, txtFirstName;

gives

 Microsoft Access can't find the object 'SELECT tblPersonnelID, strFullName FROM qryPersonnelMaster ORDER BY txtLastName, txtFirstName;'.

[Rawb]

The best way to deal with this is to create a Recordset variable. If there's an error in the SQL syntax, you'll get an error when you try to Set the Recordset. And, if there aren't any syntax errors, you can easily check if the SQL Query returned any Records by using the .RecordCount property. . .

  On Error GoTo Error_Clause

  Dim rst as DAO.Recordset

  Set rst = CurrentDb().OpenRecordset("SELECT tblPersonnelID, strFullName FROM qryPersonnelMaster ORDER BY txtLastName, txtFirstName", dbOpenDynaset)

  If rst.RecordCount = 0 Then
    ' No Records!
  Else
    ' Records!
  End If

Function_Closing:
  rst.Close

  Set rst = Nothing

  Exit Sub

Error_Clause:
  ' Do your error stuff here

  Resume Function_Closing

[help_me_with_access]

The best way to deal with this is to create a Recordset variable. If there's an error in the SQL syntax, you'll get an error when you try to Set the Recordset. And, if there aren't any syntax errors, you can easily check if the SQL Query returned any Records by using the .RecordCount property. . .

wellll...I'm not really sure if this is right. way to much un-necessary stuff it seems like. but Dan, what are we doing again? you say you want to check to see if they're working before you "copy them over"? copy to where?

working HOW? based on the talk so far, I'm guessing you mean they work in terms in that the syntax takes with the engine. YES?

so what's the copying all about again?

by the way, there's also an object dependency checker in access 2010. in the ribbon somewhere. try it and see what it says. see if you get any query names under the "unsupported objects" section.

also, the enumerations for the query types are listed in help. .TYPE property of a querydef returns them.... I think they also have alpha representations to them that you can check as returns. so e.g.=>

dim q a querydef
set q = database.querydefs("queryname")
if q.type = vbAction then
   goto errHandler 'this type not checked
end if

if that doesn't work, you'll have to use the integers.

[Rawb]

It sounds like he's storing SQL Queries in a Table. Then he loads up the Table and runs the Queries in it one at a time and - if the Query returns an error, he "flags" that one for review.

That said, what do you consider an error condition for non-action Queries? Is returning 0 Records an error? Or is this only for Syntax checking (and we don't care about what the results are)?

[help_me_with_access]

That said, what do you consider an error condition for non-action Queries? Is returning 0 Records an error? Or is this only for Syntax checking (and we don't care about what the results are)?

see this is why I don't get this. 1 => I want an answer to the "copy to" issue. more than 1 mdb in the situation here? and I've never even heard of anything doing this either. why would you have to run a check on queries? To be honest, even a mediocre data modeling effort should eliminate the need for this kind of work anyway.

but there are all kinds. last vb6 project I worked on, they had no data model at all, but yet we never did anything like this and the program was like 200K LOC using probably 300+ queries.

[Rawb]

He's not doing any copying.

What he said is that he didn't want to have to manually copy and paste each Query into the VBA code (thus "hard-coding" them into the program). That's why he's trying to set up this VBA code that would allow him to add/edit/delete SQL Queries from the Table whenever he wanted without having to change the VBA code.

At least, that's my understanding. It's possible I'm just putting words in his mouth.

EDIT: That said, I agree that, if he has a saved Query in the database already, this entire setup is somewhat unnecessary. Like you said, it's possible to just loop through each QueryDef Object (saved Query) and run it. Then, the ones that don't work, he could report (along with the error number and message) in a MsgBox, Debug line, or write out to a file.

There's no need to have duplicate copies of each Query in the database (assuming the Queries he wants to check are actually saved Query Objects in Access).

[help_me_with_access]

got it. I'm out! good luck with the solution there, Rawb.

[Rawb]

Well, I've done a little poking around and I think the following VBA code will work for you. The only non-stylistic changes I made were:

• I moved the "On Error GoTo" statement to the top of the Function.
• I removed the colon ":" from the end of your "On Error GoTo" statement. The colon should only be used on the label itself, not on the "definition" statement.
• I set DoCmd.SetWarnings back to True at the end of the Function to prevent any wierdness in the rest of the database.

Option Compare Database
Option Explicit

Private Sub Command0_Click()
  On Error GoTo Error_Command0_Click
  DoCmd.SetWarnings False

  Dim db As DAO.Database
  Dim rst As DAO.Recordset

  Dim nbrErrorCount As Long

  Set db = CurrentDb()
  Set rst = db.OpenRecordset("dan_test", dbOpenDynaset, dbSeeChanges)

  nbrErrorCount = 0

  If rst.RecordCount = 0 Then
    MsgBox "No Queries to run!"
  Else
    Do While Not rst.EOF
      With rst
        .Edit
        !working = True
        !strNote = ""
        .Update
      End With

      rst.MoveNext
    Loop

    rst.MoveFirst

Return_From_Error:
    Do While Not rst.EOF
      If UCase(Left(Trim(rst("strNewValue")), 6)) = "SELECT" Then
        DoCmd.OpenQuery rst("strNewValue")
      Else
        DoCmd.RunSQL rst("strNewValue")
      End If

      rst.MoveNext
    Loop
  End If

  DoCmd.SetWarnings True

  If nbrErrorCount = 0 Then
    MsgBox "Done!"
  Else
    MsgBox nbrErrorCount & " errors were encountered. Please check the flagged Queries for more details."
  End If

  rst.Close

  Set rst = Nothing

  Exit Sub

Error_Command0_Click:
  nbrErrorCount = nbrErrorCount + 1

  With rst
    .Edit
    !working = False
    !strNote = Err.Number & ": " & Err.Description
    .Update
  End With

  Err.Clear

  rst.MoveNext

  Resume Return_From_Error
End Sub