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
- 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.
- 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
- 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
-
------------------------------------------------------------
- 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
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 - 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.
- 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
- 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
-
------------------------------------------------------------
- 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
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
You are quite welcome, Phil.
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 JOIN s 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 32,556
Expert Mod 16PB
BTW. In ADezii's code he excludes QueryDef s 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) : - .RecordSource ~sq_ [fr] {Form or Report name}
- .ControlSource ~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 QueryDef s would reflect the RecordSource s and ControlSource s 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 Tag s. At least in my projects they are ;-)
@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.
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
NeoPa 32,556
Expert Mod 16PB
Good for you Phil. I suspect that'll get some good use over time.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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.*,...
|
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...
|
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:
========================================================...
|
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.
|
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....
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |