Executing queries using DAO

Question

I want to execute a list of queries against an Access database using DAO. The "Database.Execute()" method seems suited for this with the remark that it can only execute "action queries", that is queries which don't return a result set (MSDN reference). For queries which return records "Database.OpenRecordset()" can be used. Both methods throw exceptions if the wrong type of query is passed.

Having both action queries and select queries in the list how can I decide upfront which will return records and which will not?

Answer 1

This information applies to the type of the query. So:

• All queries that execute a SELECT ... FROM ... are select queries
• All INSERT, UPDATE, DELETE are action queries

You just have to inspect the query sql command text to look if it starts with any of the above keywords and act accordingly.

Answer 2

Note that the ADO Execute method can be used regardless of whether or not the query returns a resultset.

But do you really want to execute all 'queries'? What if they contain SQL DDL? Consider you created a PROCEDURE using this SQL DDL code:

CREATE PROCEDURE qryDropMe AS DROP PROCEDURE qryDropMe;

;)

Answer 3

Access maintains a hidden system table called MSysObjects which includes a field (Flags) which stores a value indicating the query type. You could try the following function with each of the query names from your list and use the return value to determine whether to use Database.Execute() or Database.OpenRecordset()

The function requires read permission for MSysObjects. I have heard reports than some Access 2007 users are denied permission to read MSysObjects. However, I haven't encountered that problem with Access 2007.

I tested several query types to determine the Flags values. If one of your queries is a type I didn't test, the function will return the Flags value as unrecognized. You can modify the function to include that Flags type.

The only DDL query I tested was DROP TABLE (Flags = 96).

Also, please be aware that not all "SELECT ... FROM ..." queries are select queries for your purpose (returning a recordset). A query such as "SELECT fields INTO newtable FROM oldtable;" does not return records, and the Access UI classifies it as a Make Table query.

Public Function QueryType(ByVal pQueryName As String) As String
    Dim lngFlags As Long
    Dim strType As String
    Dim strCriteria As String

    strCriteria = "[Name] = """ & pQueryName & """ And [Type] = 5"
    lngFlags = DLookup("Flags", "MSysObjects", strCriteria)

    Select Case lngFlags
    Case 0
        strType = "Select"
    Case 16
        strType = "Crosstab"
    Case 32
        strType = "Delete"
    Case 48
        strType = "Update"
    Case 64
        strType = "Append"
    Case 80
        strType = "Make Table"
    Case 96
        strType = "Drop Table"
    Case 128
        strType = "Union"
    Case Else
        strType = "Flags " & CStr(lngFlags) & " unrecognized"
    End Select

    QueryType = strType
End Function

Answer 4

Why don't you catch the exception thrown and analyse it?

Do you have any way to use a beginTrans/Rollback instruction? You could then send your SQL command, collect the errors, then rollback your transactions, having your database left unchanged.

What about using ADO connection, somewhat smarter than the ADO one, where the connection hold an 'errors' collection and returns some other data like number of records affected?

Answer 5

Inspired by @HansUp's answer I investigated a bit more the QueryDef structure provided by the DAO interface. The structure has a "Type" property which I can use to differentiate between different query types (MSDN). I ended up with the following implementation:

function TAccessDatabase.SQLExec(AName, AQuery: String): Integer;
var
  I: Integer;
  QDef: QueryDef;
  QDefExists: Boolean;
begin
  Result := 0;

  // Lookup querydef in the database
  QDefExists := False;
  for I := 0 to DB.QueryDefs.Count - 1 do
  begin
    QDef := DB.QueryDefs[I];
    if QDef.Name = AName then
    begin
      QDefExists := True;
      break; //-->
    end;
  end;

  // Create query def if it doesn't exists
  if not QDefExists then
  begin
    QDef := DB.CreateQueryDef(AName, AQuery);
    // Refresh is required to get the correct QDef.Type_
    DB.QueryDefs.Refresh;
  end;

  // Execute the query only if it is not a SELECT
  if QDef.Type_ <> dbQSelect then
  begin
    db.Execute(AQuery, dbInconsistent);
    Result := DB.RecordsAffected;
  end;
end;

Thank you all for the helpful answers and remarks.