Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access database crashed, please help.

Posted on 2013-11-23
11
Medium Priority
?
297 Views
Last Modified: 2013-12-09
Hello Experts, please help me.

I use a MS Access 2K split front end/back end database.
The back end has become corrupt.

I can still open the database and access the modules and forms, however, none of the tables are visible.  As a result, I can not edit, delete, or modify any table in the back end.

I have attempted to Compact and Repair but receive the following error:
Could not create; design permission for table or query 'MSysAccessObjects'

I also tried the external compact and repair utility on Microsoft's website and it also failed.

I attempted to use a routine to cycle through the tables and then transfer them to an external database and the operation failed after displaying a "Record is deleted" error.

The data in the linked tables can however be viewed from the links in the front end.
I wrote a routine to cycle through each table in the tables collection and then execute a MakeTable query in a blank database, which successfully created new tables with my data.  The query however, does not move indices, or anything else that I might need.

As a result, the use the new data I would need to create every index, which would take a very long time.  There are approximately 500 tables in the database that is corrupt.

Can anyone help?
How can I repair the corrupt database, or copy indices and anything else I might need to use the recreated data?

Thanks
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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39671916
If Compact & Repair did not work, then you are looking at having to engage a data recovery service, and that can get very expensive.

You should count yourself lucky that your were able to retrieve the data, and set about rebuilding the indices.  That will be tedious, but doable.

And if you haven't already, start planning for at least nightly back-ups, stat!

:)
0
 

Author Comment

by:pcalabria
ID: 39672058
We have been making nightly backups, however the backup file for the past month all exhibit this same problem.

 Are you saying there is no way to copy the indices?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39672092
Not that I am aware of
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 38

Expert Comment

by:Jim P.
ID: 39672147
The indices are specific to the mdb file and aren't recoverable.

There are approximately 500 tables in the database that is corrupt.

Once you get above about 50 tables you really need to look at design or a different DB option, like SQL Server. Even with express you can find substitutes for doing the backups.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 39672158
This is a long shot, but this worked for me with two healthy databases.

1) Put this code into a new VBA module in the "new" database

Sub GetTheIndexes()
    
    Dim tdDest As DAO.TableDef
    Dim tdSource As DAO.TableDef
    Dim indDest As DAO.Index
    Dim indSource As DAO.Index
    Dim fldSource As DAO.Field
    Dim AccObj As Access.Application
    Dim dbSource As DAO.Database
    
    Const SourceDbName As String = "Q_28302225_A.mdb" 'modify as needed
    
    ' New Access instance to open the original database
    
    Set AccObj = New Access.Application
    
    ' Open original database
    
    AccObj.OpenCurrentDatabase CurrentProject.Path & "\" & SourceDbName 'modify as needed
    
    ' Loop through each table in the new database.  If it's not a system table, try to
    ' build the indexes
    
    For Each tdDest In DBEngine(0)(0).TableDefs
        If Not tdDest.Name Like "MSys*" Then
            
            ' Get corresponding table in original DB
            
            Set tdSource = AccObj.DBEngine(0)(0).TableDefs(tdDest.Name)
            
            ' Loop through indexes on that table
            
            For Each indSource In tdSource.Indexes
                
                ' See if thre index already exists on the destination table.  If it
                ' does, then skip it.  If not, create it
                
                On Error Resume Next
                Set indDest = tdDest.Indexes(indSource.Name)
                If Err <> 0 Then
                    Set indDest = tdDest.CreateIndex(indSource.Name)
                    With indDest
                        For Each fldSource In indSource.Fields
                            .Fields.Append .CreateField(fldSource.Name)
                        Next
                        .Clustered = indSource.Clustered
                        .Primary = indSource.Primary
                        .Unique = indSource.Unique
                    End With
                    tdDest.Indexes.Append indDest
                    Err.Clear
                End If
                On Error GoTo 0
            Next
        End If
    Next
    
    Set fldSource = Nothing
    Set indSource = Nothing
    Set tdSource = Nothing
    AccObj.CloseCurrentDatabase
    AccObj.Quit
    Set AccObj = Nothing
    Set indDest = Nothing
    Set tdDest = Nothing
    
    MsgBox "Done"
    
End Sub

Open in new window


2) Modify path/file name as necessary

3) Run it

If the original database is so far gone that VBA cannot hit it, then you are stuck with having to do the manual recreation.

I second jimpen's curiosity about the number of tables.  500 seems rather a lot, and is probably an indicator that you need to rethink your design.
0
 

Author Comment

by:pcalabria
ID: 39672653
If I ever get this problem solved I can easily move half of the tables into a different Access mdb file.  I am also using SQL Server 2008 R2 but have not attempted to convert these tables yet, do to the effort required to find and re-write the SQL statements!

We are making lots of progress, but still have a way to go.

The code provided by MathewsPatrick starts to run but fails after it successfully creates the first five indexes of the first table.

(By the way, the first table name is 0000 which occasionally causes problems unless I enclose it in brackets!)

The problem is with the following line:
Set indDest = tdDest.Indexes(indSource.Name)
the sixth time through the loop.

The first time through the loop indSource.Name is set to "AlternateSearchNumber" which is the correct name of the first index in my 0000 table.

The second, third, fourth, and fifth time through the results are as you would expect, indSource.Name is set to the correct index name, and the index is correctly rebuilt.

The sixth time through the loop things get weird.   indSource.Name is set to "code", which is the correct name of the sixth index (as verified by viewing the index from the front end), however, the line causes an "Object Not found in this collection" error.

I thought the next step would be to write code to loop through all the indexes in the collection to see what indexes are listed there, however, I don't know how to write this code.


Any ideas? I feel that the code provided is bringing a solution so close!

Thanks
0
 

Author Comment

by:pcalabria
ID: 39672657
More info:

I figured out how to loop through the index collection and I used a debug.print to make a list of all the indexes I the current table:

AlternateSearchNumber
AlternateSearchNumber2
AvailCode
BaseNumber
CCode
code
ConditionCode
DataSheet
DateCode
DocName
IDEA1010_By
IDEA1010_Date
IDEA1010_Rev
LifecycleCode
LineID
Location
Manufacturer
ManufacturerFull
NewIndex
OrdernoVerified
OrderNumber
OriginID
PartID
PartIDNo
PopCode
PrimaryKey
QtyCode
Quantity
SearchNumber
TempID
ValueBasisCode

It does seem that "code" should be part of the collection.
0
 

Author Comment

by:pcalabria
ID: 39673466
Can anyone help?  I'm trying to get this fixed before Monday morning.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39673610
I believe that Code is some kind of keyword.

So try skipping that particular one and see if the rest will run.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39685030
It seems that if you can view the linked data from the "injured" database.... maybe you can IMPORT the tables.

I'd try creating an new accdb (mdb) and try to import as many tables as possible.

??

Scott C
0
 

Author Closing Comment

by:pcalabria
ID: 39705764
Thank you.
The long shot worked!

The GetTheIndexes() sub successful allowed me to copy the indexes from one database to the second.

I was not able to import the tables or indexes from the injured database,
but I was successful at importing the indexes from the last good back up.

Thank you so much.
I'm not out of hot water yet, because I've realized I have another problem due to the fact that the tables created with the MakeTable query did not bring with them table properties, but I will open a new question on this as it is another issue.

THANK YOU!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
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: …

610 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