|  | Database keeps corrupting | | |
| | | Ben M |  |
|  Posted: Wed Aug 27, 2008 1:55 pm Post subject: Database keeps corrupting |  |
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 |
| |
| | | Roger Carlson | |
| | Posted: Wed Aug 27, 2008 2:17 pm Post subject: Re: Database keeps corrupting | |
| |  | |
Look at these corruption links:
a.. Microsoft Access Corruption FAQ at
LINK
a.. Recovering from corruption
LINK
a.. Fix Corrupt Access Database v4.5
LINK
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:
LINK)
--
--Roger Carlson
MS Access MVP
Access Database Samples: LINK
Want answers to your Access questions in your Email?
Free subscription:
LINK
"Ben M" <BenM@discussions.microsoft.com> wrote in message
news:718BF03D-2B7B-4A98-97DF-22212A0A0B5E@microsoft.com...
| Quote: | 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 |
|
| |
| | | Larry Linson | |
| | Posted: Wed Aug 27, 2008 2:25 pm Post subject: Re: Database keeps corrupting | |
| | | |
MVP Tony Toews' site, LINK 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
LINK
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" <BenM@discussions.microsoft.com> wrote in message
news:718BF03D-2B7B-4A98-97DF-22212A0A0B5E@microsoft.com...
| Quote: | 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 |
|
| |
| | | Ben M | |
| | Posted: Thu Aug 28, 2008 7:19 am Post subject: Re: Database keeps corrupting | |
| | | |
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:
| Quote: | MVP Tony Toews' site, LINK 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
LINK
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" <BenM@discussions.microsoft.com> 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
|
|
| |
| | | Paul Shapiro | |
| | Posted: Thu Aug 28, 2008 10:45 am Post subject: Re: Database keeps corrupting | |
| | | |
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" <BenM@discussions.microsoft.com> wrote in message
news:6B66303A-D799-480E-8ADC-9F430C495E5B@microsoft.com...
| Quote: | 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, LINK 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
LINK
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" <BenM@discussions.microsoft.com> 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
|
|
| |
| | | Ben M | |
| | Posted: Thu Aug 28, 2008 11:22 am Post subject: Re: Database keeps corrupting | |
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 |
| |
| | | Paul Shapiro | |
| | Posted: Thu Aug 28, 2008 11:42 am Post subject: Re: Database keeps corrupting | |
| | | |
You can see if any of Allen Browne's Corrupt Repair tips apply:
LINK
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. LINK 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" <BenM@discussions.microsoft.com> wrote in message
news:9828B87A-FCEF-4256-997F-F9311A999F23@microsoft.com...
| Quote: | 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 M | |
| | Posted: Thu Aug 28, 2008 12:44 pm Post subject: Re: Database keeps corrupting | |
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 |
| |
| | | Tony Toews [MVP] | |
| | Posted: Thu Aug 28, 2008 4:00 pm Post subject: Re: Database keeps corrupting | |
| | | |
Ben M <BenM@discussions.microsoft.com> wrote:
| Quote: | 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 LINK for more info. See the
Auto FE Updater downloads page
LINK 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
LINK
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
LINK
Tony's Microsoft Access Blog - LINK |
| |
| | | David W. Fenton | |
| | Posted: Thu Aug 28, 2008 7:07 pm Post subject: Re: Database keeps corrupting | |
=?Utf-8?B?QmVuIE0=?= <BenM@discussions.microsoft.com> wrote in
news:6B66303A-D799-480E-8ADC-9F430C495E5B@microsoft.com:
| Quote: | 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 LINK
usenet at dfenton dot com LINK |
| |
| Page 1 of 3 .:. Goto page 1, 2, 3 Next | |
|
|
