Unable to delete linked table in Access 2007

[Ed Hansberry, MS-MVP/Mobile Devices]

Windows Vista (no service pack)
Office 2007 Pro, SP1, all hotfixes
Using 2 .accdb databases, newly created in 2007, not upgraded
from MDB files.


After compacting a database, I saw a new table called
MSysCompactError with for fields and 1 record:

ErrorCode: -1002

ErrorDescription:'|' is not a valid name. Make sure that it does
not include invalid characters or punctuation and that it is not
too long.

ErorRecid:

ErrorTable: MSysRelationships

I looked in the hidden tale MSysRelationShips and found nothing -
no records. So I went to the relationship view.

It gives an error (and the " is part of the error message)

" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.

There are NO relationships set up in the Relationship View.

One by one I started deleting tables and recompacting to see
which might be the culprit. I finally got to a linked table to
another 2007 ACCDB file. It won't delete. Deleting it just
returns the following error message:

" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.

My temporary solution was to go to the source database, copy that
table to a new ACCDB and delete all but one record, then go back
to my original database, use Linked Table Manager and link to the
new database, which it let me do, then delete the linked table.

Any clues as to what the problem could be? The linked table is
called tblProduct Master and has no "|" characters in any of the
19 text field names.
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -

 

[Stuart Bratesman]

Ed:

I just posted a message yesterday about a similar problem ("Access
2007: Can't delete linked tables in secured database") that seems to affect
both Access 2003 and Access 2007.

You experienced the problem with ACCDB files. I found that the
problem also occurs with .MDB files.

What I've found is that you cannot delete a table link in Access 2003
or Acces 2007 if the table you are linking to is housed in an Access database
protected by user-level security. The bug occurs even if you are logged-in
as an admin under the USER ID and PASSWORD for the protected database's
user-level security Workgroup.

It does not matter whether the database that has the link you are
trying to delete is protected or unprotected.

This error does not occur in Access 2002.

If you try to delete the link using the docmd.DeleteObject method in
Visual BASIC, it gets even worse. The Visual BASIC code exits the sub,
without warning and without displaying any error message, at the line that
has the docmd.DeleteObject command that is trying to delete the table link.

This has to be a bug and not a security feature.

 

[Ed Hansberry, MS-MVP/Mobile Devices]

Ed:

I just posted a message yesterday about a similar problem ("Access
2007: Can't delete linked tables in secured database") that seems to affect
both Access 2003 and Access 2007.

You experienced the problem with ACCDB files. I found that the
problem also occurs with .MDB files.

What I've found is that you cannot delete a table link in Access 2003
or Acces 2007 if the table you are linking to is housed in an Access database
protected by user-level security. The bug occurs even if you are logged-in
as an admin under the USER ID and PASSWORD for the protected database's
user-level security Workgroup.

It does not matter whether the database that has the link you are
trying to delete is protected or unprotected.

This error does not occur in Access 2002.

If you try to delete the link using the docmd.DeleteObject method in
Visual BASIC, it gets even worse. The Visual BASIC code exits the sub,
without warning and without displaying any error message, at the line that
has the docmd.DeleteObject command that is trying to delete the table link.

This has to be a bug and not a security feature.

Well, that is not the issue here. User level security is gone in
ACCDB format, and since both of these databases are new ACCDB
creations, not upgrades of MDB files, there is no possibility of
that being the issue, and that wouldn't explain the error
messages either. It may be somehow related to come checks done at
deletion though.
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -

 

[Stuart Bratesman]

Ed:

You're right. It should have occurred to me that User Level Security
is missing from databases created in the new .ACCDB file format. However,
I've noticed that Microsoft has developed a new an more secure database
password system for .ACCDB databases, and I wonder if that new system has the
same cannot-delete-linked-table bug that occurs in user-level secured .MDB
databases.

Did you password protect the .ACCDB file that houses the table to which
you have linked, or is it not password-protected?

From the appearance of the

" is not a valid name. Make sure that it does
not include invalid characters or punctuation
and that it is not too long.

error message, it seems as if the name of the table link has either been
converted to NULL or to an empty string.

 

[Ed Hansberry, MS-MVP/Mobile Devices]

Ed:

You're right. It should have occurred to me that User Level Security
is missing from databases created in the new .ACCDB file format. However,
I've noticed that Microsoft has developed a new an more secure database
password system for .ACCDB databases, and I wonder if that new system has the
same cannot-delete-linked-table bug that occurs in user-level secured .MDB
databases.

Did you password protect the .ACCDB file that houses the table to which
you have linked, or is it not password-protected?

From the appearance of the

" is not a valid name. Make sure that it does
not include invalid characters or punctuation
and that it is not too long.

error message, it seems as if the name of the table link has either been
converted to NULL or to an empty string.

No passwords of any sort. I am not sure what the problem is. Your
speculation is as good as anything I've come up with to explain
it.
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -

 

[Stuart Bratesman]

Ed,

That's interesting that you could copy the target table to a new .ACCDB
database, delete all but one row, update the table link to point to that new
table and then be able to delete the link.

Had you unsuccessfully tried to delete the link to the new table location
before you had deleted all but one record in the new target table? If so,
this might suggest that it had something to do with the data stored in
one-or-more of the records you had deleted.

In my experience trying to delete links to multiple tables in a user-level
secured .mdb database, the

" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.

error message occurred for each and every table link that I had tried to
delete, but I had no problem deleting the table links to tables stored in an
unsecured database.

I experimented to test the hypothesis that the problem has something to do
with password protection. I created one .ACCDB database with a single table
("Table1"), added 5 rows of data, and then used the "Encrypt with Password"
command on the Database Tools ribbon to secure it.

I then created a link to that table from a second .ACCDB database and then
tried to delete the link - and I was able to delete it without an error
message.

My one hope is that someone from the Microsoft Access development team may
become aware of these posts and then be able to fix the problem. I have not
been able to find an easy method to submit bug notices to Microsoft.

Good luck!

My way around the problem has been to create a new blank database and import
all the objects from the old one, excluding, of course, the table links I had
been trying to erase in the first place. What a kludge! (NOTE: I have
heard most people pronounce "kludge" in a way that rhymes with "huge", but
I've been trying to promote a pronunciation that more appropriately rhymes
with "sludge".)

 

[Grappler]

I have been suffering exactly this problem with precisely the same error
message since I started grappling with the Access 2007 format. I have never
used password protection nor encryption of tables or database, yet I cannot
delete a linked table once the link is established. How can we find an
answer to this problem?

 

[Stuart Bratesman]

I recently discovered (by accident following much searching) a Microsoft
Office Feedback web page at:
https://feedback.office.microsoft.com/default.aspx?productkey=office2007

I have already submitted a description of the

" is not a valid name

problem, and if you and others were to do likewise, perhaps it might elevate
Microsoft's awareness of the problem.

Meanwhile, I have now taken to importing tables instead of linking, whenever
feasible, and re-importing to refresh the data. It's a bit tedious, but it
works. Of course, the only way to edit the original data is to go back and
do it in the original database.

Stuart Bratesman, MPP
Health Policy Analyst
Muskie School of Public Service
University of Southern Maine
Portland, Maine

 

[Grappler]

Stuart - thank you for details of the feedback page - I shall certainly add
my error message and hope that someone comes up with a solution. Sadly,
importing tables without being able to modify the source is not an option for
me.

 

[Ed Hansberry, MS-MVP/Mobile Devices]

I have been suffering exactly this problem with precisely the same error
message since I started grappling with the Access 2007 format. I have never
used password protection nor encryption of tables or database, yet I cannot
delete a linked table once the link is established. How can we find an
answer to this problem?

I don't have a solution, but do have a fix that will allow you to
delete the table link.

1) create a new file.
2) Import (not link) the source table into this file and close
the database.
3) Open the problematic database and use the Link Table manager.
Check the "always ask" box and change the link to the new table.
4) Now delete the link.
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -

 

[Clifford Bass]

Hi All,

I have been able to replicate this bug. If you link to a table in a
database whose name includes an apostrophe (') you are not able to delete the
table link and you get the '' name is not valid... error.

Clifford Bass

 

[Klatuu]

The message says the name is not valid. That is correct. an apostrophe in
any object name will cause problems. It is not a bug. Rename the table.

Access will allow bad names to be created, but will have trouble with them
at run time. To minimize this sort of problem, here are some basic rule:

Use only letters, numbers, and the underscore for names. Do not in clude
spaces or any other special character.

Do not use any Access or Jet reserved words in you names. Some examples
are:
Date, Year, Month, Description, Name, Property, Usage, Prior
This is only a very short list of examples.
To avoid reserved word conflicts, it is a good idea to use two words that
will avoid a name issue and give info on the use of the name. Use either
camel case or words separated with an underscore. For example:

EntryDate, Property_Description

You can also download a free untility that will check your database for name
issues as well as other common mistakes:

http://allenbrowne.com/AppIssueChecker.html

Good luck!

 

[Clifford Bass]

Hi Klatuu,

I was not entirely clear so I believe you misunderstood what I was
stating. The problem is with the perfectly valid back-end database when its
name contains an apostrophe. For example: Use "Clifford's Data.mdb" as the
back-end with a table "tblMy_Table" and "FrontEnd.mdb" as the front-end
database that links to "tblMy_Table" in "Clifford's Data.mdb". Attempting to
delete the linked table out of "FrontEnd.mdb" will give the '' is not a valid
name... error. The error also exists with Access 2007 databases. I have
submitted the steps to replicate this bug to Microsoft's Access support
people.

Regardless of that, I believe you are at lease partially incorrect in
saying that a table (or other object) name with an apostrophe can cause
trouble. I have been using them for quite a few years without any trouble
whatsoever in table and query names. And possibly in some types of objects
as well.

Sincerely,

Clifford Bass

 

[david]

Note: By default, I convert link paths to "short" form (8.3) using
the Windows API when I do a re-link (using code).

I've left the option to link on the "long" path because the "short"
path is not guaranteed to be available or remain the same, but
in practice that has never caused a problem.

I confess that the main advantage is that you can fit a longer path
into a 60 character display field, but we did it because of some
obscure problem like the one reported here.

(david)

 

[Klatuu]

You have just been lucky. In that an apostrohe can be used as a string
delimiter in Jet SQL and SQL Server syntax, you could end up with a problem.

Just because nothing bad has happened doesn't justify bad habits.

 

[Clifford Bass]

Hi Klatuu,

I do not understand why I am being drawn into an argument with you when
all I wanted to do was help others who encountered this bug in Access 2007.
(By the way, for those who cannot get into the design mode of your queries,
an apostrophe in the back-end database's file name can be the cause.) But I
feel I must respond one last time to you.

Klatuu, you are unjustified in calling my "habits" bad when you do not
know me and when Microsoft Access's own documentation clearly states that
using those characters is quite valid. From Access 2003's help:

===============================================

Guidelines for naming fields, controls, and objects

Names of fields, controls (control: A graphical user interface object, such
as a text box, check box, scroll bar, or command button, that lets users
control the program. You use controls to display data or choices, perform an
action, or make the user interface easier to read.), and objects in Microsoft
Access:

Can be up to 64 characters long.
Can include any combination of letters, numbers, spaces, and special
characters except a period (.), an exclamation point (!), an accent grave
(`), and brackets ([ ]).
Can't begin with leading spaces.
Can't include control characters (ASCII values 0 through 31).
Can't include a double quotation mark (") in table, view, or stored
procedure (stored procedure: A precompiled collection of SQL statements and
optional control-of-flow statements that is stored under a name and processed
as a unit. The collection is stored in an SQL database and can be run with
one call from an application.) names in a Microsoft Access project (Microsoft
Access project: An Access file that connects to a Microsoft SQL Server
database and is used to create client/server applications. A project file
doesn't contain any data or data-definition-based objects such as tables and
views.).

====================================================

And yes, I have long been cognizant of the migration issues associated
with using Access's more liberal naming conventions.

Sincerely,

Clifford Bass

 

[Clifford Bass]

Hi David,

That sounded like a good work-around until Microsoft fixes the bug.
Unfortunately for me, the 8.3 for of the file I am dealing with still
contains the apostrophe (i.e. CLIFF'~1.MDB for Cliff's Data.mdb"). If I do
the link manually through the graphical interface, pasting in the shortened
file name, Windows expands it back out the the long name before passing it
back to Access. And if I use

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\CLIFF'~1.MDB",
acTable, "tblMy_Table", , True

it complains with the '' name is not valid... error again. So there is a
bug there also. Tested it in Access 2003 also and it gave the same error as
in Access 2007.

At least I can still do the stuff I need in this particular database
using Access 2003.

Thanks much for the idea,

Clifford Bass

 

[Klatuu]

The text you does not inclued naming for tables.

I neither agree with nor follow Microsoft's Suggested naming conventions.
Because it is allowed does not make it a good idea.

I don't consider this a bug. I consider it a mistake on the part of
whomever used an apostrophe in a name.

I am not trying to drag you into an argument. I am stating my opinions
regarding naming conventions.

Hi Klatuu,

I do not understand why I am being drawn into an argument with you
when
all I wanted to do was help others who encountered this bug in Access
2007.
(By the way, for those who cannot get into the design mode of your
queries,
an apostrophe in the back-end database's file name can be the cause.) But
I
feel I must respond one last time to you.

Klatuu, you are unjustified in calling my "habits" bad when you do not
know me and when Microsoft Access's own documentation clearly states that
using those characters is quite valid. From Access 2003's help:

===============================================

Guidelines for naming fields, controls, and objects

Names of fields, controls (control: A graphical user interface object,
such
as a text box, check box, scroll bar, or command button, that lets users
control the program. You use controls to display data or choices, perform
an
action, or make the user interface easier to read.), and objects in
Microsoft
Access:

Can be up to 64 characters long.
Can include any combination of letters, numbers, spaces, and special
characters except a period (.), an exclamation point (!), an accent grave
(`), and brackets ([ ]).
Can't begin with leading spaces.
Can't include control characters (ASCII values 0 through 31).
Can't include a double quotation mark (") in table, view, or stored
procedure (stored procedure: A precompiled collection of SQL statements
and
optional control-of-flow statements that is stored under a name and
processed
as a unit. The collection is stored in an SQL database and can be run with
one call from an application.) names in a Microsoft Access project
(Microsoft
Access project: An Access file that connects to a Microsoft SQL Server
database and is used to create client/server applications. A project file
doesn't contain any data or data-definition-based objects such as tables
and
views.).

====================================================

And yes, I have long been cognizant of the migration issues associated
with using Access's more liberal naming conventions.

Sincerely,

Clifford Bass

You have just been lucky. In that an apostrohe can be used as a string
delimiter in Jet SQL and SQL Server syntax, you could end up with a
problem.

Just because nothing bad has happened doesn't justify bad habits.

 

[Clifford Bass]

Hi Klatuu,

I am not trying to drag you into an argument. I am stating my opinions
regarding naming conventions.

I can accept that.

I don't consider this a bug. I consider it a mistake on the part of
whomever used an apostrophe in a name.

Unfortunately we have been conversing about two different things, which
may be causing confusion. The bug is that in certain situations Access 2007
chokes on mdb FILE names which contain apostrophes.

Clifford Bass

 

[Clifford Bass]

Hi All,

Got to make a correction here. If I specify the destination in the
DoCmd, it will link to files with apostrophes. Odd, in that I was thinking
that if the optional destination were left out, that it would use the
source's name. Apparently not.

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\Cliff's Data.MDB",
acTable, "tblMy_Table", "tblMy_Table", True

However, even if I specify a 8.3 format file name with this method the
name is expanded out to it's long version (use the Linked Table Manager to
confirm this), thereby causing the same problem that was there in the first
place; the inability to delete the linked table.

Clifford Bass