Dynaset, inconsistent updates

[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

[Gina Whipp]

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

"Craig" <Cr...@discussions.microsoft.com> wrote in message
news:84312F30-AB5B-46DA...@microsoft.com...

[Craig]

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

[Gina Whipp]

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

"Craig" <Cr...@discussions.microsoft.com> wrote in message

news:442FD316-3A03-423F...@microsoft.com...

[Craig]

Gina...

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

[Gina Whipp]

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

"Craig" <Cr...@discussions.microsoft.com> wrote in message

news:FEAD1680-C695-4144...@microsoft.com...

[Craig]

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

"Gina Whipp" wrote:

> .
>

[Gina Whipp]

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

"Craig" <Cr...@discussions.microsoft.com> wrote in message

news:17ACECD1-7577-4EA8...@microsoft.com...

[Craig]

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.

[Gina Whipp]

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

"Gina Whipp" <NotInt...@InViruses.com> wrote in message
news:8EF03C9A-0A1D-4C52...@microsoft.com...

[Craig]

Please see below...
--
Craig

[Gina Whipp]

Craig,

I checked in Version 2003 and 2007 and that setting not allowed in Update
queries only Select queries.

--
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" <Cr...@discussions.microsoft.com> wrote in message

news:891543E0-7D1A-4957...@microsoft.com...

[Bob Barrows]

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.

Craig wrote:
> 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.
>

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Gina Whipp]

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

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OKiof0a0...@TK2MSFTNGP06.phx.gbl...

[Craig]

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.

[Bob Barrows]

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.

[Gina Whipp]

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

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:#GjMSId0...@TK2MSFTNGP04.phx.gbl...

[Gina Whipp]

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

"Craig" <Cr...@discussions.microsoft.com> wrote in message

news:1A7E6865-128F-4962...@microsoft.com...

[Bob Barrows]

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?

Craig wrote:
> 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.
>

--

[Craig]

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.
--
Craig

"Bob Barrows" wrote:

> .
>

[Gina Whipp]

Craig,

Nope, never thought you were *snarky*, just confused... hence the asking for
the SQL. We are happy to hear the matter has been resolved. However, if
you still need help with your UPDATE query because, as by this posting, it
is not working as desired... posting it here might help yget you your
desired results!

--
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" <Cr...@discussions.microsoft.com> wrote in message

news:A049E1D0-F763-4410...@microsoft.com...