?
Solved

MS Access Database Crash - Need Help!

Posted on 2013-12-09
10
Medium Priority
?
477 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 

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 58
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 58
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

How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

770 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