" is not a valid name. Bug in Access 2007 (Another question)

[MJ]

My department recently upgraded to Access 2007, with SP2 installed. I tested
all of my responsible databases without any issues and then yesterday I run
smack into this BUG. I have read the numerous responses of workarounds that
have worked in some cases and others that seem to have no impact.

I know that Microsoft has to have people reviewing these discussion groups
and the issues brought up. Can anyone tell us if they are working on a fix
to this problem?

If they are working on it, when can we expect a permanent fix?

Thank you.

 

[Klatuu]

There is no way to know the answer to your question. This group is only
hosted by Microsoft. It is used by we who use the products. No one here has
access to that information.

 

[Tom van Stiphout]

On Fri, 31 Jul 2009 06:33:01 -0700, MJ <[email protected]>
wrote:

Each version of Access is a bit different from the previous. Sometimes
it involves strenghtening some rules resulting in errors where you
previously could get away with it.
You could wait for an upgrade (don't hold your breath - the team is
hard at work on Office 2010), or you could tell us more about your
issue, and we might be able to suggest a way to fix it.

-Tom.
Microsoft Access MVP

 

[MJ]

Tom,

My problem with AC07 is exactly the same as those posted in David P's
question of the same name (dtd 01.31.2008), plus Elaine's reply to same (dtd
03.10.2009).

The queries impacted with this problem all appear to have the same thing in
common: (1) two or more tables in them; and (2) appear to function normally
EXCEPT that you cannot get the DESIGN VIEW on them without the error (" is
not a valid name. Make sure that it does not include invalid characters or
punctuation and that it is not too long.)

As with similar threads on this error;
(1) I have checked the location of the database and it contains NO unusual
characters;
(2) It doesn't have any issues with MISSING references;
(3) I have created a NEW empty database (both 2000-03 and 2007 formats) and
imported all of the needed object with the same error results; and
(4) I was even able to develop a NEW query from scratch within the existing
database which I was able to flip between DESIGN and SQL and DATASHEET
without any problems UNTIL I saved it, closed out of it, and tried to reopen
the new query. The same stupid error!

This problem is NOT only with this one query, but several within this
database (and my educated guess, based on the number of ppl reporting to have
this same problem, is that it will appear in many other databases).

The one thing that I have discovered (by reading through threads), was
mentioned by Elaine (dtd 03.10.2009), the queries that get this error have
two or more tables in them. Of course these same said queries tend to be
some of the more complex queries. It doesn't appear to impact their
processing (they appear to work correctly and you can access the SQL view of
them), it makes maintenance an absolute nightmare for the non-SQL expert.

As a software professional and user, I applaude the MS Staff for their focus
on the upcoming new release.

[CHANGE OF MIND] I will leave it at that, I had originally started to write
something else that would not have been taken nicely by some people. I think
that someone at Microsoft needs to take notice of this ongoing problem...

Thank you for your time and patience to read my venting...

 

[Tom van Stiphout]

On Fri, 31 Jul 2009 08:26:01 -0700, MJ <[email protected]>
wrote:

If you email me a zipped copy of your db (my .no.spam trap can easily
be avoided), stripped down to the bare essentials, with some
instructions, I will run it on the Access 2010 version I have, and I
will give the team "constructive but strong" feedback if the problem
still exists.

-Tom.
Microsoft Access MVP

 

[Tom]

I've been having the same problem with somebody else's computer, and
did all the same checks. It finally dawned on me that they had set
the user up as "Jim's Computer" - so if you only started looking for
special characters at the "My Documents" level you'd miss the
apostrophe in "Jim's".

Sounds like you've been pretty vigilant, but sometimes its the easy
stuff that bites you.

Good luck - I sympathize with you!

Tom

 

[Clifford Bass]

Hi MJ,

As plain Tom hinted, an apostrophe anywhere in the path and/or name of
the back end database will cause the error. I have reported this to
Microsoft. The work-around is to remove the apostrophes and then relink the
tables.

If you do not have access to an earlier version of access, you can use
this code to update the links:

Public Sub DealingWithApostrophesNew()

Dim dbCurrent As DAO.Database
Dim tdCurrent As DAO.TableDef

Set dbCurrent = CurrentDb
For Each tdCurrent In dbCurrent.TableDefs
With tdCurrent
If InStr(1, .Connect, "'") > 0 Then
.Connect = Replace(.Connect, "'", "")
.RefreshLink
End If
End With
Next tdCurrent
Set tdCurrent = Nothing
Set dbCurrent = Nothing

End Sub

Clifford Bass

 

[Tom]

plain Tom

Not sure how I should take that - hopefully its a description of my
name in this group and not my personality.. I guess it could have been
worse - you could have called me plain *old* Tom. ;-)

Plain Tom

 

[Clifford Bass]

Hi Tom,

Hmmm, I think you took it in the spirit it was used. I suppose I could
have used "the Tom without any other name(s)" .

Clifford (two names) Bass