Access 2007 - Query Design View Error - " is not a valid name...

[emc]

My database was built in 2003 and runs great in 2003, however, I get this
error when attempting to open a query in design view within Access 2007 (" is
not a valid name...).

I am running Office Professional Pro 2007 with SP1 on Win XP SP2.

All of my tables are linked to a backend Access DB on our network.
I generate this error attempting to view queries containing 2 or more tables.
I also generate this error attempting to view the Relationships window.

I have used proper naming conventions, no punctuation marks, etc. I am able
to open my tables in either view as well as run my queries or view them in
SQL. Also, I do not have security applied to this MDB.

Upon calling Microsoft, they apparently have listed this item as a bug (ref
#37769) but I haven't seen a fix anywhere yet.

Anyone hear of anything?

Thx!

 

[NTC]

you are able to: "run my queries or view them in SQL"

but not view them in design view??

how do you get to view the sql if you can not open them in design view?

have you established the backend file as a Trust Location?

 

[Bronwyn]

We have experienced the same thing on a number of our databases. When you
right click and go to "Open in design view" it will bail out to SQL view
(sometimes).

I'd love to hear if there's a fix for it though!

 

[NTC]

a query will go direct to SQL view if the sql statement is too complex for a
design grid visual representation.

there is nothing wrong in a case like this (presuming your query and
forms/reports are working of course....) there are plenty of sql statements
particularly involving joins of one table on itself that can not be displayed
via grid....

 

[Bronwyn]

But why would it display fine in Access 2003 and not in 2007? If I open up
the database in 2003 it will display fine in grid view but won't display in
2007.

 

[NTC]

don't know

I suppose you could copy that sql code into a brand new query, run it, and
see if it will display the design grid or not....just a possibility....

 

[emc]

My queries are quite simple and I have never had a problem working in 2003.
This is totally a bug in 2007.

To open any query in SQL view, select the query, go to the Home tab on the
Ribbon and the first button is called View, select SQL from the list. I can
also open in Datasheet view, in other words the query will run fine. I can
also open all tables in any view as well.

This is a known bug but there is no fix. I can open any single-table query
in design view, just not a query based on 2 or more tables. This issue has to
do with joins, since I also encounter the same error when attempting to open
the Relationship window.

 

[emc]

You simply go to the Home tab and select View from the Ribbon and choose SQL.
How do I setup a network backend as a trusted location?

 

[emc]

My queries are rather simple. Two tables, inner join, based on less than 10
fields.
My database works fine in 2003, it's a 2007 bug.

 

[emc]

You can also get to SQL View by opening the Query in datasheet view (running
the query) and go to View on the menu and SQL will be listed there.

 

[NTC]

while there are some complex sql that can not be displayed via the grid
inherently due to their complexity, my Access07 shows the design grid
perfectly ok in multi table queries that are not complex.....

that's why I suggested importing the sql text into a new query...or perhaps
even into a new db...

if it is a known bug - that is news to me....not sure the MVPers monitor a
dialog this old...you might want to reintroduce your topic...

 

[emc]

Hello,

If you google this issue, you will find many others having this same
problem. I called Microsoft and spoke to a Tech support person and they
looked it up and it turns out they listed this as a bug back in May 2008
(reference number 37769).

 

[NTC]

strange - I have absolutely no problem.

but then I am in mdb...looking at apps built with 03; am not using accdb.
I wonder if that makes a difference.

I know Allen Browne has warned that key fields must be included in queries.

If you rebuild the query from scratch will it be viewable in grid?

 

[emc]

I've tried converting to ACCDB as well as staying in MDB; it doesn't make a
difference.
But not all queries require the key fields... some of my queries include
them, some don't... I still get the error. Some GroupBy queries cannot have a
unique field anyway.

I've tried recreating my DB and importing /re-linking all objects, but I get
the same result. As for recreating the query... I have tons, and this is only
one DB out of many, it would not be feasible.

The only alternative I have read about is to continue working in 2003.
Thanks!