Why does my DB crash?

[Andrew]

I am currently designing a database, using Access 2003 running on
Windows Vista Premium Home.

The database file format is Access 2002 / 2003 (mdb). The database is
stored on a 1Gb USB memory stick so that I can work on it anywhere
without having to keep updating it on various machines. The disk has
never had any other errors, all other files are fine, it has plenty of
space and checkdisk reports no errors.

The database is split between a back end (not many records yet - a
file-size of about 1mb) and a front end with about 60 forms (inc
subforms) and about 40 queries. There is also a fairly large amount of
code, split between the various form modules and 7 standard modules.
The total front end size is about 3mb.

Until recently, everything has run smoothly, albeit with occasional
compile and logic errors as might be expected. I am therefore
currently going through each form, ironing out these bugs, improving
the code and generally tidying up. However, in the past week the
database has begun to crash. As an example, I finished work on it at
around 5.20 last night, tested that the work I'd done was behaving as
expected, and was happy that everything worked. This morning, opened
the db, opened various forms without problems, then tried to open one
called frmContacts. As soon as I did, I got the dreaded "Access has
stopped working. Please wait while we gather more information..."
message. All other forms were fine, that form was fine yesterday.
Closing the DB, then re-opening it, compacting and repairing, re-
compiling the VBA... none of this helped. Trying to access the module
for frmContacts caused the same problem.

I also had this problem earlier in the week. It was a completely
different form that caused the issue, but the symptoms were
identical.

In both cases, I have found that there is one solution. Create a
new .mdb file, and import all the objects from the "faulty" database.
This resolves the issue and I can continue working as though nothing
had happened. However, there is one last point of note. When importing
all the objects into a new database, everything runs smoothly until it
tries to import a standard module called "modUtiliti es". This is not a
module I have changed recently (probably last changed it about 6 weeks
ago), and it contains nothing complex or unusual. But when trying to
process this module, on both occasions the import has hung. The light
on the memory stick that indicates data transfer keeps flashing, and
pressing CTRL-Break makes no difference. Ultimately, after waiting for
about 10 minutes, I removed the memory stick. At that point the
transfer stopped (funnily enough!) and the "new" database worked fine
- including the full module modUtilities, which had imported fully.

Does anyone have any ideas why this might suddenly have started
happening, and more to the point, what I might be able to do to
prevent it happening in the future? I don't think the problem is with
modUtilities, but if it would help I'm happy to post it....

Thanks in advance

Andrew

[Allen Browne]

Andrew, there's a range of possible issues here.

The unusual one is that you are working directly on a USB drive. The trouble
with that is that if the USB stick is bumped or removed before Windows has
finished writing, the database will be corrupted. If everything worked
perfectly, and you always used closed Access first, and then the "Stop" icon
in the system tray to unmount the drive, this should not occur. But I
wouldn't trust it. Copy to hard disk. Work on it there, and copy back to the
USB again afterwards. As a side-benefit, you have a backup that way too.

As for what corrupts the database, it is usually an interrupted write. For
suggestions on how to avoid it, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html
There's a list of things to work through there.
Don't take any of the 13 items for granted.

--
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.

"Andrew" <an************ *@yahoo.co.ukwr ote in message
news:11******** **************@ n59g2000hsh.goo glegroups.com.. .

>I am currently designing a database, using Access 2003 running on
Windows Vista Premium Home.

The database file format is Access 2002 / 2003 (mdb). The database is
stored on a 1Gb USB memory stick so that I can work on it anywhere
without having to keep updating it on various machines. The disk has
never had any other errors, all other files are fine, it has plenty of
space and checkdisk reports no errors.

The database is split between a back end (not many records yet - a
file-size of about 1mb) and a front end with about 60 forms (inc
subforms) and about 40 queries. There is also a fairly large amount of
code, split between the various form modules and 7 standard modules.
The total front end size is about 3mb.

Until recently, everything has run smoothly, albeit with occasional
compile and logic errors as might be expected. I am therefore
currently going through each form, ironing out these bugs, improving
the code and generally tidying up. However, in the past week the
database has begun to crash. As an example, I finished work on it at
around 5.20 last night, tested that the work I'd done was behaving as
expected, and was happy that everything worked. This morning, opened
the db, opened various forms without problems, then tried to open one
called frmContacts. As soon as I did, I got the dreaded "Access has
stopped working. Please wait while we gather more information..."
message. All other forms were fine, that form was fine yesterday.
Closing the DB, then re-opening it, compacting and repairing, re-
compiling the VBA... none of this helped. Trying to access the module
for frmContacts caused the same problem.

I also had this problem earlier in the week. It was a completely
different form that caused the issue, but the symptoms were
identical.

In both cases, I have found that there is one solution. Create a
new .mdb file, and import all the objects from the "faulty" database.
This resolves the issue and I can continue working as though nothing
had happened. However, there is one last point of note. When importing
all the objects into a new database, everything runs smoothly until it
tries to import a standard module called "modUtiliti es". This is not a
module I have changed recently (probably last changed it about 6 weeks
ago), and it contains nothing complex or unusual. But when trying to
process this module, on both occasions the import has hung. The light
on the memory stick that indicates data transfer keeps flashing, and
pressing CTRL-Break makes no difference. Ultimately, after waiting for
about 10 minutes, I removed the memory stick. At that point the
transfer stopped (funnily enough!) and the "new" database worked fine
- including the full module modUtilities, which had imported fully.

Does anyone have any ideas why this might suddenly have started
happening, and more to the point, what I might be able to do to
prevent it happening in the future? I don't think the problem is with
modUtilities, but if it would help I'm happy to post it....

Thanks in advance

Andrew

[Andrew]

On Mar 22, 10:23 am, "Allen Browne" <AllenBro...@Se eSig.Invalid>
wrote:

Andrew, there's a range of possible issues here.

...........

As for what corrupts the database, it is usually an interrupted write. For
suggestions on how to avoid it, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html
There's a list of things to work through there.
Don't take any of the 13 items for granted.

Allen,

Thanks very much for the pointers. It may be that it was somehow
caused by an interrupted write, although I'm not sure when that would
have happened as I'm pretty careful about the various things that can
cause it. Nonetheless, some useful ideas, and I will move the db onto
the hard disk (I have various backups on HDD anyway).

Regarding your page on preventing corruption, can you point me in the
direction of finding more information on the SaveAsText and
LoadFromText commands? Also, how do I decompile the VBA in a database?

Thanks again for your help - much appreciated.

Andrew

[Albert D. Kallal]

>Also, how do I decompile the VBA in a database?

during development, I assume that you *frequently* do a compact and repair.

When working on code, if you modify some code, I usually hit a ctrl-s to
save
the code.

I then go debug->compile (alt-d, enter key occurs so often I don't even
think about it).

Also, I assume you turned off track-auto name correct.

You can build a short cut to do the de-compile..but, that a big hassle.

I would suggest that you download the registry entries here:

http://www.members.shaw.ca/AlbertKal.../accessreg.reg

After you install he above registry entries, you can right click on mdb
file..and you see two new options (compact and decompile).

Remember, when you do a de-compile, if you have any start-up forms..make
sure you hold down the shift key...(and, I would suggest you exit right
after you do the de-compile).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com

[David W. Fenton]

"Albert D. Kallal" <Pl************ *******@msn.com wrote in
news:1DtMh.4945 1$DN.1217@pd7ur f2no:

When working on code, if you modify some code, I usually hit a
ctrl-s to save
the code.

I then go debug->compile (alt-d, enter key occurs so often I don't
even think about it).

Now, that's interesting. I compile first, and *then* save, because I
don't want to discover a code error *after* I've saved. I also have
always assumed that SAVE saves both the code and the compilation. Am
I wrong on that?

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

[David W. Fenton]

"Albert D. Kallal" <Pl************ *******@msn.com wrote in
news:1DtMh.4945 1$DN.1217@pd7ur f2no:

You can build a short cut to do the de-compile..but, that a big
hassle.

???

I keep a shortcut on my desktop for each version of Access that
doesn't specify the database. So, when I execute it, I'm prompted to
open a database, and it gets decompiled.

Where's the hassle in that?

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

[AlterEgo]

Andrew,

Just one more idea. I version the database extensively when modifying code
for this very reason - sometimes 3 or 4 versions in one day. It has saved me
from some "unexplaine d" errors a number of times.

-- Bill
"Andrew" <an************ *@yahoo.co.ukwr ote in message
news:11******** **************@ p15g2000hsd.goo glegroups.com.. .

On Mar 22, 10:23 am, "Allen Browne" <AllenBro...@Se eSig.Invalid>
wrote:

>Andrew, there's a range of possible issues here.

..........

>As for what corrupts the database, it is usually an interrupted write.
For
suggestions on how to avoid it, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html
There's a list of things to work through there.
Don't take any of the 13 items for granted.

Allen,

Thanks very much for the pointers. It may be that it was somehow
caused by an interrupted write, although I'm not sure when that would
have happened as I'm pretty careful about the various things that can
cause it. Nonetheless, some useful ideas, and I will move the db onto
the hard disk (I have various backups on HDD anyway).

Regarding your page on preventing corruption, can you point me in the
direction of finding more information on the SaveAsText and
LoadFromText commands? Also, how do I decompile the VBA in a database?

Thanks again for your help - much appreciated.

Andrew

[Albert D. Kallal]

Now, that's interesting. I compile first, and *then* save, because I

don't want to discover a code error *after* I've saved. I also have
always assumed that SAVE saves both the code and the compilation. Am
I wrong on that?

No, you not wrong at all....

I tend to use ctrl s....even when not compiling. Just a habit of mine....

I do as a habit hit alt-d enter...then ctrl-s (I just entered into the code
editor..and tried what I been doing for 8 years -- I do hit alt d first!! --
I don't actually consciously think about this anymore, and just ried what I
do!!!).

Also, if I am done in the code, then after I compile, I do a ctrl-f4 (close
the code), and do ctrl-s (if you compile, alt-f4 close, then save..the code
module window will not re-open for that module).

So, as a habit...I do (and have been) doing a debug-compile first....., then
ctrl-s.

I stand corrected on what I said..and even actually what I do...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com

[Albert D. Kallal]

I keep a shortcut on my desktop for each version of Access that

doesn't specify the database. So, when I execute it, I'm prompted to
open a database, and it gets decompiled.

Where's the hassle in that?

Most people assume that you have to create a specify shortcut to the
particular mdb. (and, I am one of them!!).

In fact, I was not aware using a shortcut to ms-access.exe with the
/decompile, but NOT supplying a mdb file name would even work (I have not
seen this before!!).

Very neat-o. I was not aware this was possible!!!

On the other hand, I not needed nor used the shortcut idea, since I use the
use the registry entry that gives a context right click. That right click
also gives a compact option also (this allows me to simply right click on
the mdb, and de-compile -- I don't have to go back to the desktop to run a
shortcut, and *then* open a file. Further, I only run ONE version of
ms-access on my dev machine - other versions are installed to virtual pc
now.

so, while I prefer the right click idea....I was not aware you could use
/decompile without supplying the file name.

Your approach certainly makes building a shortcut a lot less of a hassle...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com