Query crashes Access 2000

[David Maggard]

I am working on a Access database for a client that uses office 2000 on
winXp, I use OfficeXP.on win2000.
Using access 2000 fileformat.
Everything works fine on my PC, everything except 1 query and the 2 forms
that reference it are working on 2000.
The query is about 1100 characters and joins 8 tables.
They upgraded to newest SP of 2000.
Anytime the query or one of the forms is opened in data or design mode
access crashes without giving an error or closing the ldb file.
Nothing in event viewer
Please help me! I am at wits end!

 

[David Maggard]

no udf's just some sums and cout aggregates

weird thing is that after playing with it, they mysteriously start and stop
working and about the only thing that seems to possibly be effecting it is
outlook, which is now acting funny(doesn't want to open) on their machine

Do you have any user defined function calls in the query? If so, you may have something in
one of the functions that is the actual cause of the crash.

Have you tied copying the SQL from the query and creating a new query by pasting the SQL
text into it? This is something to try in case the query is corrupt for some reason.

I've seen this behavior in 2 instances. 1) A problem with the version of Jet 4, installing
a newer version solved the problem. Service Pack 7 for Jet 4 was just released and is
available by running Windows Update. 2) While the query was perfectly legal and the syntax
was correct, removing all unneeded brackets and parentheses (while technically they were
correct and shouldn't hurt anything, they weren't required to be there) from the SQL text
allowed it to run.

Example:
SELECT [tblMenu].[MenuID], [tblMenu].[MenuItem]
FROM [tblMenu] INNER JOIN [tblMenuCategories] ON [tblMenu].[CategoryID] =
[tblMenuCategories].[CategoryID]
WHERE ((([tblMenuCategories].[MenuCategory])="Desserts"));

Could be rewritten:
SELECT tblMenu.MenuID, tblMenu.MenuItem
FROM tblMenu INNER JOIN tblMenuCategories ON tblMenu.CategoryID =
tblMenuCategories.CategoryID
WHERE tblMenuCategories.MenuCategory="Desserts";

You will have to leave brackets around fields and table names that have spaces in them and
you'll have to leave parentheses anyplace they are required for the logic of the
statement.

--
Wayne Morgan
Microsoft Access MVP

I am working on a Access database for a client that uses office 2000 on
winXp, I use OfficeXP.on win2000.
Using access 2000 fileformat.
Everything works fine on my PC, everything except 1 query and the 2 forms
that reference it are working on 2000.
The query is about 1100 characters and joins 8 tables.
They upgraded to newest SP of 2000.
Anytime the query or one of the forms is opened in data or design mode
access crashes without giving an error or closing the ldb file.
Nothing in event viewer
Please help me! I am at wits end!

 

[David Maggard]

SELECT SCACs.SCACID, SCACs.SCACGroupID, SCACGroups.PoolNew, SCACs.SCAC,
SCACs.Active,
IIf(SCACGroups.PoolNew,SCACGroupNewBoxCount.NewBoxCount,SCACNewBoxCount.NewB
oxCount) AS NewBoxCount, SCACGroupUsedBoxCount.UsedBoxCount,
[UsedBoxCount]+[NewBoxCount] AS OnHand,
IIf(SCACGroups.PoolNew,SCACGroupBookedBoxCount.BookedBoxCount-SCACBookingSta
ts.BookedBoxes,SCACGroupOverFlow.SCACGroupOverFlow-SCACBookingStats.OverFlow
Boxes) AS GroupBookedBoxes, SCACBookingStats.BookedBoxes,
SCACBookingStats.BookedWeight, [OnHand]-[BookedBoxes]-[GroupBookedBoxes] AS
Available
FROM SCACGroups RIGHT JOIN (((((SCACNewBoxCount RIGHT JOIN
(SCACGroupNewBoxCount RIGHT JOIN SCACs ON SCACGroupNewBoxCount.SCACGroupID =
SCACs.SCACGroupID) ON SCACNewBoxCount.SCACID = SCACs.SCACID) LEFT JOIN
SCACGroupUsedBoxCount ON SCACs.SCACGroupID =
SCACGroupUsedBoxCount.SCACGroupID) LEFT JOIN SCACGroupOverFlow ON
SCACs.SCACGroupID = SCACGroupOverFlow.SCACGroupID) LEFT JOIN
SCACBookingStats ON SCACs.SCACID = SCACBookingStats.SCACID) LEFT JOIN
SCACGroupBookedBoxCount ON SCACs.SCACGroupID =
SCACGroupBookedBoxCount.SCACGroupID) ON SCACGroups.SCACGroupID =
SCACs.SCACGroupID
ORDER BY SCACs.SCAC;

no udf's just some sums and cout aggregates

weird thing is that after playing with it, they mysteriously start and stop
working and about the only thing that seems to possibly be effecting it is
outlook, which is now acting funny(doesn't want to open) on their machine

Do you have any user defined function calls in the query? If so, you may have something in
one of the functions that is the actual cause of the crash.

Have you tied copying the SQL from the query and creating a new query by pasting the SQL
text into it? This is something to try in case the query is corrupt for some reason.

I've seen this behavior in 2 instances. 1) A problem with the version of Jet 4, installing
a newer version solved the problem. Service Pack 7 for Jet 4 was just released and is
available by running Windows Update. 2) While the query was perfectly legal and the syntax
was correct, removing all unneeded brackets and parentheses (while technically they were
correct and shouldn't hurt anything, they weren't required to be there) from the SQL text
allowed it to run.

Example:
SELECT [tblMenu].[MenuID], [tblMenu].[MenuItem]
FROM [tblMenu] INNER JOIN [tblMenuCategories] ON [tblMenu].[CategoryID] =
[tblMenuCategories].[CategoryID]
WHERE ((([tblMenuCategories].[MenuCategory])="Desserts"));

Could be rewritten:
SELECT tblMenu.MenuID, tblMenu.MenuItem
FROM tblMenu INNER JOIN tblMenuCategories ON tblMenu.CategoryID =
tblMenuCategories.CategoryID
WHERE tblMenuCategories.MenuCategory="Desserts";

You will have to leave brackets around fields and table names that have spaces in them and
you'll have to leave parentheses anyplace they are required for the

logic
of the

 

[Wayne Morgan]

Also, let's try something basic. Have you done a Compact and Repair lately? Do it on both
the front end and back end if the database is split.

 

[Wayne Morgan]

Well, I'm glad it's working. If the format and reinstall fixed it, then that limits the
problem to some extent. Either one of the files needed to run the database was corrupted
or the version of a file they had was causing problems. Jet4 Service Packs 4 & 5 tended to
cause problems. They may be below this level or, if they installed all the updates when
they reinstalled, will now be at Service Pack 7.