Unable to Delete/Copy Objects in Access 2010

I am opening up a .mdb Access file in Access 2010.  At some point in the past it got corrupted in some minor way where it won't allow me to delete or copy any objects (tables, queries, etc.)

This is not a read-only database.  I can copy/delete tables programmatically.  I can see all of the objects and every other function is available via the ribbon.

I have seen this phenomenon with other databases, both .accdb and .mdb.

In hunting around the internet, the only solution seems to be to create a new database and import all of the objects from the old database into the new database.  But there are two problems:

1) This low-level corruption is happening quite often to a variety of databases.  Creating new databases and importing is just too much overhead.

2) We have some linked tables that will not copy over.  They are pointing to tables that have more than 2Gbytes of records in them.  Because of an obscure error with ODBC and how Jet creates linked tables, there is no way create or copy a link that points to a table that has more than 2Gbytes of records.  Links that pointed to tables before they grew beyond 2Gigs still work but creating new ones does not.

Does anyone know of any low-level Access properties that I can set to regain the ability to delete/copy objects?
koughdurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<
1) This low-level corruption is happening quite often to a variety of databases.  Creating new databases and importing is just too much overhead.
>>

1.  I would replace the work group file (and that applies to both .accdb's and .mdbs)

2.  Make sure A2010 is patched to current levels.  Access 2010 had numerous problems before SP1.

 It sounds like the ACL's in the DBs are getting messed up.   The ACL (Access Control List) are what controls the permissions for each of the objects in the DB container..

 There are no low level repair utilities outside of those used by recovery companies that have developed their own and the internal JET/ACE format has never been documented.  You may want to try submitting one to Wayne Phillips at www.everythingaccess.com and see if it's an easy repair and what he might charge

<<  Because of an obscure error with ODBC and how Jet creates linked tables, there is no way create or copy a link that points to a table that has more than 2Gbytes of records. >>

 I don't understand this...a linked table is nothing more than a tabldef object with a connect string.   JET should care less about the number records or the size.   The only issue I'm aware of is in linking to a table with more that 32 indexes.   It may however be choking when  it opens the table to get the schema.   What's the error your getting?

 But in any case,  you should be able simply create the tabledef object and set the connect string programmatically

Jim..
0
koughdurAuthor Commented:
Jim,

Thanks for the info on Wayne Phillips.

We stopped using workgroups a long time ago.  So I don't believe there are any workgroup files to fix.  We control access/privileges with Windows OS authentication.

With regard to the ODBC error:  when Jet creates a link to an Oracle table it collects certain statistics on the table.  This includes the number of rows in the table.  It shouldn't need to do this, but it does it anyway.  In any case, it tries to put the value it finds into a variable, but that Jet variable is not capable of storing any value greater than 2G, i.e. 2^31.  Evidently the variable is a 32-bit signed integer.  So the link creation fails.

Our solution for this is to create a very simple database that contains only the links to those tables that are above or approaching 2G rows.  We're going to have the DBA make a copy of the large tables with just a few rows in them, rename the tables so the the small copies have the names of the large tables, create the links, then move everything back to the way it was.

Then we'll never open the simple database ever again.  Every time our application gets corrupted, we'll just make a copy of the simple DB and create the application from scratch by importing all the tables, queries, forms, etc. into the copy.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<We stopped using workgroups a long time ago.  So I don't believe there are any workgroup files to fix.  We control access/privileges with Windows OS authentication.>>

 There is always a workgroup file in use, even with A2007 and up where workgroup security was "stripped out".

 What Micorsoft did is when converted to .axxx, is simply remove all the ACL from the db except for the default ones.     You'll find a SYSTEM1.MDW somewhere on your station.

<<It shouldn't need to do this, but it does it anyway.  >>

 Ah, so not 2GB in size, but in count.   Your the first I've ever heard of bumping into that!   I didn't understand that from the question.  It does that for query costing by the way.

 Sounds like you have a good work around for the time being.  Make sure you select your last comment there as answer.

Jim.
0
koughdurAuthor Commented:
Jim,

I located the SYSTEM.MDW file on my computer and renamed it.  Access created a new one, but that didn't solve my problem as I had hoped.  I also sent an email to Everything Access to see if they know a simpler way to fix databases that get into the "don't allow delete" mode.

Thanks for your comments.
0
koughdurAuthor Commented:
No better solution was provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.