views:

547

answers:

5

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?

A: 

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.

MicSim
"All queries that execute a SELECT ... FROM ... are select queries" -- but that doesn't they return a resultset e.g. consider if the 'query' contained the SQL DDL 'CREATE VIEW MyView AS SELECT CustomerID FROM Customers;'
onedaywhen
That's not a SELECT query, it's a CREATE query and wont give any result ... This one would be in category "action query", as true with all DDL queries.
MicSim
So back to the original question then: "how can I decide upfront which will return records and which will not?" Put another way, how do you tell whether a query containing the SELECT keyword will return a resultset? INSERT INTO..SELECT.. is another example construct.
onedaywhen
This one will also not return any result set, as it's basically an insert. Quoting my answer: "... inspect the query sql command text to look if it STARTS with any of the above keywords ..." Feel free to add some other keywords to the 2 groups as needed (like DDL statements CREATE, DROP, ...)
MicSim
Hmm, what about a query that STARTS with the PARAMETERS keyword, followed by the SELECT keyword? How about one that STARTS with the SELECT keyword but then uses INTO [NewTableName] and therefore does not return a resultset? Thanks for the offer but I feel I cannot edit your answer to improve it; rather it's fundamentally flawed and parsing SQL is non-trivial anyhow. I don't think you have a workable system here.
onedaywhen
I admit that the proposed solution is not fitting all possible situations. It's always about the use case: Usually the above mentioned approach solves 99% or more of the usual situations with minimal effort, while the remaining 1% can be handled otherwise (like exceptions, as mentioned by someone else), or even ignored depending on use case.
MicSim
A: 

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;

;)

onedaywhen
+1  A: 

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
HansUp
Logically, an UPDATE is a DELETE and an INSERT. The above suggests that for the Access database engine an UPDATE is a DELETE and a crosstab!
onedaywhen
Onedaywhen. How is an UPDATE that doesn't update all fields in a record a logical DELETE and INSERT?
Tony Toews
@oneday - i think you are reading in more meaning where there is none - the list of flags is simply that - a list of flags
DJ
@DJ: in my experience, a enum named 'flags' suggests a bitmask, not to mention the values (16, 32, 64, ....).
onedaywhen
@Tony Toews: because it uses existing values for column not mentioned and happen all at once. Do you ever use SQL Server ON UPDATE triggers? You will have two logical tables named 'inserted' and 'deleted', rather than one named 'updated'.
onedaywhen
A: 

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?

Philippe Grondier
+1  A: 

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.

Tihauan
Sorry I completely overlooked the Delphi tag. I like your QueryDef Type approach better than MSysObjects Flags.
HansUp
@HansUp Now that you said that accepting my own answer won't feel that strange. Thanks again for the hints.
Tihauan