[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS Access Database Crash - Need Help!

Posted on 2013-12-09
10
Medium Priority
?
483 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

656 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