仅查找可更新的查询 [英] Find only updateable Queries

问题描述

我在数据库中有多个查询，都保存了，SQL是RecordSources和RowSources。


我想找到一个只列出可更新SELECT查询的方法。


如果SQL中的第一个单词不是SELECT，我可以消除许多查询。例如，TRANSFORM，INSERT，UPDATE，DELETE等，如果单词UNION，GROUP BY，DISTINCT，FIRST，MAX，则为其他; ，MIN，COUNT，出现在SQL中。


我在假设单词LEFT JOIN是否正确&安培; 正确加入出现了，它也不会更新？


这仍然会留下很多疑问，其中许多仍然无法更新。


我怀疑MsysQueries可能有一些线索，但还没有把它们弄清楚。


感谢任何输入


Phil

解决方案

希望这会有所帮助，因为当我遇到意外的只读查询时，我会回到这两个参考站点：

严重用户的Microsoft Access提示


由Allen Browne提供。创建时间：2006年6月。更新日期：2010年4月

为什么我的查询只读？

如果您无法编辑查询中的数据，此列表可帮助您确定无法更新的原因：

？它有一个GROUP BY子句。总计查询始终是只读的。


？它有一个TRANSFORM子句。交叉表查询始终是只读的。


？它在SELECT子句中使用First（），Sum（），Max（），Count（）等。聚合记录的查询是只读的。


？它包含一个DISTINCT谓词。在查询的属性中将唯一值设置为否。


？它涉及UNION。联合查询始终是只读的。


？它在SELECT子句中有一个子查询。取消选中子查询下的显示框，或使用域聚合函数。


？它在FROM子句中的多个表上使用不同方向的JOIN。删除一些表。


？JOIN中的字段没有正确编制索引：JOINed字段上没有主键或唯一索引。


？查询'的Recordset Type属性是Snapshot。将Recordset Type设置为Dynaset在查询的属性中。


？查询是基于另一个只读查询（堆叠查询）。


？您的权限是只读的（访问安全性。）


？数据库是以只读方式打开的，还是文件属性是只读的，或者数据库是只读的媒体（例如CD-ROM，没有写权限的网络驱动器。）


？查询调用VBA函数，但数据库不在受信任位置，因此代码无法运行。 （请参阅此Access 2007页面顶部的黄色框。）


？查询输出的字段是计算字段（Access 2010.）

在Roger的博客上还有一个非常长的条目进入其他一些情况：
http：//rogersaccessblog.blogspot.com...eable-why.html

1. 我为您创建了一个代码演示，它将列出数据库中的所有查询及其更新状态。它基于Northwind示例数据库，可以轻松修改。我的第一印象是使用QueryDef对象的Updatable属性，但这仅表明您是否可以更新QueryDef的''定义''。我最终为每个非临时查询创建了一个Recordset，并检查了结果Recordset的可更新状态。它似乎是准确的，但我会留给你测试。
2. 代码定义：
   

   展开 | 选择 | 换行 | 行号

< blockquote>感谢您的信息。


正如我的第一篇帖子所示，我知道有很多单词是DISTINCT，GROUP BY。 &安培; " UNION"保证它不可更新，但我从未遇到过Rst.Updateable属性。


看起来很有前途，我会稍后再试。


非常感谢


Phil

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

解决方案

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

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

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

这篇关于仅查找可更新的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！