Access2002 crashes while changing from query design to SQL

[Guest]

Hi @ all

I have Access 2002 (eng.) and a query which causes Access to crash when changing the view from Design to SQL
Is this a known bug and if yes, any hotfix, solution available

The strange thing is that I have another query doing nearly the same, only one criteria is different (same field of course), criteria value either 4165 or 4167 and this query doesn't crash Access.
Due to that I have more than 100 fields + 10 tables in the queries it is not that easy to check if there could be an other difference beside the criterias. ;-

Thanks in advance
Bernd

 

[Allen Browne]

This sounds like one of the dozen or more problems caused by Name
Autocorrect. See:
ACC2002: Access Quits Unexpectedly When You Run a Query
at:
http://support.microsoft.com/?id=319491

For other problems with Name AutoCorrect, see:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have Access 2002 (eng.) and a query which causes Access to crash when

changing the view from Design to SQL.

Is this a known bug and if yes, any hotfix, solution available?

The strange thing is that I have another query doing nearly the same, only

one criteria is different (same field of course), criteria value either 4165
or 4167 and this query doesn't crash Access.

Due to that I have more than 100 fields + 10 tables in the queries it is

not that easy to check if there could be an other difference beside the
criterias. ;-)

 

[Guest]

Hi Allen

no unfortunately nothing to do with this, I had disabled this already
Access is not closing without error message. I receive the 'usual' message, that Access encoutered a problem and needs to close

Any other idea

Regards
Bernd

 

[Allen Browne]

Okay, next thing is to verify that you have at least Service Pack for Access
2002 (the Help | About screen will show "Access 2002 SP2" if you do).

Just as importantly, check you have SP 8 for JET 4.
Find msjet40.dll (usually in windows\system32).
Right-click, and choose Properties.
On the version tab, if it is not 4.0.xxxx.0, go to
http://support.microsoft.com, the Downloads section, and get JET 4 SP8.

If this thing still persists, it is also possible that the database is
partially corrupt, in which case you could rebuilt it fairly quickly by
creating a new (blank) database, disabling Name AutoCorrect, and then
importing everything: File | Get External | Import.

One other thing. You say this does not happen with some criteria? That hints
at two possibilities:
1) corrupted index: try Tools | Database Utilities | Compact/repair, or
2) misinterpreted data type.

The 2nd is possible if the query contains an undeclared parameter. For
example, if this number is being picked up from a text box on a form, try
declaring the parameter by choosing Parameters from the Query menu (in query
design view), and entering something like this in the dialog:
[Forms]![MyForm]![MyTextbox] Long

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

no unfortunately nothing to do with this, I had disabled this already.
Access is not closing without error message. I receive the 'usual'

message, that Access encoutered a problem and needs to close.

 

[Guest]

Hi Allen

Access 2002 SP2 : installed (10.4302.4219
MSJet40.dll : Version 4.0.8618.
(Unfortunately I won't not able to install SPs or patches - no admin rights on my PC (Win XP pro), only a user and won't get them installed by IT if they are not rolled out company wide)

I don't think that the database is (partially) corrupt. I have the problem with closing Access due to 'errors' not only in this database. I had created this db in January this year in Access 2002 on XP pro
Compact and Repair I have done several times in the past without getting this problem fixed

They are only 'plain' queries (nothing special, no parameter, forms, etc.) with fixed criterias
Misinterpreted data types: As I run the queries on linked Oracle tables which haven't changed the structure at all for a few years and had with Access 97 (under WinNT4) no probs I don't think that I have problems due to that

Different criteria in the queries is on the same field once 4165, once 4167 (= different trade types), other criterias are the same

Could it be that the problem is coming from my ODBC driver as the tables on which I run the query are only linked into the Access db from 2 different Oracle dbs (diff. versions of Oracle dbs)

Warm regards, (or what are you writing in Downunder
Bernd

 

[hin87_at_yahoo.com]

BerHav,

I am also having the same problem. When I open a report or form,
sometime I will get this error message. However I did notices it's
only happening in certain forms/reports. Those usually contain huges
query (about 5-7 tables in the query)

 

[Allen Browne]

Ok: service packs are pretty good.

The fact that the issue occurs with multiple databases and is not fixed by a
compact/repair adds weight to your assertion that it is not just a corrupt
mdb.

The ODBC driver handing 2 versions of Oracle could be an issue, though I
don't have enough experience with that to give you an informed comment.

A search of the knowledgebase at:
http://support.microsoft.com/default.aspx?scid=FH;[LN];KBHOWTO
locates 73 matches for "ODBC". 11 of those also contain "quit", including
this one:
http://support.microsoft.com/default.aspx?scid=kb;en-us;821809&Product=acc2002
which is addresses by SP3.

That approach might be your best line of enquiry.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access 2002 SP2 : installed (10.4302.4219)
MSJet40.dll : Version 4.0.8618.0
(Unfortunately I won't not able to install SPs or patches - no admin

rights on my PC (Win XP pro), only a user and won't get them installed by IT
if they are not rolled out company wide).

I don't think that the database is (partially) corrupt. I have the problem

with closing Access due to 'errors' not only in this database. I had created
this db in January this year in Access 2002 on XP pro.

Compact and Repair I have done several times in the past without getting this problem fixed.

They are only 'plain' queries (nothing special, no parameter, forms, etc.) with fixed criterias.
Misinterpreted data types: As I run the queries on linked Oracle tables

which haven't changed the structure at all for a few years and had with
Access 97 (under WinNT4) no probs I don't think that I have problems due to
that.

Different criteria in the queries is on the same field once 4165, once

4167 (= different trade types), other criterias are the same.

Could it be that the problem is coming from my ODBC driver as the tables

on which I run the query are only linked into the Access db from 2 different
Oracle dbs (diff. versions of Oracle dbs).