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

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
"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%230CzBg7OGHA.1132@xxxxxxxxxxxxxxxxxxxxxxx
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: 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: Take database, copy & edit, have reports reflect changes
    ... >I've tried two different things with the database. ... but the reports wouldn't show my reflections. ... this will be a query selecting *some* fields (not the newly added ... and a Query Design window will ...
    (microsoft.public.access.gettingstarted)
  • Re: running report cause fatal error- on Win98, not XP
    ... only the more complex reports cause the crash- the others do ... Then compact the database: ... Open a code window. ... Still in the code window, choose Compile from the Debug menu. ...
    (microsoft.public.access.reports)
  • Re: SYSTEMS ENGINEER/ VAX-VMS/ CAREER POSITION
    ... System Administration and database admin from NIIT, ... salary details and generate reports and enclosures,and calculates ... Responsibilities: ... Write New programs from user requirements, Programming Changes ...
    (comp.os.vms)
  • Re: Advice needed for a growing Access 2000 project
    ... However, it turned out that quite a few of those were "leftovers" from previous releases, no longer accessible from anywhere but the database window, and, thus, no longer used. ... But that certainly isn't the _norm_ -- without any 'heroic' measures, there are routine reports of split Access DBs ... Finally, in my opinion, for "Windows apps", that is, individual-user applications, modest-sized multiuser applications, and client-server applications of any size, Dot Net does NOT "help along" any of these issues. ... The post I reference was in reference its self to the MS Access Help file under "Microsoft Access database general specifications" ...
    (comp.databases.ms-access)
Loading