Error with compound key in converted DB

CJM

unread,

Jul 1, 2004, 5:47:55 AM7/1/04

to

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

unread,

Jul 1, 2004, 6:20:19 AM7/1/04

to

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" <cjmn...@newsgroups.nospam> wrote in message
news:ep8b9B1X...@tk2msftngp13.phx.gbl...

CJM

unread,

Jul 1, 2004, 7:54:06 AM7/1/04

to

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

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
news:u6BbFU1X...@TK2MSFTNGP10.phx.gbl...

Brendan Reynolds

unread,

Jul 1, 2004, 9:07:22 AM7/1/04

to

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.

"CJM" <cjmn...@newsgroups.nospam> wrote in message

news:e4n6dI2X...@TK2MSFTNGP09.phx.gbl...

CJM

unread,

Jul 1, 2004, 11:50:57 AM7/1/04

to

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

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message

news:OTCFcx2X...@tk2msftngp13.phx.gbl...

Mingqing Cheng [MSFT]

unread,

Jul 1, 2004, 10:37:22 PM7/1/04

to

Hi CJM,

Thanks for using MSDN Newsgroup!

I have noticed you have another thread in newsgroup:
microsoft.public.access.query and I have added a reply to that thread. To
keep the intergity of newsgroup, I will keep monitoring that thread and
follow up if necessary :)

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!