Error with compound key in converted DB

[CJM]

Problem:

This problem occurs in a new Access 2003 database with objects imported from
another Access 2003 database which was once converted from a previous
version.
This problem does not occur in the original (converted) Access 2003
database.

The problem occurs in all queries (approx. 80) that have two particular
tables linked by three joins (ie table with a 3-part compound key). When any
of these queries are opened in design view an error is displayed (see
attachment). On accepting the error the query opens in design view and one
particular join has been removed (the import routine also removes the same
join).

When running one of the queries which contains this 'error', the query runs
without a problem and returns the correct data.

Creating new fields in both tables and recreating the join does not resolve
the problem, it saves okay, then displays the error message the next time
the query is opened in design view.

Additional Info:

Importing all tables, forms, etc., but not queries from the current database
to the older (converted) database, the database works fine.
However, when importing the queries from the older (converted) database to
the current database the third joins are removed (without any warnings).

This error occurs when the join links two identical fields (numeric or
text).

This error sounds similar to KB articles 207868 (Access 2000) and 161861
(Access 97), but the SQL view does not the have extra parentheses, nor were
the queries created using the wizard.

'Compact and Repair Database' does not cure the problem, nor remove the
third join - the error is still displayed the next time the query is opened
in design view.

A screenshot of the error is available at
http://www.brightnorth.com/error.jpg.

Thanks

Chris

 

[Brendan Reynolds]

The use of the word 'Year' as a field name may be causing problems because
this is the name of a built-in VBA function.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[CJM]

Possible. I'll check it out but I think the use of keywords within [] should
be OK...

 

[Brendan Reynolds]

Might be, but your screen shot indicates that it is *not* within [].

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Possible. I'll check it out but I think the use of keywords within [] should
be OK...

The use of the word 'Year' as a field name may be causing problems because
this is the name of a built-in VBA function.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[CJM]

Just checked with my colleague (who owns the DB) - seems you were right...

He's changed the field names and reports that it now works...

Cheers