Access hanging, corrupting, getting me worried

[Andrew Backer]

I am using A2k, and having quite a few scares with database
corruption. Making me wonder if access should even be used at all,
since once again there is no logic to Access' misbehavior.

I have a startup form set, which should load every time I open the db.
If it doesn't load, I know something is wrong.

Sometimes a recompile will fix it, and it will start loading fine
again. Other times it will just die, and importing all objects into a
new databaes will fix it.

It just did it to me again. I have tried to import to a new database,
but on import it will crash on importing some object, there doesn't
appear to be any logic to which one. It kept dying on
"&Relationship", but after I turned off import relationships, it just
crashed on a random form. If it crashes, I can sometimes make the
import dialog go away by pressing esc, but I usually can't close the
database (msaccess process won't quit), but the db window will go
away.

I get wierd object names in the database I am importing from, like
"tmprryQryFrLnkFldsCrtn0123456789". I was able to delete the
relationships in the 'corrupted' database, but after I did I couldn't
save it.

Any idea what is goin on? This happens quite regularly, and is really
starting to worry me.

Thanks,
Andrew Backer

..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

 

[Tom Wickerath]

Hi Andrew,

Is this a single-user database located on one PC? Or, is it a multi-user database? If
multi-user, is it split into a front-end (FE) and back-end (BE) databases? Does each user have
their own copy of the FE database?

See the following articles:
http://www.access.qbuilt.com/html/gem_tips.html#SplitDB

http://members.iinet.net.au/~allenbrowne/ser-25.html

http://www.granite.ab.ca/access/corruptmdbs.htm
http://www.granite.ab.ca/access/bestpractices.htm


Tom
__________________________________________


I am using A2k, and having quite a few scares with database
corruption. Making me wonder if access should even be used at all,
since once again there is no logic to Access' misbehavior.

I have a startup form set, which should load every time I open the db.
If it doesn't load, I know something is wrong.

Sometimes a recompile will fix it, and it will start loading fine
again. Other times it will just die, and importing all objects into a
new databaes will fix it.

It just did it to me again. I have tried to import to a new database,
but on import it will crash on importing some object, there doesn't
appear to be any logic to which one. It kept dying on
"&Relationship", but after I turned off import relationships, it just
crashed on a random form. If it crashes, I can sometimes make the
import dialog go away by pressing esc, but I usually can't close the
database (msaccess process won't quit), but the db window will go
away.

I get wierd object names in the database I am importing from, like
"tmprryQryFrLnkFldsCrtn0123456789". I was able to delete the
relationships in the 'corrupted' database, but after I did I couldn't
save it.

Any idea what is goin on? This happens quite regularly, and is really
starting to worry me.

Thanks,
Andrew Backer

..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

 

[Andrew Backer]

This is a multi-user db, but I have the problems single user or multi
user. I usually don't distribute it until it works fine, and then the
MDE goes out. I am getting the current problems on my dev machine.

I just opened a working copy of the db, changed a lot of code from
using constants to using an Enum. It saved & quit ok, but it is
exhibiting the same problem when I open it now.

One thing I forgot to mention :

Access CRASHES when I try to compile the DB. It compiled fine before
I closed & reopened, but now it goes boom.

Hi Andrew,

Is this a single-user database located on one PC? Or, is it a multi-user database? If
multi-user, is it split into a front-end (FE) and back-end (BE) databases? Does each user have
their own copy of the FE database?

See the following articles:
http://www.access.qbuilt.com/html/gem_tips.html#SplitDB

http://members.iinet.net.au/~allenbrowne/ser-25.html

http://www.granite.ab.ca/access/corruptmdbs.htm
http://www.granite.ab.ca/access/bestpractices.htm


Tom
__________________________________________


I am using A2k, and having quite a few scares with database
corruption. Making me wonder if access should even be used at all,
since once again there is no logic to Access' misbehavior.

I have a startup form set, which should load every time I open the db.
If it doesn't load, I know something is wrong.

Sometimes a recompile will fix it, and it will start loading fine
again. Other times it will just die, and importing all objects into a
new databaes will fix it.

It just did it to me again. I have tried to import to a new database,
but on import it will crash on importing some object, there doesn't
appear to be any logic to which one. It kept dying on
"&Relationship", but after I turned off import relationships, it just
crashed on a random form. If it crashes, I can sometimes make the
import dialog go away by pressing esc, but I usually can't close the
database (msaccess process won't quit), but the db window will go
away.

I get wierd object names in the database I am importing from, like
"tmprryQryFrLnkFldsCrtn0123456789". I was able to delete the
relationships in the 'corrupted' database, but after I did I couldn't
save it.

Any idea what is goin on? This happens quite regularly, and is really
starting to worry me.

Thanks,
Andrew Backer

.--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

 

[Andrew Backer]

Sorry, let me clarify...

In production usage the database is FE/BE split, with the FE being an
MDE that is *copied* to each users desktop.

I do not experience this problem in production, since it doesn't
appear to 'crop up' unless I do dev work. Once working and compiled,
it hasn't yet broken in this way.

I do experience it in single user mode ( still with FE/BE ) while
doing development at home or at the office.

I just had it break on me again. I am able to import everything, but
it's crashing on a form again. This time the message is :

"Could not start transaction. too many transactions already nested."

Hrm...

- Andrew Backer

Hi Andrew,

Is this a single-user database located on one PC? Or, is it a multi-user database? If
multi-user, is it split into a front-end (FE) and back-end (BE) databases? Does each user have
their own copy of the FE database?

See the following articles:
http://www.access.qbuilt.com/html/gem_tips.html#SplitDB

http://members.iinet.net.au/~allenbrowne/ser-25.html

http://www.granite.ab.ca/access/corruptmdbs.htm
http://www.granite.ab.ca/access/bestpractices.htm


Tom
__________________________________________


I am using A2k, and having quite a few scares with database
corruption. Making me wonder if access should even be used at all,
since once again there is no logic to Access' misbehavior.

I have a startup form set, which should load every time I open the db.
If it doesn't load, I know something is wrong.

Sometimes a recompile will fix it, and it will start loading fine
again. Other times it will just die, and importing all objects into a
new databaes will fix it.

It just did it to me again. I have tried to import to a new database,
but on import it will crash on importing some object, there doesn't
appear to be any logic to which one. It kept dying on
"&Relationship", but after I turned off import relationships, it just
crashed on a random form. If it crashes, I can sometimes make the
import dialog go away by pressing esc, but I usually can't close the
database (msaccess process won't quit), but the db window will go
away.

I get wierd object names in the database I am importing from, like
"tmprryQryFrLnkFldsCrtn0123456789". I was able to delete the
relationships in the 'corrupted' database, but after I did I couldn't
save it.

Any idea what is goin on? This happens quite regularly, and is really
starting to worry me.

Thanks,
Andrew Backer

.--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

 

[Tom Wickerath]

I do experience it in single user mode ( still with FE/BE ) while doing development at home or
at the office.
By chance, are you using a laptop PC for your development work? If you have a bad network
interfact card (NIC) in the laptop, this might explain the frequent corruptions while doing dev.
work at home or at the office....

Have you tried doing development work on a different PC at work?

Here is the only KB article I could find with the search string "Too many transactions already
nested":

BUG: SQLState S1000 with SQL_AUTOCOMMIT_OFF/SQLTransact
http://support.microsoft.com/?id=169469


Tom
_______________________________________________


Sorry, let me clarify...

In production usage the database is FE/BE split, with the FE being an MDE that is *copied* to
each users desktop.

I do not experience this problem in production, since it doesn't appear to 'crop up' unless I do
dev work. Once working and compiled, it hasn't yet broken in this way.

I do experience it in single user mode ( still with FE/BE ) while doing development at home or at
the office.

I just had it break on me again. I am able to import everything, but it's crashing on a form
again. This time the message is :

"Could not start transaction. too many transactions already nested."

Hrm...

- Andrew Backer
_______________________________________________


This is a multi-user db, but I have the problems single user or multi user. I usually don't
distribute it until it works fine, and then the MDE goes out. I am getting the current problems
on my dev machine.

I just opened a working copy of the db, changed a lot of code from using constants to using an
Enum. It saved & quit ok, but it is exhibiting the same problem when I open it now.

One thing I forgot to mention :

Access CRASHES when I try to compile the DB. It compiled fine before I closed & reopened, but
now it goes boom.



_______________________________________________

On Sun, 22 Aug 2004 15:18:15 -0700, "Tom Wickerath"

Hi Andrew,

Is this a single-user database located on one PC? Or, is it a multi-user database? If
multi-user, is it split into a front-end (FE) and back-end (BE) databases? Does each user have
their own copy of the FE database?

See the following articles:
http://www.access.qbuilt.com/html/gem_tips.html#SplitDB

http://members.iinet.net.au/~allenbrowne/ser-25.html

http://www.granite.ab.ca/access/corruptmdbs.htm
http://www.granite.ab.ca/access/bestpractices.htm


Tom
_______________________________________________


I am using A2k, and having quite a few scares with database corruption. Making me wonder if
access should even be used at all, since once again there is no logic to Access' misbehavior.

I have a startup form set, which should load every time I open the db. If it doesn't load, I know
something is wrong.

Sometimes a recompile will fix it, and it will start loading fine again. Other times it will
just die, and importing all objects into a new databaes will fix it.

It just did it to me again. I have tried to import to a new database, but on import it will
crash on importing some object, there doesn't appear to be any logic to which one. It kept dying
on "&Relationship", but after I turned off import relationships, it just crashed on a random
form. If it crashes, I can sometimes make the import dialog go away by pressing esc, but I
usually can't close the database (msaccess process won't quit), but the db window will go away.

I get wierd object names in the database I am importing from, like
"tmprryQryFrLnkFldsCrtn0123456789". I was able to delete the relationships in the 'corrupted'
database, but after I did I couldn't save it.

Any idea what is goin on? This happens quite regularly, and is really starting to worry me.

Thanks, Andrew Backer

..--------------------------------------
| Andrew Backer | backer_a @ h0tmai1 dot com `--

 

[Jamie]

Test the mdb on a different PC if possible. If it still crashes then there
is probably some form of corruption afoot, if not then there may be an error
with the installation of Access.

One option would be to try saving each form etc as a text file and then load
them into a new mdb to try to eradicate any corruption. You can do this
using saveastext from the immediate window and loadfromtext to reload them
into the new mdb. If you have a lot of objects it may be quicker to write a
VBA routine to loop through them all.

Jamie
www.jamiessoftware.tk

 

[Sandra Daigle]

Hi Andrew,

I understand and share your frustration. I too have a large db which in
production is extermely stable but in development causes me no end of grief.
In addition to the good advice others have given you I'd like to throw in a
couple of other ideas (you'll find these if you read the articles referenced
by otheres but these are important ones).

1) Make sure that Name Autocorrect is turned off. It causes all sorts of
problems - Here's Allen Brownes article on the subject:
http://members.iinet.net.au/~allenbrowne/bug-03.html.

2) Never change code in debug mode.

3) If your FE mdb is large you might consider trying to break it up into
smaller mdbs that are referenced by the primary FE mdb. If you are
distributing mdes this can be a bit painful since you have to produce mdes
for each of the referenced mdbs but it might be worth it since in my
experience a smaller mdb tends to corrupt less often.

Be sure to read Tony's page and Allen's page on corruption. There are many
things which potentially cause corruption:

Allen's: http://allenbrowne.com/ser-25.html
Tony's: http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Tony Toews]

In production usage the database is FE/BE split, with the FE being an
MDE that is *copied* to each users desktop.

I do not experience this problem in production, since it doesn't
appear to 'crop up' unless I do dev work. Once working and compiled,
it hasn't yet broken in this way.

Ah, good. You're doing things properly here.

I do experience it in single user mode ( still with FE/BE ) while
doing development at home or at the office.

Very interesting that this happens only on your system.

I just had it break on me again. I am able to import everything, but
it's crashing on a form again. This time the message is :

"Could not start transaction. too many transactions already nested."

Groups.google.com shows some hits on this problem. One of which
indicates the SPs weren't up to date. Do you have SP3 installed on
your system? And Jet 4.0 SP 8 or newer?

Microsoft Access Fixes, Patches and Updates
http://www.granite.ab.ca/access/msfixes.htm

Would the MDB happen to be replicated? If so that's definitely
*NOT* a good idea.

Have you done a decompile recently? That may help to clean things
up a bit. Decompile or how to reduce Microsoft Access MDB/MDE size
and decrease start-up times
http://www.granite.ab.ca/access/decompile.htm I'd suggest importing
to a new MDB afterwards though in your situation.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Andrew Backer]

It doesn't just happen on my desktop. It happens at the office (on
machine) and at home (a different machine). It has also happened on
any other machine I have developed on, though I did not include those
in my list.

I am going to look into the decompile. I haven't done that yet, but I
think that importing everything to a new mdb would take care of that,
right?

Ah, good. You're doing things properly here.


Very interesting that this happens only on your system.


Groups.google.com shows some hits on this problem. One of which
indicates the SPs weren't up to date. Do you have SP3 installed on
your system? And Jet 4.0 SP 8 or newer?

Microsoft Access Fixes, Patches and Updates
http://www.granite.ab.ca/access/msfixes.htm

Would the MDB happen to be replicated? If so that's definitely
*NOT* a good idea.

Have you done a decompile recently? That may help to clean things
up a bit. Decompile or how to reduce Microsoft Access MDB/MDE size
and decrease start-up times
http://www.granite.ab.ca/access/decompile.htm I'd suggest importing
to a new MDB afterwards though in your situation.

Tony

..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

 

[Andrew Backer]

Well, it's working again.

It appears that there were some issues with some forms that I no
longer use. I had just recently stopped using them since their
features were no longer necessary. To figure this out I imported each
form one at a time, saving backups in between imports.

I am not sure if these forms were the source of all the problems,
since It has never been this bad before. Prior to this, imports fixed
everything every time. Maybe these forms were a symptom rather than
the cause?

Here is an overview of what was happening
1. MDB runs fine
2. When done with dev, close the MDB (for VSS or just for lunch)
3. Re-open MDB and the Startup form will not load (first sign)
4. Attempt a re-compile (will sometimes fix it)
5. Re-compile fails, so make a blank db and import all objects (99%
success)
6. When that failed, I imported each object type separatly.
7. Found problem was on a form (hanging on &relationships)
8. Import one form at a time (or small blocks)
9. End up lucky that the problem forms (2 of em) not needed
10. Rejoice

This may have been a lot of stink for nothing, but the issues still
stands that I get some form of corruption quite regularly with Access.
I know I am not unique in this.

and

I am using A2k, and having quite a few scares with database
corruption. Making me wonder if access should even be used at all,
since once again there is no logic to Access' misbehavior.

I have a startup form set, which should load every time I open the db.
If it doesn't load, I know something is wrong.

Sometimes a recompile will fix it, and it will start loading fine
again. Other times it will just die, and importing all objects into a
new databaes will fix it.

It just did it to me again. I have tried to import to a new database,
but on import it will crash on importing some object, there doesn't
appear to be any logic to which one. It kept dying on
"&Relationship", but after I turned off import relationships, it just
crashed on a random form. If it crashes, I can sometimes make the
import dialog go away by pressing esc, but I usually can't close the
database (msaccess process won't quit), but the db window will go
away.

I get wierd object names in the database I am importing from, like
"tmprryQryFrLnkFldsCrtn0123456789". I was able to delete the
relationships in the 'corrupted' database, but after I did I couldn't
save it.

Any idea what is goin on? This happens quite regularly, and is really
starting to worry me.

Thanks,
Andrew Backer

.--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

 

[Andrew Backer]

Think I got this one whupped.

Thanks to eveyrone for all the suggestions and help. In the end it
was a combination that did it.

I was able to export *almost* all my objects as text. I had to
rewrite a function I found to not quit on error, so that what I was
left with was all my known good forms/etc. I did this because even
SaveAsText would crash when trying to export.

After getting a good one, I was able to open a prior version that
would let me export the still corrupt forms. However, though they
looked ok, they still broke when I reimported them. This killed the
db, so I had to remove them and use /decompile.

To finally get those in, I opened a blank form in the new db and
went into design mode in the corrup db. Copied all the objects and
pasted into the new form. Then I went and grabbed all the code for
the form, and did the same.

Hrm. As an aside, I noticed that when I exported all objects from the
really-corrupted version (crash on compile), I ended up with tons of
~qryliajsdlfijaslifjalsijy.txt files, like every temporary query for
every object was still hangin around.

Thanks again, and hopefully I can do as much for you guys sometime.

- Andrew Backer

I am using A2k, and having quite a few scares with database
corruption. Making me wonder if access should even be used at all,
since once again there is no logic to Access' misbehavior.

I have a startup form set, which should load every time I open the db.
If it doesn't load, I know something is wrong.

Sometimes a recompile will fix it, and it will start loading fine
again. Other times it will just die, and importing all objects into a
new databaes will fix it.

It just did it to me again. I have tried to import to a new database,
but on import it will crash on importing some object, there doesn't
appear to be any logic to which one. It kept dying on
"&Relationship", but after I turned off import relationships, it just
crashed on a random form. If it crashes, I can sometimes make the
import dialog go away by pressing esc, but I usually can't close the
database (msaccess process won't quit), but the db window will go
away.

I get wierd object names in the database I am importing from, like
"tmprryQryFrLnkFldsCrtn0123456789". I was able to delete the
relationships in the 'corrupted' database, but after I did I couldn't
save it.

Any idea what is goin on? This happens quite regularly, and is really
starting to worry me.

Thanks,
Andrew Backer

.--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

 

[Tony Toews]

It doesn't just happen on my desktop. It happens at the office (on
machine) and at home (a different machine). It has also happened on
any other machine I have developed on, though I did not include those
in my list.

I am going to look into the decompile. I haven't done that yet, but I
think that importing everything to a new mdb would take care of that,
right?

That's a good question and one I hadn't really thought of and one I
don't recall seeing addressed in the newsgroups. I don't have a
definitive answer but I'd strongly suspect that compacting would not
clean out orphaned compiled code from the system.

Ah, yes, when you make an MDE the final step is always compacting.
And I know due to my own personal knowledge that decompiling can make
a 2 or 4 Mb difference in the size of an MDE.

Also I know folks have said that a 40 Mb MDB, once decompiled for the
first time ever, have shrunk dramatically. Even though it was
compacted on a regular basis.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm