I have an Access 2000-2003 database that is continually corrupting. Even if i 
make a copy and open the copy on a single machine, just doing a single action 
like opening a table or a form can corrupt the database.

I have tried compacting and repairing which doesn't help. The database is 
quite a complex one but is not split into a front end and a back end. There 
is a lot of code in the database. The database is about 6 years old and about 
33mb. It is generally accessed by between 2 and 5 users using Access 2003 on 
a XP OS. 

Can anyone give me any help as to what i could do to make the database more 
stable? At the moment, almost any design change seems to trigger it to 
corrupt.

Ben

Look at these corruption links:
a.. Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm
a.. Recovering from corruption
http://allenbrowne.com/ser-47.html
a.. Fix Corrupt Access Database v4.5
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=25

Have you tried creating a NEW database and importing all the objects from 
the old to the new?
Have you tried splitting the database?
Have you tried decompiling the database? (you can find instructions here: 
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=253)

-- 
--Roger Carlson
  MS Access MVP
  Access Database Samples: www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
  http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"Ben M"  wrote in message 
news:718BF03D-2B7B-4A98-97DF-22212A0A0B5E@microsoft.com...
>I have an Access 2000-2003 database that is continually corrupting. Even if 
>i
> make a copy and open the copy on a single machine, just doing a single 
> action
> like opening a table or a form can corrupt the database.
>
> I have tried compacting and repairing which doesn't help. The database is
> quite a complex one but is not split into a front end and a back end. 
> There
> is a lot of code in the database. The database is about 6 years old and 
> about
> 33mb. It is generally accessed by between 2 and 5 users using Access 2003 
> on
> a XP OS.
>
> Can anyone give me any help as to what i could do to make the database 
> more
> stable? At the moment, almost any design change seems to trigger it to
> corrupt.
>
> Ben

MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm has much 
information on multiuser Access database applications, including avoiding 
corruption and performance.  You will find several other sites, rich with 
Access information, in the Resources list at 
http://sp.ntpcug.org/accesssig/.

The immediate first step I would advise is to split your database into front 
end or FE (queries, forms, reports, macros, and modules) with a copy on each 
user's machine, and a back end or BE (tables, data, and relationships) on a 
shared folder (because of the number of connections available, preferrably 
on a server). Having multiple users logged in to the same front end or 
monolithic database substantially increases the probability of corruption --  
some people go for years without experiencing much or any corruption in that 
environment, and then some minor change will trigger frequent corruptions.

Other steps may include creating a new empty database and importing each 
object from the FE into a new FE, doing the same for the BE; simply updating 
your version of Access with all current Service Packs, or moving to another 
version of Access; using the SaveAsText and LoadFromText to eliminate the 
possibility of "silent corruption" that is hidden some of the time and 
occasionally manifests itself in the corruption you are seeing. But these 
are all covered, and in detail, in the references above.

You will, no doubt, receive advice that "Jet is unsuitable" and that you 
should upsize to SQL Server.  In certain cases, that can help with the 
problem, but it is no panacea or cure-all, and there are many approaches 
that usually alleviate the problem with Jet, without investing in the time, 
effort, and expertise necessary to install and maintain a server database. 
Usually, in this newsgroup, such advice comes from a single poster, labeled 
by many as "just a troll" because his posts lack technical depth and 
substance.

 Larry Linson
 Microsoft Office Access MVP

"Ben M"  wrote in message 
news:718BF03D-2B7B-4A98-97DF-22212A0A0B5E@microsoft.com...
>I have an Access 2000-2003 database that is continually corrupting. Even if 
>i
> make a copy and open the copy on a single machine, just doing a single 
> action
> like opening a table or a form can corrupt the database.
>
> I have tried compacting and repairing which doesn't help. The database is
> quite a complex one but is not split into a front end and a back end. 
> There
> is a lot of code in the database. The database is about 6 years old and 
> about
> 33mb. It is generally accessed by between 2 and 5 users using Access 2003 
> on
> a XP OS.
>
> Can anyone give me any help as to what i could do to make the database 
> more
> stable? At the moment, almost any design change seems to trigger it to
> corrupt.
>
> Ben

Thanks Larry and Roger for the information. It is very helpful. I have 
already tried on several occasions creating a new database and importing the 
objects from the old database into it. However the new database always 
corrupts in the middle of the import process at different points.

I'll look into the other options you talked about and do some more research. 
I do think splitting the database may not be practical as it is likely to 
slow the database down so much. This database is used on a large University 
Network, so I'm not sure how fast their network is.

Ben

"Larry Linson" wrote:

> MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm has much 
> information on multiuser Access database applications, including avoiding 
> corruption and performance.  You will find several other sites, rich with 
> Access information, in the Resources list at 
> http://sp.ntpcug.org/accesssig/.
> 
> The immediate first step I would advise is to split your database into front 
> end or FE (queries, forms, reports, macros, and modules) with a copy on each 
> user's machine, and a back end or BE (tables, data, and relationships) on a 
> shared folder (because of the number of connections available, preferrably 
> on a server). Having multiple users logged in to the same front end or 
> monolithic database substantially increases the probability of corruption --  
> some people go for years without experiencing much or any corruption in that 
> environment, and then some minor change will trigger frequent corruptions.
> 
> Other steps may include creating a new empty database and importing each 
> object from the FE into a new FE, doing the same for the BE; simply updating 
> your version of Access with all current Service Packs, or moving to another 
> version of Access; using the SaveAsText and LoadFromText to eliminate the 
> possibility of "silent corruption" that is hidden some of the time and 
> occasionally manifests itself in the corruption you are seeing. But these 
> are all covered, and in detail, in the references above.
> 
> You will, no doubt, receive advice that "Jet is unsuitable" and that you 
> should upsize to SQL Server.  In certain cases, that can help with the 
> problem, but it is no panacea or cure-all, and there are many approaches 
> that usually alleviate the problem with Jet, without investing in the time, 
> effort, and expertise necessary to install and maintain a server database. 
> Usually, in this newsgroup, such advice comes from a single poster, labeled 
> by many as "just a troll" because his posts lack technical depth and 
> substance.
> 
>  Larry Linson
>  Microsoft Office Access MVP
> 
> "Ben M"  wrote in message 
> news:718BF03D-2B7B-4A98-97DF-22212A0A0B5E@microsoft.com...
> >I have an Access 2000-2003 database that is continually corrupting. Even if 
> >i
> > make a copy and open the copy on a single machine, just doing a single 
> > action
> > like opening a table or a form can corrupt the database.
> >
> > I have tried compacting and repairing which doesn't help. The database is
> > quite a complex one but is not split into a front end and a back end. 
> > There
> > is a lot of code in the database. The database is about 6 years old and 
> > about
> > 33mb. It is generally accessed by between 2 and 5 users using Access 2003 
> > on
> > a XP OS.
> >
> > Can anyone give me any help as to what i could do to make the database 
> > more
> > stable? At the moment, almost any design change seems to trigger it to
> > corrupt.
> >
> > Ben 
> 
> 
>

That's the indication that there are one or more corrupted objects. Try 
importing subsets of the objects until you can directly identify the 
corrupted ones. If you've already split the database, the tables are not the 
issue in the FE. You could start by importing all the forms. If that works, 
then try all the reports, etc. If any group fails, try again, importing half 
the objects. Etc.

Once you identify the corrupted object(s), create a clean database and 
import all the good objects. Then you can either import older copies of the 
corrupted objects from backups before the corruption, or re-create them.

"Ben M"  wrote in message 
news:6B66303A-D799-480E-8ADC-9F430C495E5B@microsoft.com...
> Thanks Larry and Roger for the information. It is very helpful. I have
> already tried on several occasions creating a new database and importing 
> the
> objects from the old database into it. However the new database always
> corrupts in the middle of the import process at different points.
>
> I'll look into the other options you talked about and do some more 
> research.
> I do think splitting the database may not be practical as it is likely to
> slow the database down so much. This database is used on a large 
> University
> Network, so I'm not sure how fast their network is.
>
> Ben
>
> "Larry Linson" wrote:
>
>> MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm has much
>> information on multiuser Access database applications, including avoiding
>> corruption and performance.  You will find several other sites, rich with
>> Access information, in the Resources list at
>> http://sp.ntpcug.org/accesssig/.
>>
>> The immediate first step I would advise is to split your database into 
>> front
>> end or FE (queries, forms, reports, macros, and modules) with a copy on 
>> each
>> user's machine, and a back end or BE (tables, data, and relationships) on 
>> a
>> shared folder (because of the number of connections available, 
>> preferrably
>> on a server). Having multiple users logged in to the same front end or
>> monolithic database substantially increases the probability of 
>> corruption --
>> some people go for years without experiencing much or any corruption in 
>> that
>> environment, and then some minor change will trigger frequent 
>> corruptions.
>>
>> Other steps may include creating a new empty database and importing each
>> object from the FE into a new FE, doing the same for the BE; simply 
>> updating
>> your version of Access with all current Service Packs, or moving to 
>> another
>> version of Access; using the SaveAsText and LoadFromText to eliminate the
>> possibility of "silent corruption" that is hidden some of the time and
>> occasionally manifests itself in the corruption you are seeing. But these
>> are all covered, and in detail, in the references above.
>>
>> You will, no doubt, receive advice that "Jet is unsuitable" and that you
>> should upsize to SQL Server.  In certain cases, that can help with the
>> problem, but it is no panacea or cure-all, and there are many approaches
>> that usually alleviate the problem with Jet, without investing in the 
>> time,
>> effort, and expertise necessary to install and maintain a server 
>> database.
>> Usually, in this newsgroup, such advice comes from a single poster, 
>> labeled
>> by many as "just a troll" because his posts lack technical depth and
>> substance.
>>
>>  Larry Linson
>>  Microsoft Office Access MVP
>>
>> "Ben M"  wrote in message
>> news:718BF03D-2B7B-4A98-97DF-22212A0A0B5E@microsoft.com...
>> >I have an Access 2000-2003 database that is continually corrupting. Even 
>> >if
>> >i
>> > make a copy and open the copy on a single machine, just doing a single
>> > action
>> > like opening a table or a form can corrupt the database.
>> >
>> > I have tried compacting and repairing which doesn't help. The database 
>> > is
>> > quite a complex one but is not split into a front end and a back end.
>> > There
>> > is a lot of code in the database. The database is about 6 years old and
>> > about
>> > 33mb. It is generally accessed by between 2 and 5 users using Access 
>> > 2003
>> > on
>> > a XP OS.
>> >
>> > Can anyone give me any help as to what i could do to make the database
>> > more
>> > stable? At the moment, almost any design change seems to trigger it to
>> > corrupt.
>> >
>> > Ben
>>
>>
>>

Hi Paul,

Thanks very much for your reply. It was very helpful. I have located one of 
the corruption issues as being in one of the tables (I'm not sure if this is 
the only one!). Every time i open the table, the database corrupts. Do you 
know of a way i can locate the corrupt data and get rid of it without the 
need to open the table?

This is one of the main tables which i will not be able to replace from a 
back up.

Ben

You can see if any of Allen Browne's Corrupt Repair tips apply: 
http://www.allenbrowne.com/tips.html

I guess you've already tried importing just that single table into a new db, 
and it fails? You've also probably done a Compact and Repair, without any 
improvement?

Other possibilities might be:
1. http://support.microsoft.com/kb/283849 offers some suggestions, including 
exporting the table from Access in delimited text format and then 
reimporting to a new table.
2. Try linking to that table from a new db. If you can open a recordset with 
VBA, you might be able to loop through the records inserting them into a new 
table in the linking db. When it fails, try restarting from a later record 
to see if you can skip individual corrupted records.
3. You could see if another application can import the data from that table, 
like Excel. Probably not, but you never know.

Last possibility is one of the commercial Access recovery services. They are 
probably expensive, and I've never used any, but several have excellent 
reputations. Search on google since I don't remember any names.

"Ben M"  wrote in message 
news:9828B87A-FCEF-4256-997F-F9311A999F23@microsoft.com...
> Hi Paul,
>
> Thanks very much for your reply. It was very helpful. I have located one 
> of
> the corruption issues as being in one of the tables (I'm not sure if this 
> is
> the only one!). Every time i open the table, the database corrupts. Do you
> know of a way i can locate the corrupt data and get rid of it without the
> need to open the table?
>
> This is one of the main tables which i will not be able to replace from a
> back up.

Hi Paul,

Thanks for your response. I am currently using a make-table query to copy 
the corrupted table into a new database. For some reason, this seems to be 
working. I then delete the corrupted table from the original database and 
import it back from the new database.

Don't know why but this seems to have fixed some of the problems. More than 
one of the tables is corrupt so I'm going through all the corrupt tables. 
There may be other corruption issues but this certainly seems to be resolving 
some of them.

Ben

Ben M  wrote:

>I'll look into the other options you talked about and do some more research. 
>I do think splitting the database may not be practical as it is likely to 
>slow the database down so much. This database is used on a large University 
>Network, so I'm not sure how fast their network is.

You have a rock and a hard place choice.

1) If you do not split the database 
a) it will be next to impossible for you to do any development work on
the objects while others are in the database

b) The chances of corruption are greatly increased when people are
sharing the objects other than tables.

c) You will have weird and interesting problems.

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info.  See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless..   The utility also supports Terminal Server/Citrix quite
nicely.

2) If you do split the database yes performance will be slower.
However you can get around all those problems with varying amounts of
work.  Some solutions are real simple such as opening a bound form at
all times.

Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

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
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

=?Utf-8?B?QmVuIE0=?=  wrote in
news:6B66303A-D799-480E-8ADC-9F430C495E5B@microsoft.com: 

> I do think splitting the database may not be practical as it is
> likely to slow the database down so much.

And constant corruption doesn't slow things down?

Every Access application with more than one user should be split. NO
EXCEPTIONS. 

Every Access application where the user needs to get updates to the
forms/reports from someone else should be split, even if it has only
one user. NO EXCEPTIONS. 

Every replicated Access app should be split. NO EXCEPTIONS.

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

Thanks for your responses. It certainly seems like you both feel the best 
course of action is to split the database. I may take up your advice and let 
you know how i get on.

Ben

Thanks for your responses. It certainly seems like you both want me to split 
the database as the best course of action. I may take you up on your advice 
and will let you know how i get on.

Ben

"Ben M"  wrote

 > Thanks for your responses. It certainly seems like you
 > both want me to split the database as the best course
 > of action.

Knowing these two people from years and years of interacting with them in 
newsgroups, I can assure you that they don't think you should split "because 
they want you to" -- they are advising you to split because not doing so can 
cause you a great deal of stress, strife, and trauma.  People may go for 
years without problems, make some small (apparently-innocuous) change, and 
<BANG> frequent corruption. Splitting also expedites "offline development" 
and being able to replace the front-end without having an "exercise in 
copying data" as part of the procedure. I caution, "You don't know when that 
will cause Access to rise up and bite you in the tender places."

 Larry Linson
 Microsoft Office Access MVP

hi Larry

Thanks for your response. I am continuing to have problems with my database. 
 I create two new databases, a backend and the front end.I import all the 
tables into the back-end and all the other objects into the front end. If any 
objects fail to import because they are corrupted I use the loadfromtext 
command to create duplicates. I then have a brand new database both front-end 
and back-end. However almost as soon as side start to alter any of the 
objects or even open one of the forms it corrupts all over again. The message 
I get is " Microsoft access has encountered a problem and needs to close".

I can't seem to get out of this problem. It will take me too long to 
recreate the database from scratch so this is not a viable alternative, and 
now I am not sure what to suggest to the client. If I cannot fix it they will 
be unable to continue using the database.

They are using the XP operating system with service Pack 3 installed, and 
office 2003 also with service Pack 3 installed. They also have the latest hot 
fixes applied. I would be grateful if you or anyone else could suggest 
anything that may help.

Ben

I forgot to add that when I take a copy of the database home and open it 
using Office 2007 on Windows vista, it works fine. It only seems to be 
unstable in office 2003 on XP.

On Wed, 3 Sep 2008 06:05:01 -0700, Ben M 
wrote:

>I forgot to add that when I take a copy of the database home and open it 
>using Office 2007 on Windows vista, it works fine. It only seems to be 
>unstable in office 2003 on XP.

You may need to completely uninstall Office on the offending machine and
reinstall it. The problem may not be with the database but with the instance
of Access.
-- 

             John W. Vinson [MVP]

=?Utf-8?B?QmVuIE0=?=  wrote in
news:CC73BCDD-CB22-4D1D-8AC2-9FB63D42D4CC@microsoft.com: 

> I am continuing to have problems with my database. 
>  I create two new databases, a backend and the front end.I import
>  all the 
> tables into the back-end and all the other objects into the front
> end. 

Try importing *everything* with LoadFromText. Otherwise, you can end
up silently importing corruption that Access doesn't detect during
the import process. 

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

John W. Vinson  wrote in
news:dpetb4p98bsk27tefgf6rrqiet4v3mopd3@4ax.com: 

> On Wed, 3 Sep 2008 06:05:01 -0700, Ben M
>  wrote:
> 
>>I forgot to add that when I take a copy of the database home and
>>open it using Office 2007 on Windows vista, it works fine. It only
>>seems to be unstable in office 2003 on XP.
> 
> You may need to completely uninstall Office on the offending
> machine and reinstall it. The problem may not be with the database
> but with the instance of Access.

Or possibly a hard drive problem?

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

hi John

Thank you for your comment. I don't think the problem is with the instance 
of acces itself. the database corrupts on all machines with XP and office 
2003.I even took the database home and tried it on my home machine and it 
corrupted when I used it with XP and office 2003.

Ben

> You may need to completely uninstall Office on the offending machine and
> reinstall it. The problem may not be with the database but with the instance
> of Access.
> -- 
> 
>              John W. Vinson [MVP]
>

hi David

Thanks for your suggestion. I'll give it a go.

Ben

Conclusion

I've finally managed to sort out most of the corruption problems. I started 
by deleting all the records in the tables that crashed the database when 
opened. This didn't make any difference. I then went through the fields 
deleting the fields to see which ones caused the crashing. I found out that 
the crashing was being caused by some look-up fields within the table. Not 
all the look-up fields were causing problems, but some of them were. I then 
simply changed all the look-up fields that were causing problems to 
non-look-up fields and this fixed the problem. I did this for each of the 
tables that crashed on open.

I then noticed that the hotfix for look-up fields had not been installed. I 
installed it, but it didn't make any difference to the tables crashing on 
open. I still needed to remove the look-up fields and change them to 
non-look-up fields. I had also, before that, installed service pack 3 for XP. 
This also had not made any difference.

There is still some forms with corruption problems, and i am fixing these by 
creating new forms and replacing them. I tried using the LoadFromText command 
but this didn't stop them from crashing.

I have now split the database into a front end and a back end, and each user 
has a copy of the front end on their local machine. I am using the autoFE 
updater provided at http://www.granite.ab.ca/access/autofe.htm. Everything 
seems to be working much better now. 

Thank you all for your help.

Ben

www.benmango.co.uk

On Mon, 8 Sep 2008 03:06:12 -0700, Ben M 
wrote:

>I found out that 
>the crashing was being caused by some look-up fields within the table. 

That's VERY interesting, Ben. Another arrow (a sharp one!!) in the quiver for
those of us who dislike Lookup Fields...

Of course, as you now realize, lookups are never necessary; but this is just
another good reason to avoid using them entirely.
-- 

             John W. Vinson [MVP]