Re: running report cause fatal error- on Win98, not XP

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Riley" <CDStendel@xxxxxxxxxxx> wrote in message
news:ej4kLV7OGHA.2012@xxxxxxxxxxxxxxxxxxxxxxx
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" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:emprsJ7OGHA.2888@xxxxxxxxxxxxxxxxxxxxxxx
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" <CDStendel@xxxxxxxxxxx> wrote in message
news:u6tNm%236OGHA.516@xxxxxxxxxxxxxxxxxxxxxxx
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

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%23EOt1DbOGHA.2300@xxxxxxxxxxxxxxxxxxxxxxx
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

"Riley" <CDStendel@xxxxxxxxxxx> wrote in message
news:ep5YgYYOGHA.1132@xxxxxxxxxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... synchronize combo boxes to the detail section or the parameter form query ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would ...
    (microsoft.public.access.conversion)
  • Re: Newbie? Do I use Report or Query
    ... Makes reading your database easier! ... Table JobDetails ... Reports are the counterpart of Forms. ... If based on a query, ...
    (microsoft.public.access.gettingstarted)
  • Re: Query In BE database
    ... that would give the users "live" access to the underlying queries. ... When selected from the drop-down list box, I what the the query that is ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
  • Re: running report cause fatal error- on Win98, not XP
    ... another query. ... only the more complex reports cause the crash- the others do ... Then compact the database: ... Still in the code window, choose Compile from the Debug menu. ...
    (microsoft.public.access.reports)