Extract TableAffected from Query

[Rijnsent]

I've built a bit of VBA code to run an action query (append/delete/insert into/update) and to log its result, I will run this function from a MS Access macro. There are a bunch of action queries, so I want to make a general function that will log the results. RecordsAffected is one bit of that, but I'm looking for something like "TableAffected". An action query can have multiple inputs, but in the end there is only 1 table influenced by it (rows added, deleted, updated).
I've had a look at MsysQueries and the properities of a QueryDefs, but the only way to go seems to be to try to disect the SQL statement. Is there anyone here with a working solution to extract the "TableAffected" from any given action query?

Thanks!

Koen

Function RunLogQuery(QueryName As String)

Dim db As Database
Set db = CurrentDb

Set qry = Nothing
On Error Resume Next
Set qry = db.QueryDefs(QueryName)
On Error GoTo 0

If qry Is Nothing Then
    'No query found, log error
    MsgBox "ERROR, unknown query: " & QueryName, vbOKOnly + vbCritical
Else
    varReturn = SysCmd(acSysCmdSetStatus, "Running: " & QueryName)
    Multi = 1
    If InStr(qry.SQL, "INSERT INTO") > 0 Then
        TblAffected = Trim(Mid(qry.SQL, Len("INSERT INTO") + 1, InStr(qry.SQL, "(") - Len("INSERT INTO") - 1))
    ElseIf InStr(qry.SQL, "DELETE ") > 0 Then
        Multi = -1
        If InStr(qry.SQL, "WHERE ") > 0 Then
            TblAffected = Trim(Mid(qry.SQL, InStr(qry.SQL, "FROM ") + 5, 1))
        ElseIf InStr(qry.SQL, ";") > 0 Then
            TblAffected = Trim(Mid(qry.SQL, InStr(qry.SQL, "FROM ") + 5, InStr(qry.SQL, ";") - InStr(qry.SQL, "FROM ") - 5))
        Else
            TblAffected = Trim(Mid(qry.SQL, InStr(qry.SQL, "FROM ") + 5, Len(qry.SQL) - InStr(qry.SQL, "FROM ") - 5))
        End If
    ElseIf InStr(qry.SQL, "UPDATE ") > 0 Then
        TblAffected = Trim(Mid(qry.SQL, Len("UPDATE") + 1, InStr(Len("UPDATE ") + 1, qry.SQL, " ") - Len("UPDATE") - 1))
    Else
        TblAffected = "UNKNOWN"
    End If
    
    On Error GoTo Err_Execute
    qry.Execute
    On Error GoTo 0
    
    Set rstLog = db.OpenRecordset("XTBL_LOG")
    rstLog.AddNew
    rstLog("TIMESTAMP") = Now()
    rstLog("ITM") = TblAffected
    rstLog("ACTION") = QueryName
    rstLog("VAL") = qry.RecordsAffected * Multi
    rstLog("USER") = Environ("USERNAME")
    rstLog.Update
    rstLog.Close
    Set rstLog = Nothing
    varReturn = SysCmd(acSysCmdSetStatus, " ")
End If

Set qry = Nothing
Set db = Nothing

Exit Function

Err_Execute:
' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
   For Each errLoop In DBEngine.Errors
      MsgBox QueryName & vbCr & "Error number: " & errLoop.Number & vbCr & errLoop.Description, vbCritical + vbOKOnly
   Next errLoop
End If
varReturn = SysCmd(acSysCmdSetStatus, " ")

Set qry = Nothing
Set db = Nothing

End Function

 

[Micron]

Well, that's a tricky one. It depends on whether or not your queries are single table or not. If they are, what you're doing should work - I know of no other method.
However, if they involve linked tables, then I don't know how you'd tell. What can affect the outcome is whether or not you've defined relationships, if referential integrity is applied, if cascade deletes are applied, and AFAIK, the type of joins. Because of that, I can't think of a reason why you'd need to know. If you were to open an action query in datasheet view and noted all was well with the proposed deletes/updates/etc. why worry about capturing the info in code? In other words, if the query operates correctly, why worry about it further?

 

[Rijnsent]

Hi Micron,

that's indeed as far as I got . Basically an UPDATE, INSERT INTO, DELETE query in my case will only affect 1 table (no cascades), but in the query itself could be multiple tables for filtering, relations etc. I want to log this so the user can see what happened with the various tables, for that a table name would be ideal. I will show that to them on a form where they run the macros I created, e.g. comparison over time of the same macro effect is valuable for consistency checks. Plus: if Access can cough up "RowsAffected" it should somewhere/somehow also know TableAffected, but that might be my logic and not MSFT logic :P. Come to think of it, if I run any of those queries manually it will only tell e.g. "you are about to delete x rows", not adding "from TABLE_NAME", so Access won't have that knowledge present .

Thanks for your thoughts,

Koen

 

[xenou]

Seems logical that somewhere in the code there is a table name. You could check the sys tables to see if you can find it there, when the query is parsed out. Probably parsing it from the query text (as you are doing) is just as good. I'd find the query names enough for logging purposes myself.

 

[Micron]

Come to think of it, if I run any of those queries manually it will only tell e.g. "you are about to delete x rows", not adding "from TABLE_NAME", so Access won't have that knowledge present

Corrrect, and for the reasons I noted. I don't profess to be an expert by any stretch of the imagination, but what's interesting is that since dabbling in Access since version 2.0 I've never known anyone to be concerned about which tables are affected - least of all the users, some who could not even create a desktop shortcut. The idea might have crossed Access developer minds, but I'm confident they'd quickly dismiss it in a flash because there's no way to pin it down if there's more than one table involved. Your method can only be considered accurate IF there are no joins between tables. I'm curious as to why you'd want to apprise the user which table was affected. I would think showing which records were or will be affected would be of more value, but even then, it's not something I've ever seen done. Perhaps someone else who might be following this will comment if they disagree. I can always use enlightenment.

 

[xenou]

Action queries only update/insert/delete a single table. I believe that also defined by the standard for SQL (in theory) and implemented by all SQL languages (in practice).

Note: ignoring cascades, etc. - that's a good point.

 

[Micron]

Action queries only update/insert/delete a single table. I believe that also defined by the standard for SQL (in theory) and implemented by all SQL languages (in practice).

Note: ignoring cascades, etc. - that's a good point.

No argument there, but which one in a series of linked tables? The leftmost, rightmost, left joined, right joined, equal? To pick that table name out of a long sql string and get it right sounds risky, and for me, pointless.

 

[xenou]

The table must come immediately after the UPDATE keyword. Example:

UPDATE Table1 INNER JOIN ...

This updates Table1, regardless of what's in the rest of the join. Parsing a query string isn't terribly difficult. In fact, the string must be capable of being parsed without uncertainty in order for the sql engine to work correctly.

 

[Rijnsent]

Hi Micron & Xenou,
thanks for the discussion on this topic. I do get that I want something that is not normal to ask, but that's my job (am a business consultant, 9 out of 10 times I get that at customers: "but why would you want that", "we've never thought about that", "that is not going to work"). "There is no use for that" and "it can't be done" just get me started and more curious . Anyhow, I'll work on my current macro a bit more, because as Xenou stated: the first table mentioned after INSERT INTO, UPDATE or DELETE FROM should be the table that is affected by the query.
The immediate purpose for the logging: I'm running about 100 seperate queries from a series of macros influencing a couple of tables and I want, at the end of an update run, a quick overview for myself/the user what query updated which table and what it did, giving the user the ability to filter on the effects on just one table.
Thanks again for your input!
Cheers,
Koen