App Crashes on Compact & Repair

[Timmy!]

A2003 on Win XP Pro.

A number of very stable apps I have are lately sometimes crashes when
I do a compact & repair, especially when I hold the shift key to do
so.

When this starts happening, I usually turn off or restart my
computer. Sometimes this stops the crashing, sometimes it doesn't.
I'm really not sure why this is happening, I've never experienced it
before.

I first noticed this about a month ago.

Does this sound like anything that might be related to some of the
recent XP updates?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Chris O'C via AccessMonster.com]

Does the vba code compile? Are there any "impossible" queries? (Ones that
won't run because they're missing tables, fields etc.) Does the vba code
have precompiler directives?

Have you tried a decompile?

Chris
Microsoft MVP
Timmy! wrote:

>A2003 on Win XP Pro.

A number of very stable apps I have are lately sometimes crashes when
I do a compact & repair, especially when I hold the shift key to do
so.

When this starts happening, I usually turn off or restart my
computer. Sometimes this stops the crashing, sometimes it doesn't.
I'm really not sure why this is happening, I've never experienced it
before.

I first noticed this about a month ago.

Does this sound like anything that might be related to some of the
recent XP updates?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

[Timmy!]

On Aug 11, 2:27 pm, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:

Does the vba code compile? Are there any "impossible" queries? (Ones that
won't run because they're missing tables, fields etc.) Does the vba code
have precompiler directives?

Have you tried a decompile?

Thanks for replying, Chris.

Yes, compile produces no errors. Not sure what an impossible query is
- all my querydefs would be pass through queries (to Oracle)

I'm not sure what precompiler directives are?

Decompile doesn't help, in fact, I first noticed this during decompile
- after decompiling and closing the app then opening (holding shift)
and then compacting (while holding shift). But it happens outside of
decompile processes as well.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[paii, Ron]

Do you have any local tables with memo fields? If so one of your memos may
be corrupted which can crash a compact & repair. You can find the record by
opening the table and scrolling down until you see "ERROR" in the memo
field. Do not enter this record, use a query to delete the record then try
compact & repair.

If no memo fields, try importing into a new database file.

"Timmy!" <tm******@mun.cawrote in message
news:21**********************************@m73g2000 hsh.googlegroups.com...

A2003 on Win XP Pro.

A number of very stable apps I have are lately sometimes crashes when
I do a compact & repair, especially when I hold the shift key to do
so.

When this starts happening, I usually turn off or restart my
computer. Sometimes this stops the crashing, sometimes it doesn't.
I'm really not sure why this is happening, I've never experienced it
before.

I first noticed this about a month ago.

Does this sound like anything that might be related to some of the
recent XP updates?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Chris O'C via AccessMonster.com]

An impossible query is one where Jet can't execute the query and therefore
can't compile it on its first run. This would usually happen when a file
holding a linked table gets moved. Table doesn't exist in current db so a
query naming that table is impossible to execute. Jet renames things when in
design view like Expr1, Expr2 etc. Jet can't compile the query so you might
have trouble compacting the db.

Pass through queries aren't going to be compiled by Jet so I don't think
that's your problem.

Might be time to start fresh by creating a new db file, turning off track
name autocorrect, importing everything into it, then compiling and compacting.
Chris
Microsoft MVP
Timmy! wrote:

>On Aug 11, 2:27 pm, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:

>Does the vba code compile? Are there any "impossible" queries? (Ones that
won't run because they're missing tables, fields etc.) Does the vba code
have precompiler directives?

Have you tried a decompile?

Thanks for replying, Chris.

Yes, compile produces no errors. Not sure what an impossible query is
- all my querydefs would be pass through queries (to Oracle)

I'm not sure what precompiler directives are?

Decompile doesn't help, in fact, I first noticed this during decompile
- after decompiling and closing the app then opening (holding shift)
and then compacting (while holding shift). But it happens outside of
decompile processes as well.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

[Chris O'C via AccessMonster.com]

And precompiler directives are #if, #else, #end if in vba code to
conditionally compile blocks of code. If you don't know what they are, and
you're the only one who ever worked on the db, you won't have any of these in
your code.

Chris
Microsoft MVP
Chris O'C wrote:

>An impossible query is one where Jet can't execute the query and therefore
can't compile it on its first run. This would usually happen when a file
holding a linked table gets moved. Table doesn't exist in current db so a
query naming that table is impossible to execute. Jet renames things when in
design view like Expr1, Expr2 etc. Jet can't compile the query so you might
have trouble compacting the db.

Pass through queries aren't going to be compiled by Jet so I don't think
that's your problem.

Might be time to start fresh by creating a new db file, turning off track
name autocorrect, importing everything into it, then compiling and compacting.

Chris
Microsoft MVP

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

[Timmy!]

On Aug 11, 4:10 pm, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:

Might be time to start fresh by creating a new db file, turning off track
name autocorrect, importing everything into it, then compiling and compacting.

Thanks again Chris and to Ron.

Yes, I always try to make sure I turn off what Allen Browne, I think,
referred to as "autocorrupt" when I first saw his posts about a few
years ago. 8)

And I did create a blank database, autocorrupt off. imported
everything and still the behaviour persisted.

Sometimes I do have local Jet tables and some of these do have memo
fields, but usually whenever they are used (often, for example, to
list choices a person has made from a combo box and display the
choices alphabetically), I run an execute "delete * from tbl_Jet_Sort"
sort of thing. I must keep Ron's suggestion in mind for future
occurrences in other apps. Though for the app where I am experiencing
problems, there were no local Jet tables.

Happily, for the app where this was happening, it seems to have gone
away again and I'm not 100% sure why. One thing I did do was go into
the module where I keep all my constants (things like various ODBC
connect strings for different DSNs for PTQ construction or standard
PTQ query names I don't want all over my code in case I want to change
them and so on). In this app, all my constants were defined as per
the following example (the constant refers to the name of an Oracle
view - the equivalent of a querydef - frequently used):

Public Const cAssets as string = "tma.V_PERMIT_ASSETS"

I got rid of the "as string" on all of them:

Public Const cAssets = "tma.V_PERMIT_ASSETS"

and suddenly the app was compacting properly.

cAssets is used in situations like setting up the SQL of a PTQ the VBA
is about to create. For example:

dim strS as string

strS = "select p_tag_no, p_tag_description from " & cAssets

I have no idea if including definition of the constant as a string was
the cause, as sometimes, in other cases where this occurs, the
behaviour goes away and I'm not sure why. Does anyone think this
might be it?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[David W. Fenton]

"Timmy!" <tm******@mun.cawrote in
news:21**********************************@a1g2000h sb.googlegroups.com
:

I have no idea if including definition of the constant as a string
was the cause, as sometimes, in other cases where this occurs, the
behaviour goes away and I'm not sure why. Does anyone think this
might be it?

No, but it likely caused problematic code to be discarded, because
any code that used the constant would have to be completely
recompiled.

It sounds to me like you didn't really import properly. You should
only import into a new MDB from a decompiled and compacted source,
because otherwise, some of the corrupted code could be carried over
through the import.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Timmy!]

On Aug 11, 10:03 pm, "David W. Fenton" <[e-mail]>
wrote:

It sounds to me like you didn't really import properly. You should
only import into a new MDB from a decompiled and compacted source,
because otherwise, some of the corrupted code could be carried over
through the import.

Thanks David,

Unfortunately, that wouldn't help, because I'd be unable to compact
after decompile.

And while I thought the afore mentioned removal of "as string" worked,
it didn't. I was curious and opened the corrupted mdb, or the one
that had the "as string" in the constant declarations and compacted
it. It crashed. Then the reconstructed mdb with the as string
removed started crashing too.

I'm very careful and try very hard to be consistent with my code. But
this was an mdb that had been reconstructed for me from an mde
(original mdb code was lost in an unfortunate incident). The
reconstruction used some code with dbengine in it, which I never ever
use. It was on a form that I didn't recognize, was not referenced
anywhere else in the application, so I ditched the form. It's
behaving now.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[David W. Fenton]

"Timmy!" <tm******@mun.cawrote in
news:cf**********************************@e39g2000 hsf.googlegroups.co
m:

On Aug 11, 10:03 pm, "David W. Fenton"
<[e-mail]:

>It sounds to me like you didn't really import properly. You
should only import into a new MDB from a decompiled and compacted
source, because otherwise, some of the corrupted code could be
carried over through the import.

Unfortunately, that wouldn't help, because I'd be unable to
compact after decompile.

Then you need to us Application.SaveAsText/.LoadFromText to recreate
the app.

[]

I'm very careful and try very hard to be consistent with my code.
But this was an mdb that had been reconstructed for me from an mde
(original mdb code was lost in an unfortunate incident). The
reconstruction used some code with dbengine in it, which I never
ever use. It was on a form that I didn't recognize, was not
referenced anywhere else in the application, so I ditched the
form. It's behaving now.

Again, I don't use of the DBEngine object would by itself be a
source of instability, as it's one of the most basic components of
Access.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/