By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,569 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Find only updateable Queries

PhilOfWalton
Expert 100+
P: 1,430
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
Jun 25 '17 #1

✓ answered by 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:
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef
    2. Dim rst As DAO.Recordset
    3.  
    4. Debug.Print "Query Name"; Tab(45); "Update Status"
    5. Debug.Print String(60, "-")
    6.  
    7. For Each qdf In CurrentDb.QueryDefs
    8.   With qdf
    9.     If Left$(.Name, 1) <> "~" Then
    10.       Set rst = .OpenRecordset
    11.         Debug.Print .Name; Tab(45); IIf(rst.Updatable, "Updateable", "NOT Updateable")
    12.     End If
    13.   End With
    14. Next
    15. Debug.Print String(60, "-")
    16.  
    17. qdf.Close
    18. rst.Close
    19. Set qdf = Nothing
    20. Set rst = Nothing
  3. OUTPUT (based on Northwind Sample):
    Expand|Select|Wrap|Line Numbers
    1. Query Name                                  Update Status
    2. ------------------------------------------------------------
    3. Customers Extended                          Updateable
    4. Employees Extended                          Updateable
    5. Inventory                                   NOT Updateable
    6. Inventory on Hold                           NOT Updateable
    7. Inventory on Order                          NOT Updateable
    8. Inventory Purchased                         NOT Updateable
    9. Inventory Sold                              NOT Updateable
    10. Invoice Data                                Updateable
    11. Order Details Extended                      Updateable
    12. Order Price Totals                          NOT Updateable
    13. Order Subtotals                             NOT Updateable
    14. Order Summary                               NOT Updateable
    15. Product Category Sales by Date              NOT Updateable
    16. Product Orders                              Updateable
    17. Product Purchases                           Updateable
    18. Product Sales by Category                   Updateable
    19. Product Sales Qty by Employee and Date      NOT Updateable
    20. Product Sales Total by Date                 NOT Updateable
    21. Product Transactions                        NOT Updateable
    22. Products on Back Order                      NOT Updateable
    23. Purchase Details Extended                   Updateable
    24. Purchase Price Totals                       NOT Updateable
    25. Purchase Summary                            NOT Updateable
    26. qryEmployees                                Updateable
    27. qryOrders                                   Updateable
    28. Sales Analysis                              NOT Updateable
    29. Shippers Extended                           Updateable
    30. Suppliers Extended                          Updateable
    31. Top Ten Orders by Sales Amount              NOT Updateable
    32. ------------------------------------------------------------
  4. To process only SELECT/Updatable Queries:
    Expand|Select|Wrap|Line Numbers
    1. On Error Resume Next
    2. Dim qdf As DAO.QueryDef
    3. Dim rst As DAO.Recordset
    4.  
    5. Debug.Print "SELECT Query Name"; Tab(45); "Update Status"
    6. Debug.Print String(60, "-")
    7.  
    8. For Each qdf In CurrentDb.QueryDefs
    9.   With qdf
    10.     If Left$(.Name, 1) <> "~" And InStr(.SQL, "SELECT") > 0 Then   'SELECT/Non-Temp Query
    11.       Set rst = .OpenRecordset
    12.         If rst.Updatable Then
    13.           Debug.Print .Name; Tab(45); IIf(rst.Updatable, "Updateable", "NOT Updateable")
    14.         End If
    15.     End If
    16.   End With
    17. Next
    18. Debug.Print String(60, "-")
    19.  
    20. qdf.Close
    21. rst.Close
    22. Set qdf = Nothing
    23. Set rst = Nothing

Share this Question
Share on Google+
10 Replies


zmbd
Expert Mod 5K+
P: 5,397
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...eable-why.html
Jun 25 '17 #2

ADezii
Expert 5K+
P: 8,638
  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:
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef
    2. Dim rst As DAO.Recordset
    3.  
    4. Debug.Print "Query Name"; Tab(45); "Update Status"
    5. Debug.Print String(60, "-")
    6.  
    7. For Each qdf In CurrentDb.QueryDefs
    8.   With qdf
    9.     If Left$(.Name, 1) <> "~" Then
    10.       Set rst = .OpenRecordset
    11.         Debug.Print .Name; Tab(45); IIf(rst.Updatable, "Updateable", "NOT Updateable")
    12.     End If
    13.   End With
    14. Next
    15. Debug.Print String(60, "-")
    16.  
    17. qdf.Close
    18. rst.Close
    19. Set qdf = Nothing
    20. Set rst = Nothing
  3. OUTPUT (based on Northwind Sample):
    Expand|Select|Wrap|Line Numbers
    1. Query Name                                  Update Status
    2. ------------------------------------------------------------
    3. Customers Extended                          Updateable
    4. Employees Extended                          Updateable
    5. Inventory                                   NOT Updateable
    6. Inventory on Hold                           NOT Updateable
    7. Inventory on Order                          NOT Updateable
    8. Inventory Purchased                         NOT Updateable
    9. Inventory Sold                              NOT Updateable
    10. Invoice Data                                Updateable
    11. Order Details Extended                      Updateable
    12. Order Price Totals                          NOT Updateable
    13. Order Subtotals                             NOT Updateable
    14. Order Summary                               NOT Updateable
    15. Product Category Sales by Date              NOT Updateable
    16. Product Orders                              Updateable
    17. Product Purchases                           Updateable
    18. Product Sales by Category                   Updateable
    19. Product Sales Qty by Employee and Date      NOT Updateable
    20. Product Sales Total by Date                 NOT Updateable
    21. Product Transactions                        NOT Updateable
    22. Products on Back Order                      NOT Updateable
    23. Purchase Details Extended                   Updateable
    24. Purchase Price Totals                       NOT Updateable
    25. Purchase Summary                            NOT Updateable
    26. qryEmployees                                Updateable
    27. qryOrders                                   Updateable
    28. Sales Analysis                              NOT Updateable
    29. Shippers Extended                           Updateable
    30. Suppliers Extended                          Updateable
    31. Top Ten Orders by Sales Amount              NOT Updateable
    32. ------------------------------------------------------------
  4. To process only SELECT/Updatable Queries:
    Expand|Select|Wrap|Line Numbers
    1. On Error Resume Next
    2. Dim qdf As DAO.QueryDef
    3. Dim rst As DAO.Recordset
    4.  
    5. Debug.Print "SELECT Query Name"; Tab(45); "Update Status"
    6. Debug.Print String(60, "-")
    7.  
    8. For Each qdf In CurrentDb.QueryDefs
    9.   With qdf
    10.     If Left$(.Name, 1) <> "~" And InStr(.SQL, "SELECT") > 0 Then   'SELECT/Non-Temp Query
    11.       Set rst = .OpenRecordset
    12.         If rst.Updatable Then
    13.           Debug.Print .Name; Tab(45); IIf(rst.Updatable, "Updateable", "NOT Updateable")
    14.         End If
    15.     End If
    16.   End With
    17. Next
    18. Debug.Print String(60, "-")
    19.  
    20. qdf.Close
    21. rst.Close
    22. Set qdf = Nothing
    23. Set rst = Nothing
Jun 25 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
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
Jun 25 '17 #4

ADezii
Expert 5K+
P: 8,638
You are quite welcome, Phil.
Jun 25 '17 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jun 25 '17 #6

NeoPa
Expert Mod 15k+
P: 31,494
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) :
Expand|Select|Wrap|Line Numbers
  1. .RecordSource   ~sq_ [fr] {Form or Report name}
  2. .ControlSource  ~sq_ [cd] {Form or Report name} ~sq_ [cd] {Control name}
Where :
Expand|Select|Wrap|Line Numbers
  1. ~sq_    Standard lead-in.
  2. [c]     Control on a Form.
  3. [d]     Control on a Report.
  4. [f]     Form.
  5. [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 :
Expand|Select|Wrap|Line Numbers
  1. [frmA].RecordSource             ~sq_ffrmA
  2. [frmA].[cboB].ControlSource     ~sq_cfrmA~sq_ccboB
  3. [rptC].RecordSource             ~sq_rrptC
  4. [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 :
Expand|Select|Wrap|Line Numbers
  1.     If .Name Like "~sq_[cdfr]*" _
  2.     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 ;-)
Jun 25 '17 #7

ADezii
Expert 5K+
P: 8,638
@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.
Jun 25 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
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
Jun 28 '17 #9

ADezii
Expert 5K+
P: 8,638
You are quite welcome.
Jun 28 '17 #10

NeoPa
Expert Mod 15k+
P: 31,494
Good for you Phil. I suspect that'll get some good use over time.
Jun 28 '17 #11

Post your reply

Sign in to post your reply or Sign up for a free account.