?
Solved

Ms Access 2007 - Corrupted REPORTS - how to recover ?

Posted on 2012-08-14
24
Medium Priority
?
624 Views
Last Modified: 2012-08-20
I created an mdb with AC 2007 and have dozens of reports in it.  It is a front-end / back-end design with another mdb as the backend data.

Suddenly MOST (if not all) reports quit working -- going after a few seconds to "(NOT RESPONDING)", after which only task manager can cancel MSACCESS.EXE.

Is there a way to recover all of these reports?

I have done Compact and repair on the original MDB to no avail.  I have also created new mdbs and tried importing all objects from the original.  When I do this, tables, queries, macros, and modules can all be opened in design view, but attempting to open ANY report in design view once again places access into "Not Responding"....

Compact and repair on the newly-cloned mdbs has no effect either.

Any suggestions?
-Bill-
0
Comment
Question by:bcreen
  • 12
  • 9
  • 3
24 Comments
 

Author Comment

by:bcreen
ID: 38291877
Also, it seems that attempting to import certain reports into the new MDB also does the lockup (Not responding).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38291904
Try a Decompile
(Make a backup First)
http://www.granite.ab.ca/access/decompile.htm
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38291933
1. It is odd that this only effects Reports and not Forms as well.

I will say that some times you just have to wait out the "Not Responding" message.

May people get the message and automatically presume that the app is locked.
Sometimes the app is actually doing something, and if you wait it out, (it may take a while) it will be fine.
Obviously if the wait is longer than (let's say) 5 minutes, then there is probably something wrong.

2. Also try importing one Report at a time (testing it and running the Compact/Repair utility)..
and try to see if it is just one report that is triggering this issue.

3. Is this and error on all front ends?
Is this an issue with the Development copy of the DB?

JeffCoachman
0
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.

 
LVL 13

Assisted Solution

by:Ryan
Ryan earned 500 total points
ID: 38292044
I suspect that Jeff is correct. Did you change a query/table/linked criteria that is used by these reports that is causing the query to just take a long time?

If you'd got non-TSQL code in queries, like string manipulations, and theres 100k records, or multiple nested, or left joins, bad indices...these could all cause very slow queries, on top of a slow network compounding it.

The annoying part about Access, is when you go to edit these queries, they parse which makes the system freeze again.  If you find that this is the case, create a new version, and link your tables to a local backend so you can at least remove the network latency factor.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38292240
MrBullwinkle,

Yes, I have never seen a "corruption" issue that just affected "Reports".

Typically the "Forms" are effected, since they typically contain the most code.

So your theory may be correct too.

With odd issues like this, it is best to investigate all the possibilities.

;-)

Jeff
0
 

Author Comment

by:bcreen
ID: 38292285
Thanks guys.  No changes to reports or tables.  Just started mis-behaving several days ago.
There are NO reports now that will open in design view ... nor RUN.  I let it sit there for 25 minutes -- after 5-10 seconds it says (Not Responding) in the window header and 25+ minutes later.... no change.  Also, task manager shows 0 % processing from the MSACCESS.EXE task.

I have an older backup with 22 of the 25 reports that I will try to import one at a time to see what happens.  Record sizes in all table is very small: < 10,000 records.

I also have non-TSQL code in a couple queries (of many dozens), but reports have always run instantly -- til now.

This app is used only on one workstation at at time at this point.  So it's both a development copy AND the user-version.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38292608
<I let it sit there for 25 minutes -- after 5-10 seconds it says (Not Responding) in the window header and 25+ minutes later.... no change.  Also, task manager shows 0 % processing from the MSACCESS.EXE task.>
OK, so we can rule out that.
;-)

Let us know what happens when you use the Backup...

Also for fun try the decompile...
0
 

Author Comment

by:bcreen
ID: 38292813
decompile still applicable to ac2007?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 38293035
<decompile still applicable to ac2007? >
LOL
Well, Decompile has always been an "Undocumented" feature...
;-)
So it is just as applicable (or un-applicable, ...depending on how you think about it) in 2007 as it is in any other version of Access.
;-)

Again, *always* do a backup first,
...worth a shot right?

;-)

Jeff
0
 

Author Comment

by:bcreen
ID: 38293229
Will try the decompile on a backup.
BTW, I created an ACCDB with the same name as the MDB, and imported everything except the reports -- which I will get from the older backup (which had 22 of the 25 reports).

Could an ACCDB be more immune to this weirdness?  I've been using Access since v 2.0 and have never run into a problem like this one...   Should I change the backend DATA mdb to an ACCDB as well?

-Bill-
0
 

Author Comment

by:bcreen
ID: 38293323
Well, the decompile and compact-and-repair reduced the size of the already-c-and-r'd mdb from 6,288KB to 3,256KB.  So it did SOMETHING :-)

The ACCDB I created from the MDB (prior to the decompile) is just 12 bytes larger at 3,268KB.

I think I'll begin pulling in the 22 reports from the old backed-up MDB.... into the ACCDB.

-Bill-
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38293423
I am really hesitant to speculate on that, but here goes...

My opinion is that you should try to stay fairly current with your software versions.
Windows and Office share a great many files.
...and MS will only go back a certain number of years and offer full support.

Ms Access 2.0 was released in 1993, so this is almost 10 year old technology.
So you should only upgrade if *every* user will have the newer version installed.

Complicating this further is that although Access 2007 and 2010 share the same format (.accdb), ...files created in 2010, will sometimes not open in Access 2007...
:-O

Ultimately, my guess is that since this DB was working fine up until recently, then keep it in the same (mdb) format.

So try the old backup first and see what happens.
If you get back 75% or the reports and have to re-create the other 25%, then perhaps that won't be so bad.

Oh!, ...and buy the way, also try JetComp (http://support.microsoft.com/kb/273956)

Jeff
0
 

Author Comment

by:bcreen
ID: 38293478
TWENTY YEARS OLD+++   Oh!  I've mis-communicated.  I always try to keep my clients up with the latest Office and Access versions.... I haven't USED Access 2.0 for a couple of decades it seems.
This current problem is occurring under AC2007 (development) and is being used by only one user (my girlfriend) at work -- a large multi-national tire company.... they have Office 2010 as a standard at the moment.  The entire company is on 2010, and likely will be for another 10 years.

I only STARTED out with an MDB (still developed using AC2007) because her home desktop has AC2003, and it turns out we never use her home desktop for this project anyway....  so converting to an ACCDB is now a no-brainer I guess.

Still haven't imported the reports from the backup into the new ACCDB, but do NOT anticipate any problems.
-Bill-
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38293528
...
Sorry, ...
Then I misread your post...
When you stated:
 "I've been using Access since v 2.0 and have never run into a problem like this one..."  
I presumed that you were still in that version...

Sorry for the misunderstanding.

;-)

Jeff
0
 

Author Comment

by:bcreen
ID: 38294206
Roe Roe!  Completely converted over to ACCDBs.  I have TWO accdb's where linked tables are stored.  One table in one, and all others in the other ACCDB.

IMPORTED REPORTS STILL HANG UP -- EVEN JUST TRYING TO IMPORT THEM NOW....

Adding the second linked mdb/accdb was the most recent thing I did, so may have something to do with problems.  In that logic, when program opens I CREATE a link on-the-fly to the single table in one of the accdbs to get some path information.  Then I delete the link.
When the application closes, it re-instates the linked table long enough to store those paths back into the linked table then removes the link again and closes the program.

I suspect the problem with the reports started happening around the same time I made these changes.  My backup -- from before these changes -- works fine with reports.....

The machine I am having problems with is Win7 Home Premium 64-bit with AC2007  -- (32-bit).   Same as the laptop on which the older version of the program works.

What a nightmare!  Any further ideas?
-Bill-

PS: I will try to CREATE a new report from scratch, just to see what happens!
0
 

Author Comment

by:bcreen
ID: 38294238
Even Creating a new report locks up Access.
I have 3 additional modules in the new program that are not in the backup program:  Here is one entire module... note the DECLARATIONS ! ! !   I have other declarations as well, could there be a  32-bit vs 64-bit "conflict" of some sort?

Option Compare Database
Option Explicit

Private Declare Function apiOpenClipboard Lib "user32" _
Alias "OpenClipboard" _
(ByVal hWnd As Long) _
As Long

Private Declare Function apiEmptyClipboard Lib "user32" _
Alias "EmptyClipboard" _
() As Long

Private Declare Function apiCloseClipboard Lib "user32" _
Alias "CloseClipboard" _
() As Long


Function EmptyClipboard()

If apiOpenClipboard(0&) <> 0 Then
Call apiEmptyClipboard
Call apiCloseClipboard
End If

End Function
'----- end of code -----
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38294271
Having dynamic table links may be your issue. Access may not be able to do its automatic checking, or it's failing/hanging up on some background automatic thing.  I question why you need to have dynamic tables, instead of just using a recordset in code or possibly temporary tables.

I'm also wondering if the problem might be something is failing to be accessed and hung up. Or possibly an error is occuring that is being suppressed by On Error Resume/Goto or some similar rogue error checking.
0
 

Author Comment

by:bcreen
ID: 38294311
I do the links immediately upon opening the app and the very last thing before closing the app.  Everything I can do in between seems to work 100%, as long as I don't try to do anything whatsoever with reports.....  even reports against tables native to the 'front end' accdb (there are a couple) fail, locking up the app.

On API declarations, isn't there some way to open a 32-bit vs a 64-bit DLL based on evaluations made at run-time?

As per error checking, I  <<DO>> have a third party enhanced error handling routine, but it's been in there and working properly since day one....  still, I will try checking that out.

Just running a query, then doing Create | report wizard lets me design a new report, but as soon as I try to Finish (to view results) it hangs up....

still thinking API declarations, for some reason
0
 

Accepted Solution

by:
bcreen earned 0 total points
ID: 38294703
Guess what guys.... don't know what the ORIGINAL cause of these problems was, but rearranging the ORDER of the references seems to have solved the problem.  Will know for sure tomorrow.

Thanks so much for all of your help and support!...
-Bill-
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38294822
oK...

As a note, ...many codes for Access need to be modified to work in a 64 bit environment...
0
 

Author Closing Comment

by:bcreen
ID: 38310955
Fellow contributors helped me through a process of elimination, during which I 'stumbled' upon a solution that worked.  that's why I selected my own contribution as the 'proper' solution in this particular case...
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38311548
The order of references could make a difference if 2 references have the same function. The first one in the list is the one that gets run, which may have different effects than the other.

Option Explicit and Option Strict usually prevent that chance of confusion by requiring explicit referencing and don't let the compiler assume for you.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38311926
Just as final followup...
What were the references tat needed a different Order?
0
 

Author Comment

by:bcreen
ID: 38311971
Sure:  I think I had Excel Object Library LAST, and simply bumped it up one position. So the revised order below started working all of a sudden:

Visual Basic for Applications
Microsoft Access 12.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Excel 12.0 Object Library
Microsoft Office 12.0 Object Library

Thanks again for everyone's help!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

807 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