Database Keeps Crashing

[Becky N]

Hello,

I have an Access database (version 2003) that serves as a call center log.
There are about 6 people who log call information into this database. There
is one main table where all of the information loads into. We have been
experiencing issues with the database crashing. If someone searches for a
call and scrolls down the table when it hits certain records the database
will crash. There is an error that says "MS Office Access has encountered a
problem and needs to close. We are sorry for the inconvenience." There is a
check box on there if we want to "Repair my open database and restart in MS
Office Access." If I leave it checked, when it is repairing the database it
crashes again and it creates a backup. I've also tried compacting the
database. I get this same error when I do that.

I noticed that in the table some fields have #error in them. If I try to
delete the record I get an error that says "The search key was not found in
any record." If I try to copy the record I get an error that says "Invalid
Argument" and then says "The MS Jet DB engine stopped the process because you
and another user are attempting to change the same data at the same time."
The only thing is, I'm the only user in the database when I am doing this.

Can someone please provide some insight as to why some of the fields have
#error in them and how I can fix this? My other issue is that my table has
record numbers that are autonumbered. I would just copy and paste the clean
records into a new table, but I'll loose my autonumbers. Please help!

Thank you!

Becky

 

[MikeJohnB]

Hi Becky

I don't know if this is your problem but which Office service pack has been
installed? Service Pack 3 has issues that sound a lot like what you have but
it may not be in your case. However, please check your SP issue, Help about
Microsoft Office Access will tell you which service pack is currently
installed. if it is service pack, make sure that the hotfix has been
downloaded and installed. Below, I have included some of the blurb from the
hotfix page which describes the issues addressed together with the hotfix
link.

http://support.microsoft.com/kb/945674

Just some of the text from the Hotfix blurb, I hope this sorts out your
problems? Please repost if you still have issues.

This hotfix package also fixes the following issues that were not previously
documented in a Microsoft Knowledge Base article:

Access 2003 may close unexpectedly when you do one of the following:
You open a table that contains a LOOKUP field. The table uses the LOOKUP
field to look up data in a table that contains more than 30 fields.

You open a form that contains a combo box that looks up data in a table that
contains more than 30 fields.

Additionally, you receive the following error message:
Microsoft Office Access has encountered a problem and needs to close. We are
sorry for the inconvenience.

When you view the data in the error report, the report contains an error
signature that resembles the following error signature:

(My additional Note) Also, combo and list boxes appear blank if you have
formatting setup in the table.

(I don't know what this means but I must have got it from the site)
Collapse this tableExpand this table
Application name Application version Module name Module version Offset
Msaccess.exe 11.0.8166.0 Ntdll.dll 5.2.3790.3959 45d70ad8


I hope this helps but if its not this issue, please re-post

Kindest Regards
Mike B

 

[Becky N]

Hi Mike,

Thank you for your reply. I checked and it appears we only have service pack
1 installed. Could that be the problem? Unfortunately I do not have the
ability to install any service packs as this is an application at work and
only IT can install updates. I'm also not sure that it would fix my issue as
I checked the error report that I get against the information provided on
that site and it didn't match at all besides the application name.

I'm not sure when the #error appears in the records in the table, but I get
the errors that kick me out in the following situations: compacting the
database, scrolling through the main table and coming across one of the
corrupt records, and scrolling through a lookup form based on the table and
coming across a corrupt record. So based on the examples from the page that
has the hotfix it kind of sounds similar to my issue. The only thing is that
I am only on SP1.

Any other thoughts? I really appreciate all the help with this. This has
been a very frustrating issue for the past 6 months for us.

Becky

 

[ntc]

does each user have their own separate FE?

 

[MikeJohnB]

Hi Again Becky

Well the issue with SP3 was well worth checking. I am not sure what issue
SP2 and SP3 address, the may well add functionallities which sort out your
problem. I know that SP3 was a disaster for Access when it first hit the
street, I have this installed at home but have also installed the hotfix and
everything appears stable now.

We, at work are in the same boat as you, IT control the issuing of service
packs and we normally lag well behind the issues in order to capture any
modifications and ensure that the service pack is stable before implementing
it. We haven't as yet installed the latest XP service pack for example, this
having issues with our LAN. Having added all this, I am not aware of any
further solutions but perhaps others on here may well be able to help you.

Should you find you do not receive any further answers on this question, try
re-posting in case people think that a solution has been found because it has
sunk down the newsgroup.

I hope you manage to solve the problem.

If I find any more help on this subject, I will repost.

Kindest Regards

Mike B

 

[Becky N]

If I understand your question correctly, I believe that yes, they do. They
all have a shortcut on their desktop to the database, but once they are in
the database they select their own account. Therefore, we have record of who
is logging what in the database. However, they can search/view all records,
regardless of who entered it. But they should only be creating new records
under their own account.

 

[ntc]

no no no...you don't understand; the good news is that this is probably the
cause of your issues.

those shortcuts are probably all going to the same FE or possibly the same
DB - - I see this alot.

the db must be split into an FE and a BE. then the FE must be on the
desktop of each user with linking back to the BE...

if all this is gobblegook...you really need to search/educate on splitting
the database and linking....

am sure this will fix your issues.... either that or only let one person
work in the db at a time.....

 

[David W. Fenton]

I have an Access database (version 2003) that serves as a call
center log. There are about 6 people who log call information into
this database. There is one main table where all of the
information loads into. We have been experiencing issues with the
database crashing. If someone searches for a call and scrolls down
the table when it hits certain records the database will crash.
There is an error that says "MS Office Access has encountered a
problem and needs to close. We are sorry for the inconvenience."
There is a check box on there if we want to "Repair my open
database and restart in MS Office Access." If I leave it checked,
when it is repairing the database it crashes again and it creates
a backup. I've also tried compacting the database. I get this same
error when I do that.

I noticed that in the table some fields have #error in them. If I
try to delete the record I get an error that says "The search key
was not found in any record." If I try to copy the record I get an
error that says "Invalid Argument" and then says "The MS Jet DB
engine stopped the process because you and another user are
attempting to change the same data at the same time." The only
thing is, I'm the only user in the database when I am doing this.

Can someone please provide some insight as to why some of the
fields have #error in them and how I can fix this? My other issue
is that my table has record numbers that are autonumbered. I would
just copy and paste the clean records into a new table, but I'll
loose my autonumbers. Please help!

Sounds like typical memo field corruption. Does the table have memo
fields? If so, once you get the data recovered, you might want to
change your forms to edit the memos in unbound textboxes, instead of
in bound textboxes. The way you do that is that you include the memo
field in the form's recordsource, then in the OnCurrent event, copy
the data from the recordsource into the unbound control:

Me!txtMemo = Me!Memo

Then in the AfterUpdate of the unbound textbox, copy the data back
to the field in the underlying recordsource:

Me!Memo = Me!txtMemo

For troubleshooting memo field corruption, I suggest you read
through Tony Toews's Access corruption article:

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

I'm pretty sure it explains in that article (or in an article linked
from there) how to do the troubleshooting and fixing for corrupt
memo fields.

 

[Larry Linson]

A "front end" (what ntc persists in calling FE which a new user would be
unlikely to understand) is te user interface: queries, forms, reports,
macros, modules, and perhaps rarely-changed local lookup tables -- it will
be linked to tables in a "back end" (or BE) the data store (tables, with
their data, and relationships).

Each user should have their own copy of the front end on their own machine,
but all the front ends are linked to the tables in the same copy of the back
end that is stored in a shared folder (can be on a workstation on the LAN or
on a server). Each user must have full permissions for the shared folder:
create and delete files, add, update, and delete data.

This is called "Access multiuser". If you were linking to tables in a
server database (e.g., MS SQL Server, Oracle, etc.) that would be
"client-server".

There's good coverage of multiuser in a number of sites. You'll find a short
list of MVP's (current and past) sites at
http://sp.ntpcug.org/accesssig/default.aspx -- of particular interest will
be Tony Toews' and Jeff Conrad's sites.

Larry Linson
Microsoft Office Access MVP

 

[Becky N]

Thank you David and Larry for your very helpful tips (and thanks ntc for
bringing up the "FE" issue). I was able to get rid of my current errors using
an append query to a new table that exluded the record numbers of the records
with the corrupted fields. And yes, I am using memo fields and those are
typically the ones with the errors. I will create a true front end and back
end and also try the memo field fix that you suggest to prevent this from
happening in the future.

Thanks for all your help!

 

[David W. Fenton]

Each user should have their own copy of the front end on their own
machine, but all the front ends are linked to the tables in the
same copy of the back end that is stored in a shared folder (can
be on a workstation on the LAN or on a server). Each user must
have full permissions for the shared folder: create and delete
files, add, update, and delete data.

There is no such thing as "delete data" permission in NTFS.

And you delete permission on the folder is not *required*.