A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Upgrade questions, 2003 to 2007



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 07:32 PM posted to microsoft.public.access.forms
rivneym
external usenet poster
 
Posts: 2
Default Upgrade questions, 2003 to 2007

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

  #2  
Old April 28th, 2010, 11:41 PM posted to microsoft.public.access.forms
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Upgrade questions, 2003 to 2007

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@uwe wrote in message news:a737b4f45d0e9@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



  #3  
Old April 28th, 2010, 11:59 PM posted to microsoft.public.access.forms
rivneym
external usenet poster
 
Posts: 2
Default Upgrade questions, 2003 to 2007

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


  #4  
Old April 29th, 2010, 06:31 AM posted to microsoft.public.access.forms
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Upgrade questions, 2003 to 2007

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@uwe wrote in message news:a73a0a7148be7@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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.