473,320 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Find only updateable Queries

PhilOfWalton
1,430 Expert 1GB
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

10 1241
zmbd
5,501 Expert Mod 4TB
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
8,834 Expert 8TB
  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
1,430 Expert 1GB
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
8,834 Expert 8TB
You are quite welcome, Phil.
Jun 25 '17 #5
NeoPa
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
@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
1,430 Expert 1GB
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
8,834 Expert 8TB
You are quite welcome.
Jun 28 '17 #10
NeoPa
32,556 Expert Mod 16PB
Good for you Phil. I suspect that'll get some good use over time.
Jun 28 '17 #11

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

Similar topics

6
by: jason | last post by:
I am picking up an error message on a straightforward INSERT - do I need an optimistic-type to get this working....here is is the error: Microsoft JET Database Engine error '80004005' Operation...
5
by: My SQL | last post by:
Hi, I started the general query log with mysqld --log On my machine this command works fine and every select, update and all statements are logged. I tried the same command on another...
1
by: Bruce MacDonald | last post by:
I've got a question/request for the SQL gurus. I'm building a model of bandwidth demand in MS Access and want to get aggregated results for demand at each PCP in each time period. The two...
6
by: ano1optimist | last post by:
I have been running these queries in Access 2000 with no problems. This week, I had to install Access 2003 to create some runtime versions for another application, and now I keep getting "operation...
1
by: Edward | last post by:
I've recently migrated the back end of one of my client's applications from Access to SQL Server. One of the forms is based on an Access query thus: SELECT dbo_tblDistributionDetail.*,...
7
by: Zlatko Matić | last post by:
Let's assume that we have a database on some SQL server (let it be MS SQL Server) and that we want to execute some parameterized query as a pass.through query. How can we pass parameters to the...
8
by: Jim in Arizona | last post by:
I've been using an example out of a book to be able to edit the rows in a database. I am getting the following error: ========================================================...
3
by: hedgracer | last post by:
Is there any way to generate a list of access queries in an access database which utilize a specific table? Thanks for all help in advance. Dave C.
2
by: Simon | last post by:
Dear reader, Is there a tool available to check the necessity of a Query. Queries are used in Forms and Reports. It can be that a Form or Report is not longer required and will be deleted....
0
by: elimeli1 | last post by:
Hi. I am writing a small monitoring tool for our operators that can monitor our DB2 databases in the organization. I know there are plenty of tools out there but I need something very customized that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.