Dynaset, inconsistent updates

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Hello. I've recently upgraded to Office 2007...

Prior versions of Access allowed the user to update tables (via update
queries utilizing another query) without getting the dreaded "not updateable"
error. You would accomplish this by changing your query property settings to
"dynaset/allow inconsisent updates".

Queries in Access 2007 don't allow for this, correct? Or am I missing
something? Is the only alternative to create numerous temp tables, and then
use those temp tables in your queries as joins?

Thanks.
 
Craig,

It's still there... are you looking at the Query Properties or the Field
Properties (won't find it there)?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello. I've recently upgraded to Office 2007...

Prior versions of Access allowed the user to update tables (via update
queries utilizing another query) without getting the dreaded "not
updateable"
error. You would accomplish this by changing your query property settings
to
"dynaset/allow inconsisent updates".

Queries in Access 2007 don't allow for this, correct? Or am I missing
something? Is the only alternative to create numerous temp tables, and then
use those temp tables in your queries as joins?

Thanks.
 
Gina...

Thanks for the reply. No, I do not see it. When I open a 2003 Access query
and right-click the query properties I get a dialog box with a "general" tab
that includes several options, including "recordset type" that you can toggle
to "dynaset", etc.

I don't see that in Access 2007 queries. When I right-click in the query I
get a dialog box with the "general" tab, but only 2 options ("source" and
"alias").

Do I need a service pack, or is there something else I'm missing?

Thanks.
 
Craig,

Does it say...

Property Sheet
Selection Type: Query Properties

General

....what kind of query is it? AND are you in Design Mode for the query?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina...

Thanks for the reply. No, I do not see it. When I open a 2003 Access query
and right-click the query properties I get a dialog box with a "general" tab
that includes several options, including "recordset type" that you can
toggle
to "dynaset", etc.

I don't see that in Access 2007 queries. When I right-click in the query I
get a dialog box with the "general" tab, but only 2 options ("source" and
"alias").

Do I need a service pack, or is there something else I'm missing?

Thanks.
 
Gina...

Yes, I'm in design mode for the query. But the selection type says "field
list properties", not "query properties".
 
Craig,

Click in the area where the tables are... not on a table while the
Properties window is open.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina...

Yes, I'm in design mode for the query. But the selection type says "field
list properties", not "query properties".
 
OK, now I see the query properties...but still, there is no "recordset type"
like in 2003...unless I'm missing something...
 
Craig,

Oh dear... Craig my apologies, I missed the whole Update query part. That
option not available in an Update query... it is in a Select query.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

OK, now I see the query properties...but still, there is no "recordset type"
like in 2003...unless I'm missing something...
 
No problem...thanks for the assistance.

So, my understanding is you can't update data in a table (linking a query to
a table) with inconsistent updates, as you could in version 2003?

thanks.
 
Craig,

I just saw you posted a reply but it disappeared from newsreader just that
quick. Please repost...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Craig,

Oh dear... Craig my apologies, I missed the whole Update query part. That
option not available in an Update query... it is in a Select query.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

OK, now I see the query properties...but still, there is no "recordset type"
like in 2003...unless I'm missing something...
 
Please see below...
--
Craig


Craig said:
No problem...thanks for the assistance.

So, my understanding is you can't update data in a table (linking a query to
a table) with inconsistent updates, as you could in version 2003?

thanks.
Click to expand...
 
I think you have Gina (as well as some of the rest of us) confused that you
might be talking about an Update query, ie., a query whose sql begins with
the word "UPDATE", rather than a Select query whose sql begins with the word
"SELECT". Could you clarify?

I don't have A2007 installed so I can't confirm or deny whether that option
no longer exists for Select queries in A2007.
 
Bob,

The option does exists in Access 2007 but it does only apply to SELECT
queries and I think the Poster thinks it applied to UPDATE queries which it
never did no matter what version of Access.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I think you have Gina (as well as some of the rest of us) confused that you
might be talking about an Update query, ie., a query whose sql begins with
the word "UPDATE", rather than a Select query whose sql begins with the word
"SELECT". Could you clarify?

I don't have A2007 installed so I can't confirm or deny whether that option
no longer exists for Select queries in A2007.
 
Ok, sorry for the confusion....I thought it was clear in my prior posts that
I intended to "update data".

If you look at the query properties in Access 2003 you are CLEARLY ABLE to
update using inconsistent updates (right click in query properties, see
"recordset type").

Is this recordset type available in Access 2007? You could do it in Access
2003, I'm getting the impression this ability was removed in Access 2007.

Thanks.
 
I'm not sure that's what Craig is talking about. From what I can see in the
prior posts, he could be talking about attempting to edit (update) the
resultset from a SELECT query. I see nothing to indicate that he is
definitely using an UPDATE query. That is why I asked him to confirm what
kind of query he is trying to set this property for. The easiest way for him
to confirm this is to show us the sql statement.

I do agree that the property does not apply to any type of "action" query,
only Select queries.
 
Craig,

I just went and checked again... that option is not there, the only time I
see that option is on queries that with the word SELECT... Please copy
paste the SQL of the query you say is an update query here.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Ok, sorry for the confusion....I thought it was clear in my prior posts that
I intended to "update data".

If you look at the query properties in Access 2003 you are CLEARLY ABLE to
update using inconsistent updates (right click in query properties, see
"recordset type").

Is this recordset type available in Access 2007? You could do it in Access
2003, I'm getting the impression this ability was removed in Access 2007.

Thanks.
 
Bob,

After RE-reading I see you are correct. Though he just posted back saying
he is using and UPDATE query. So I have asked that he copy/paste the SQL
here.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I'm not sure that's what Craig is talking about. From what I can see in the
prior posts, he could be talking about attempting to edit (update) the
resultset from a SELECT query. I see nothing to indicate that he is
definitely using an UPDATE query. That is why I asked him to confirm what
kind of query he is trying to set this property for. The easiest way for him
to confirm this is to show us the sql statement.

I do agree that the property does not apply to any type of "action" query,
only Select queries.
 
Please, you are not resolving the confusion. The only way to resolve this
confusion is to show us the sql statement (or at least the first few words)
of the query whose properties you are trying to set. Does the sql statement
begin with "SELECT" or doesn't it?

Just in case, you switch the query from Design View to SQL View by using the
option in the View menu, or the toolbar button, or the right-click context
menu.

If not, then Gina is correct, an action query (a query whose sql statement
begins with UPDATE, INSERT or DELETE) does not return a recordset, so that
type of query does not have a recordset type property, even in A2003 (and I
have just confirmed this again). The only type of query for which I can
"CLEARLY" see a recordset type is a SELECT query. So please check again and
confirm it for us so we can stop going around in circles. Are you dealing
with a SELECT query whose results you wish to edit in the resulting
datasheet view, or an UPDATE query?
 
Bob/Gina:

My apologies...here I am coming off 'snarky' to people who are helping
me...inexcusable.

You are correct. My code was referencing a select query. This option
doesn't exist in an update query...only in a select query, in either 2003 or
2007. By choosing "inconsistent updates" you can update data in a select
query that has both a table and a query. If that query, however, is a group
by query, then you can't.

Thanks again.
 
Back
Top