Prev: Form and subform for partial data entry
Next: Getting Server IP Address in Servoy Web Client
From: rivneym on 28 Apr 2010 14:32
Hi all,

Using 2007 now as a frontend with Oracle RDMS. Existing 2003 Access .mdb
is opened with the new version, 2007. However, adding of records, via a form
or query, fails. (Note, it works fine with Access 2003.) The query behind
the form and the other query consists of two tables with a one-to-one
relationship. (The left-side table contains a foreign key which the right-
hand table is the parent table.) The error message is that the Access engine
cannot find the record, in the right-side table, with key matching fields.

I have figured out a few different fixes. The first one is to change the
query's relationship to one-to-many. The results are still the same since
the many side (the right side table) is the parent table, and thus the same
set of records are returned by the query. The other solution is to set the
form's Recordset Type property from Dynaset to Dynaset (Inconsistent Updates).
I'm using the first solution since I couldn't find anything on the web about
the second solution, plus the name of the property's value explains enough.

I have a fix but still have yet to confirm if this is the true fix. Can't
find anything on the web. Any ideas and/or suggestions welcomed. Note: I
also converted the database to 2007 but the issue still persists.

Regards

From: Arvin Meyer [MVP] on 28 Apr 2010 18:41
There are some differences is the security models of Access 2003 and 2007,
that may affect how records are returned. You cannot really set references
in the front-end, it's the back-end that controls how data is indexed and
stored. I suggest that you continue using what works for you.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"rivneym" <u59673(a)uwe> wrote in message news:a737b4f45d0e9(a)uwe...
> Hi all,
>
> Using 2007 now as a frontend with Oracle RDMS. Existing 2003 Access .mdb
> is opened with the new version, 2007. However, adding of records, via a
> form
> or query, fails. (Note, it works fine with Access 2003.) The query
> behind
> the form and the other query consists of two tables with a one-to-one
> relationship. (The left-side table contains a foreign key which the
> right-
> hand table is the parent table.) The error message is that the Access
> engine
> cannot find the record, in the right-side table, with key matching fields.
>
> I have figured out a few different fixes. The first one is to change the
> query's relationship to one-to-many. The results are still the same since
> the many side (the right side table) is the parent table, and thus the
> same
> set of records are returned by the query. The other solution is to set
> the
> form's Recordset Type property from Dynaset to Dynaset (Inconsistent
> Updates).
> I'm using the first solution since I couldn't find anything on the web
> about
> the second solution, plus the name of the property's value explains
> enough.
>
> I have a fix but still have yet to confirm if this is the true fix.
> Can't
> find anything on the web. Any ideas and/or suggestions welcomed. Note:
> I
> also converted the database to 2007 but the issue still persists.
>
> Regards
>


From: rivneym on 28 Apr 2010 18:59
Arvin,
Thanks for the reply. BTW, the query is a 'Select' query (not an Append
Query) and it's not a pass-thru query either.
I wonder if the rules for when queries are updateable and no-updateable
have now changed with 2007 (and Microsoft hasn't gotten around to updating
their documentation or they're probably not aware of the issue).
Regards, Mike

Arvin Meyer [MVP] wrote:
>There are some differences is the security models of Access 2003 and 2007,
>that may affect how records are returned. You cannot really set references
>in the front-end, it's the back-end that controls how data is indexed and
>stored. I suggest that you continue using what works for you.
>> Hi all,
>>
>[quoted text clipped - 30 lines]
>>
>> Regards

From: Arvin Meyer [MVP] on 29 Apr 2010 01:31
The rules don't change, but sometimes the query optimizer does. In general,
a table or query will not be updateable if it doesn't contain the Primary
Key.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"rivneym" <u59673(a)uwe> wrote in message news:a73a0a7148be7(a)uwe...
> Arvin,
> Thanks for the reply. BTW, the query is a 'Select' query (not an Append
> Query) and it's not a pass-thru query either.
> I wonder if the rules for when queries are updateable and no-updateable
> have now changed with 2007 (and Microsoft hasn't gotten around to updating
> their documentation or they're probably not aware of the issue).
> Regards, Mike
>
> Arvin Meyer [MVP] wrote:
>>There are some differences is the security models of Access 2003 and 2007,
>>that may affect how records are returned. You cannot really set references
>>in the front-end, it's the back-end that controls how data is indexed and
>>stored. I suggest that you continue using what works for you.
>>> Hi all,
>>>
>>[quoted text clipped - 30 lines]
>>>
>>> Regards
>


 | 
Pages: 1
Prev: Form and subform for partial data entry
Next: Getting Server IP Address in Servoy Web Client