Serious Problem with Access 97

[wjoc1]

I have a serious problem that I was hoping someone could help me with.

I had an old database that was far too large (12mb) so I decided to get rid of all the features I didn't want and reduced it to approx. 2mb. However now when I try and open the reports the program "hangs/crashes". The reports and the queries are the exact same as in the old version and I don't get any error messages so what's wrong? The query for the report runs fine and returns the correct information. It's just that when I try to preview the information in the report it crashes. I have no problem upgrading the smaller database and running it under Office 2000 but when I run it under 97 it crashes?
Eventually I decided to re-write the entire "small" databse using Access 97 and guess what, same probllem again!

I think what is happening is that the small version (~2mb) is being allocated less memory when it is running but because of the amount of information the reports are required to display it's crashing. Interestingly I tried reducing the number of fields the report is supposed to display and it worked immediately. However this solution is not an optionThe large version (12mb) contains identical reports with identical information and this does not have any problems, probably because it is being allocated more memory.

I've tried increasing windows virtual memory, compacting the "small" database and converting it to an MDE file but nothing seems to work. How can I allocate more memory to this "small" version of the database?

Can anyone help me, PLEASE!!! This is driving me crazy. Am I completely off the mark, or what am I doing wrong?

Any help/suggestionns would be greatly appreciated

Liam

 

[The_Doc_Man]

I think we all understand "hang" - not a one of us hasn't run into a nasty little Access report that seems to take forever. "Hang" is as good a term to describe it as any other. Unfortunately, knowing that something hangs is of almost no value by itself. Because the information learned from a hung report is nearly nil.

I'm far more interested in what you described as a "crash" - do you get a message of any type? What are the "crash" symptoms? What can you tell me?

Now, the next thing I think would help in debugging...

You say that when you reduce the size of the report, it works. OK, so exactly what did you take out to reduce the report's size? A particular field? Or does it not matter which field you remove? Is one of the fields you removed an OLE or hyperlinked object?

When you played with virtual memory....

Did you attempt to muck with the Shortcut or primary Icon properties regarding DOS memory? How much physical memory do you have? Have you checked the size of your page/swap file? Can you run the System Monitor (Run >> Accessories >> System Tools) to watch physical memory and swap space usage while you are doing this?

By the way, what version of Windows are you using. Because, as far as I know, Access is not restricted in its memory grants from Windows. It is purely dynamic.

 

[wjoc1]

Thanks for the reply!

Firstly let me clear up "crash", when I try and open one of the reports they hang for ages and the program freezes. When I view it in the Task Manager window it says that the application is 'Not Responding' and I have to click 'End Task'. That is what I meant when I used the term crash because inevitably I have to restart the application again.

I have no OLE or hyperlinked fields in the report. I can remove any number or combination of fields. Typically when I remove quite a few (about 10) randon fields the report then opens and displays the remaining information about each record perfectly.

Personally I don't think,virtual memory is the problem. I can't see how it would be. All I did was went into Control Panel->System then clicked on the Performance tab, then the Virtual Memory button and increased the maximum size to 1200.

Here's the funny thing, I'm running Windows 98 with Access 2000 and neither the "small" or "large" database had any problems.

Now, when I took it to the client, all they had was Access97 again on Windows 2000. I converted the "small" back to '97 and experienced problems. At first I thought this had to do with the conversion from 2000 to '97 so I eventually re-wrote the entire database from scratch with Access 97 and then experienced the same problem!

The only conclusion I can draw that the report is unable to preview this amount of information when run under '97 and I need to fix it. The "large" can perform this exact same taskunder '97 but the "small" cannot.This is why I thought it may be some kind of memory allocation problem.

Any ideas? It has me completely buggered

Thanks,
Liam

 

[Rich]

Why not post a small sample here in 97 so we can test it

 

[The_Doc_Man]

Is there any chance that the underlying query might try to generate a record longer than 2K ? This would kill you in a heartbeat. Or, just how many records are we talking about? The resultant set of a query also has total size limits.

In AC97, look up help topic "Limits" - in AC2K the same topic is titled "Specifications" - and follow links to look at the size limitations imposed by Access versions. Seeing one of these might jog your consciousness about accidentally hitting one of these "stone walls" that Access tosses our way.

 

[wjoc1]

Thank you for all your help and I hate to be still going on about this but unfortunately I'm still screwed!

I like your idea about the underlying query generating a record that is too long or generating a record set that is too large but (and I hate saying this!), what's happening to me still doesn't make any sense! If I use the large and old version (~12mb) I have no problem and when I use the small new version (~2mb) the bluddy thing hangs when I try and open detailed reports. Both databases have the exact same information and the exact same query and report. I know it sounds ridiculous, but it can't be that the query is returning too large a record set or anything because why would it work with the large version? It's just bizarre.

As I said, I only encounter this problem with Access 97, Access 2K runs either version perfectly! Everything is identical,information,query and report. I just can't figure it out.

Thanks again for all your help

Liam