running report cause fatal error- on Win98, not XP

[Riley]

I'm baffled on this one. I have written a Preventative maintenance database
for our shop. I wrote it using Access2003 on my laptop. The computer that
will be running it is older and running win98 and Office 2000. I wrote the
database in Access 2000 format. The database runs 100% fine on my laptop
and the other computers that run XP in our office, BUT on the computer that
will be running it (the older machine) I get a complete access crash when I
try to run a couple of the reports. These are a couple of reports that are
generated by a pretty complex series of queries. Most of the reports are
fine, and print fine, but these more complex reports generate an error popup
and Access shuts down.

I have NO idea where to start on this one, any help would be appreciated.

Like I said, the XP machines run it 100% fine.

Thanks in advance.

Riley

 

[Allen Browne]

Riley, it is hard to know where to start, because there are so many possible
factors.

First step might be to check that the Win98 mahine has SP3 for Office 2000
installed (see Help | About in Access).

Also that it has SP8 for JET 4 by locating the file msjet40.dll (typically
in windows\system32), right-clicking and under Properties choose the Version
tab. You should see 4.0.8xxx.0. The xxx digits don't matter, but if you do
not see the 8, download SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
http://support.microsoft.com/kb/239114

Now that you have the same query engine running on both machines, you may be
fine. If not, there could be a corruption in the mdb, and it might be due to
the fact that A2003 and A2000 use different binaries. In a perfect world,
Access would handle the conversion back to A2000 seamlessly; in reality, if
often needs a decompile to get this to work correctly.

Try this sequence:

1. Using A2003, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. If you have any code that runs on startup (e.g. a startup form, or
AutoExec macro), disable it. Then compact the database:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again, without opening any code window, or
running any code.

5. Using A2000, create a new (blank) database.
Immediately uncheck the Name AutoCorrect boxes.

6. Import everything from the old database:
File | Get External | Import

7. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

8. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

More info on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

 

[Peter R. Fletcher]

In the (long ago!) days when I used Win 98, I used to see this sort of
problem relatively frequently. Access is a resource hog, and Win 98 is
both much less efficient in its use of physical resources (especially
memory) and much more prone to memory leaks than later versions of
Windows. It was not uncommon to have problems on Win98 developing or
running a database which would display no problems under Win 2000,
even on a machine with _less_ physical resources. If your problem is
related to this issue, and it certainly sounds like it to me, adding
more physical memory to the Win 98 system and/or making sure that
nothing else non-essential is occupying memory when you are running
the reports may help, but you may be snookered.

I'm baffled on this one. I have written a Preventative maintenance database
for our shop. I wrote it using Access2003 on my laptop. The computer that
will be running it is older and running win98 and Office 2000. I wrote the
database in Access 2000 format. The database runs 100% fine on my laptop
and the other computers that run XP in our office, BUT on the computer that
will be running it (the older machine) I get a complete access crash when I
try to run a couple of the reports. These are a couple of reports that are
generated by a pretty complex series of queries. Most of the reports are
fine, and print fine, but these more complex reports generate an error popup
and Access shuts down.

I have NO idea where to start on this one, any help would be appreciated.

Like I said, the XP machines run it 100% fine.

Thanks in advance.

Riley

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Riley]

Thanks for your time and your patience- I'll try what you have written.

Thing is, only the more complex reports cause the crash- the others do not-
I read somewhere that access 2000 has a smaller limit as far as the number
of characters on the SQL statement that the report is based on- which BTW on
these reports is very long. I'm thinking that this may be the problem- am I
on to something? Now keep in mind that I wrote it using Access2003, but in
the Acess2000 format- shouldn't I have had a problem even CONSTRUCTING the
report in the first place?

I will try what you have typed out, but would like to know thoughts on this
as well.

Thanks much.

Riley

 

[Riley]

I know Win98 is a hog- they all are in their own way. The system it will be
running on is older (I perosnally think the thing should be escorted to the
dumpster, but that's another story) It has had memory upgrades, and I have
removed all the nonessential programs from it to free up hard drive space.
As well, there are very few items running in the background to conserve RAM,
so I'm not sure that it is an issue. It almost seems as even though the
program was written in Access2000 format using Access2003, there is
something that is carrying over from 2003 that 2000 does not like.

I've written things in the past for systms that were worse off than this
one, and haven't had a problem, but I was using Access2000 at the time
though too.

Thanks.

Riley

 

[Allen Browne]

There are some limits to the length of the string if you are using the
Expression Builder to create it. But if you create the string in the query
window, or create it programmatically in VBA, the limit IIRC is around 64000
characters.

The number of ANDs you can have in a WHERE clause varies with version, but
you should be able to get at least 50. If you need more than that, you could
probably work around several of them with the IN operator.

Access 2000 and 2003 actually use the same query engine (JET 4.)

 

[Riley]

The reports that fail are based of queries, which in SQL view is around
49000 characters. Before I try anything you have so far suggested, this
morning I went down to the computer that will be using this and tried to
bring up the report in design view, but got the same crash. Maybe this
narrows things down some?

 

[Allen Browne]

Sheesh. It is actually not that difficult to crash JET with complex queries,
and by "crash" I mean "shut down by Windows." I don't have the specifics,
but subqueries that you build on top of are one example.

If you are writing queries of that size (and it is not just a matter of
using shorter table or field names), you might be ready to move the back end
to SQL Server.

Alternatively, you might consider breaking this down into smaller chunks,
i.e. writing a lower level query, and using it as a source "table" for
another query.

With a normalized data structure (no repeating fields), I can't recall
writing a single query statement of that size by hand. Some of the search
forms I write could dynamically generate SQL statements like that (the
option to generate lots of subqueries in the WHERE clause to select data
based on related tables.)


What I don't understand is the bit that you pin-pointed: why it worked under
XP, but not 98. If anyone else has a suggestion on that, please add your
wisdom.

 

[Riley]

Well, your original post may be on to something. While the machine has
Office 2000, it is in sever need of updating, which I'm currently underway
doing. I kick myself for not looking into this first- I guess I'm getting
used to XP's auto-updating.
I was thinking about breaking it down to smaller chunks, but to be honest
would prefer not to have to do, lol. In the final query that the report
runs on, it has several queries and tables that it uses already, so it will
be a very time consuming task to break it down smaller.
Keep your fingers crossed- hopefully a very thorough updating will do the
trick.

I still consider myself a newbie in this, I write my queries in design view,
with liberal use of the expression builder. I can logically figure out how
to get the results I'm after, but have had no programming training
whatsoever, so to be honest, there is a level where some of the things you
mention I a not familiar with in the slightest. 100% self taught.

Thanks much for your help.

Riley

 

[Peter R. Fletcher]

Just one other suggestion, and you may well already have tried this:
when you move the application to the Win98 machine and before you do
anything else, open a Module or some Form code in the VBA editor and
reCompile the application. I have once run into a "works under XP,
doesn't work under Win98" problem that responded to this, though the
symptoms were very different to what you describe.

I know Win98 is a hog- they all are in their own way. The system it will be
running on is older (I perosnally think the thing should be escorted to the
dumpster, but that's another story) It has had memory upgrades, and I have
removed all the nonessential programs from it to free up hard drive space.
As well, there are very few items running in the background to conserve RAM,
so I'm not sure that it is an issue. It almost seems as even though the
program was written in Access2000 format using Access2003, there is
something that is carrying over from 2003 that 2000 does not like.

I've written things in the past for systms that were worse off than this
one, and haven't had a problem, but I was using Access2000 at the time
though too.

Thanks.

Riley

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Riley]

Allen you were 100% correct in your first reply.
After updating to SP3 and the JET update, everything is 100% fine without
the need to recompile.
I can't thank you enough!!
Bravo!

Riley