Find only updateable Queries

[PhilOfWalton]

I have number of queries in a database, both saved, and SQL's as RecordSources and RowSources.

I want to find a method of listing only the updateable SELECT Queries.

I can eliminate a number of queries if the first word in the SQL is not "SELECT" e.g "TRANSFORM" , "INSERT", "UPDATE", "DELETE", and others if the word "UNION", "GROUP BY", "DISTINCT", "FIRST", "MAX" ,"MIN", "COUNT" appears in the SQL.

Am I correct in assuming if the word "LEFT JOIN" & "RIGHT JOIN" appear, it also won't be updateable?

That still leaves a lot of queries, many of which still won't be updateable.

I suspect there may be some clues in MsysQueries, but haven't yet fathomed them out.

Be grateful for any input

Phil

[zmbd]

Hopefully this will help as these are the two reference sites I'll go back to when I run across the unexpected read-only query:

Microsoft Access Tips for Serious Users

Provided by Allen Browne. Created: June 2006. Updated: April 2010

Why is my query read-only?

If you cannot edit the data in a query, this list may help you identify why it is not updatable:

•It has a GROUP BY clause. A Totals query is always read-only.

•It has a TRANSFORM clause. A Crosstab query is always read-only.

•It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.

•It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties.

•It involves a UNION. Union queries are always read-only.

•It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.

•It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.

•The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.

•The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties.

•The query is based on another query that is read-only (stacked query.)

•Your permissions are read-only (Access security.)

•The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)

•The query calls a VBA function, but the database is not in a trusted location so the code cannot run. (See the yellow box at the top of this Access 2007 page.)

•The fields that the query outputs are Calculated fields (Access 2010.)

There is also a very long entry on Roger's blog that goes into some other situations:

http://rogersaccessblog.blogspot.com/2009/11/this-recordset-is-not-updateable-why.html

[ADezii]

1. I created a Code Demo for you that will list all the Queries in your Database as well as their Update Status. It is based on the Northwind Sample Database and can easily be modified. My first impression was to use the Updatable Property of a QueryDef Object but that only indicates whether or not you can Update the 'Definition' of the QueryDef. I ended up creating a Recordset for each Non-Temporary Query and checking the Updateable Status of the resulting Recordset. It appears to be accurate but I will leave that to you to test.
2. Code Definition:
   

   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   Debug.Print "Query Name"; Tab(45); "Update Status"
   Debug.Print String(60, "-")
   
   For Each qdf In CurrentDb.QueryDefs
     With qdf
       If Left$(.Name, 1) <> "~" Then
         Set rst = .OpenRecordset
           Debug.Print .Name; Tab(45); IIf(rst.Updatable, "Updateable", "NOT Updateable")
       End If
     End With
   Next
   Debug.Print String(60, "-")
   
   qdf.Close
   rst.Close
   Set qdf = Nothing
   Set rst = Nothing

3. OUTPUT (based on Northwind Sample):
   

   Query Name                                  Update Status
   ------------------------------------------------------------
   Customers Extended                          Updateable
   Employees Extended                          Updateable
   Inventory                                   NOT Updateable
   Inventory on Hold                           NOT Updateable
   Inventory on Order                          NOT Updateable
   Inventory Purchased                         NOT Updateable
   Inventory Sold                              NOT Updateable
   Invoice Data                                Updateable
   Order Details Extended                      Updateable
   Order Price Totals                          NOT Updateable
   Order Subtotals                             NOT Updateable
   Order Summary                               NOT Updateable
   Product Category Sales by Date              NOT Updateable
   Product Orders                              Updateable
   Product Purchases                           Updateable
   Product Sales by Category                   Updateable
   Product Sales Qty by Employee and Date      NOT Updateable
   Product Sales Total by Date                 NOT Updateable
   Product Transactions                        NOT Updateable
   Products on Back Order                      NOT Updateable
   Purchase Details Extended                   Updateable
   Purchase Price Totals                       NOT Updateable
   Purchase Summary                            NOT Updateable
   qryEmployees                                Updateable
   qryOrders                                   Updateable
   Sales Analysis                              NOT Updateable
   Shippers Extended                           Updateable
   Suppliers Extended                          Updateable
   Top Ten Orders by Sales Amount              NOT Updateable
   ------------------------------------------------------------

4. To process only SELECT/Updatable Queries:
   

   On Error Resume Next
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   Debug.Print "SELECT Query Name"; Tab(45); "Update Status"
   Debug.Print String(60, "-")
   
   For Each qdf In CurrentDb.QueryDefs
     With qdf
       If Left$(.Name, 1) <> "~" And InStr(.SQL, "SELECT") > 0 Then   'SELECT/Non-Temp Query
         Set rst = .OpenRecordset
           If rst.Updatable Then
             Debug.Print .Name; Tab(45); IIf(rst.Updatable, "Updateable", "NOT Updateable")
           End If
       End If
     End With
   Next
   Debug.Print String(60, "-")
   
   qdf.Close
   rst.Close
   Set qdf = Nothing
   Set rst = Nothing

[PhilOfWalton]

Thanks for the info.

As my first post indicates, I was aware of a number of words sush as "DISTINCT", "GROUP BY" & "UNION" which guaranteed it was not updatable, but I have never come across the Rst.Updateable property.

Looks very promising, I will try that later.

Many thanks

Phil

[ADezii]

You are quite welcome, Phil.

[NeoPa]

Looks like I'm late to the party again :-(

Nevertheless, I see one aspect of the question that wasn't dealt with directly, in spite of two very good and helpful answers. LEFT & RIGHT JOINs do not, of themselves, cause a query to be non-updateable.

Otherwise, ADezii's code helps you to identify them quickly in a batch, and ZMBD's copy of Allen Browne's list of suspects will allow you to remedy any that you require to be updateable.

[NeoPa]

BTW. In ADezii's code he excludes QueryDefs that start with a tilde (~). Bear in mind though, that if you're looking for sets of SQL used in RecordSource and ControlSource properties then these are held in the QueryDefs collection with names starting with a tilde and the following format (without the spaces and where [] means one of, and {} means whatever matches the explanation inside) :

[U].RecordSource[/U]   ~sq_ [fr] {Form or Report name}
[U].ControlSource[/U]  ~sq_ [cd] {Form or Report name} ~sq_ [cd] {Control name}

Where :

~sq_    Standard lead-in.
[c]     Control on a Form.
[d]     Control on a Report.
[f]     Form.
[r]     Report.

EG. With a Form called [frmA] having a control called [cboB] and a Report called [rptC] having a control called [cboD] the following QueryDefs would reflect the RecordSources and ControlSources of each of these objects :

[frmA].RecordSource             ~sq_ffrmA
[frmA].[cboB].ControlSource     ~sq_cfrmA~sq_ccboB
[rptC].RecordSource             ~sq_rrptC
[rptC].[cboD].ControlSource     ~sq_drptC~sq_dcboD

Thus, to catch all of these without needing to develop any other code, you simply filter out less. Something like the following for ADezii's line #9 :

    If .Name Like "~sq_[cdfr]*" _
    Or Not .Name Like "~*" Then

Please bear in mind that this will not capture SQL held elsewhere. I'm thinking mainly template SQL often found in Tags. At least in my projects they are ;-)

[ADezii]

@NeoPa:
Excellent points especially given Phil's opening statement:

I have number of queries in a database, both saved, and SQL's as RecordSources and RowSources.

[PhilOfWalton]

Thanks everyone, ADezii's post put me on the right track.

As a matter if interest, I am in the process of writing a program to scramble data in an external database, so that if people want help on the Bytes website and want to send a database with sensitive information, they can scramble some of the data before sending it.

It's work in progress, but I can select the external database, list all the tables & (now) all the updateable queries, then select which one to scramble. I then list all the fields in that table / query and choose which fields to scramble.

Thanks again,

Phil

[ADezii]

You are quite welcome.

[NeoPa]

Good for you Phil. I suspect that'll get some good use over time.