Solved

MS Access Database Crash - Need Help!

Posted on 2013-12-09
10
434 Views
Last Modified: 2013-12-13
I am using Access 2K on a Windows XP Platform.
I use Split Database with Access on both sides.

I have a back end database (mdb) with about 400 tables that has crashed.
When I open the back end everything seems normal, except that all 400 tables are missing. When I switch to Table View.  Forms, Modules, and Queries are all fine.
Only the tables are missing.

Compact and repair, in addition to the MS Access External Compact and Repair Utility do not work.  I receive an error that states I don't have permission to modify one of the tables which is a system table.

We make regular backup of the back end, however, when we checked our backups we found that we have to go back six weeks before we have a good backup.  Since the front end has been working fine, no one new there was a problem.

Fortunately, all tables are linked to the front end, and I can still view and query the tables from the front end.

I have attempted to recreate the 400 tables in a new Access MDB file, using MakeTable queries, and this works, except the new table General properties are not recreated.

For example, the DefaultValue property, and Description property of each field are blank in the new tables.

I haven't checked, but I would expect to have also lost Format, Mask,  Caption, and AllowZeroLegnth a not be as important.

My question is:

Is there a way to recreate tables from the front end of an Access
Database into an external blank database, that allows me to also recreate the table properties, or is there a way I can update the tables in the newly created database to match the table properties in the back end?
0
Comment
Question by:pcalabria
  • 4
  • 3
  • 3
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39705814
<<
I have attempted to recreate the 400 tables in a new Access MDB file, using MakeTable queries, and this works, except the new table General properties are not recreated.
>>

If you haven't done so already, try *importing* your tables into a new database using the Get External Data feature.  This is a common approach in handling corruption which should maintain your complete table structures.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39705816
1. Create a new DB container.
2. From your backup from six weeks ago that's sound, import all tables, but import structure only (advanced options button).
3. Link to the tables in the BE from the new DB.
4. Run append queries to bri ng the data in.   On this last, you may want to write some code to do the 400 tables.

I might also try giving one of the Access repair utilities out on the net a shot.  However if you do this, do it on a copy of the DB.

Jim.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39705821
In Access 2003, if memory serves me right you would use:

File --> Get External Data --> Import...

Then browse to the database that you want to import from and select all of the tables and/or other objects you want to import into your new database.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39705826
<< everything seems normal, except that all 400 tables are missing >>

Also, just on a wild notion... did you create this database, or was it created by someone else?

If it was created by another developer, have you verified that these are not simply 'hidden'?

Tools --> Options --> View --> Make sure that the box to show hidden objects is checked.
0
 

Author Comment

by:pcalabria
ID: 39705833
Thanks Miriam and Jim for the quick responses!

I think Jims approach will work as the Get External Data feature yields a "Missing  Record" error.  Miriam, yes I created the database myself, and nothing is hidden.

Jim, I'll give your approach a try and report back.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pcalabria
ID: 39706000
I'm receiving errors I don't understand while importing the tables.  Specifically, I'm receving errors that state some of the tables have too many indexes.

The tables that yield the error have 25 indexes, while tables which import correctly, have 23.
I thought Access allows up to 32 indexes for each table.

Any idea whats going on?
0
 
LVL 57
ID: 39706041
The limit indeed is 32 indexes on a table.

Which part of the operation are you doing?  Creating the DB from the backup copy or actually importing the data from the corrupt DB?

In general though, it sounds like one of your msysObject table has gotten messed up and the properties of the table may not be valid.

So JET might think there are more indexes that the limit on a table already.

Jim.
0
 

Author Comment

by:pcalabria
ID: 39706269
Jim,

The first 201 tables that I am trying to import are named 0000, 0001, 0002, ...0200.

These tables should be identical in structure as they contain customer inventory.   The only thing different should be the data in the tables... for the most part.

The Inventory tables were originally setup with 23 indexes, although we tables grew large I had to add two new indexes for a total of 25.  I only went back to add these indexes to a few of the older tables, the ones with performance issues, and then all new tables created have 25 indexes.

 All the tables with 23 indexes import, while the tables with 25 indexes cause the too many indexes error message.  

One thing I left out, I am also receiving another error message for every Inventory table I try to import.  "Cannot Place this validation expression on this field".

In addition to the 201 Inventory tables, there are another 200 tables that all import without any error messages, except for one table, called quotes, which does have 32 indexes.

The Quotes table gives me the too many indexes message as well, which surprised me, because the table seemed to work find in the application.

From your response, I would think the next thing to do is to try importing from an older backup?   I'll give that a try, but do you have any other ideas or insight into the Cannot Place error message described above?

Thanks,

P.
0
 
LVL 57
ID: 39706406
What might be working into this is relationships and enforcement of RI.  If you enforce RI on a table, Access (JET) creates a hidden index on the many side of the join.  This counts towards the 32 index table limit, but is not obvious.

<<Cannot Place error message described above?>>

 With a corrupted DB, anything is possible.  In general, you create a new structure, then try reading in the records and recovering as much as possible.

 Your other approach is to try one of the repair utilities.  You also might try a hybrid approach; create a new DB off a backup, then try and import of the data off a repaired copy of the current DB.

Jim.
0
 

Author Closing Comment

by:pcalabria
ID: 39716885
Problem solved!
Thank  you to everyone for your help.

Special thanks, once again, to Jim

(I still have the index issue to deal with but will open a separate question to solve that problem.  23 indexes work fine, 25 gives a too many error message, not using RI)
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now