|
Home > Archive > MS Access Multiuser > April 2005 > back end corruption
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
back end corruption
|
|
|
| I have a system with an Access 200 front end and Access 200 back end. there
are approximately25 users on the system at any one time. When I occasionally
(every 4 - 6 months or so) compact the back end, for the next week or so, I
have to repair the back end on a daily basis.
I will get a report from one user that the back end is corrupted. All other
users can continue without error until they log off and try to log back on
again.
Any ideas as to why this may be happening, or how to prevent it?
| |
| Nikos Yannacopoulos 2005-04-06, 8:05 pm |
| Nik,
To begin with, a 4-6 month period between compactions sounds waaaaay too
long! I would have thought of weekly as the absolute minimum (I do nightly).
That said, there's gotta be a reason for that behaviour, and I suspect
that is rubbish left-overs in either the FE / BE; the best way to clean
them up is to produce a fresh, clean copy, by importing everything into
a new, blank database (no, compacting and repairing won't clean those
off). I'd do that on both ends, and re-distribute the new, clean FE to
the users. I suppose you have given each user their own separate copy of
the FE, haven't you? If not, this is also a possible reason for what
you're facing.
HTH,
Nikos
Nik wrote:
> I have a system with an Access 200 front end and Access 200 back end. there
> are approximately25 users on the system at any one time. When I occasionally
> (every 4 - 6 months or so) compact the back end, for the next week or so, I
> have to repair the back end on a daily basis.
>
> I will get a report from one user that the back end is corrupted. All other
> users can continue without error until they log off and try to log back on
> again.
>
> Any ideas as to why this may be happening, or how to prevent it?
>
| |
|
| Yes, Each user has their own FE.
The 4 -6 six month period came about because of the problems when compacting
on a weekly basis.
I have tried re-creating the BE, but still get the same problem. I usually
recreate the BE each time I need to repair it (depending on time available).
I haven't tried to rebuild/compact the FE at the same time. I'll attempt
that next time.
Thanks
"Nikos Yannacopoulos" wrote:
> Nik,
>
> To begin with, a 4-6 month period between compactions sounds waaaaay too
> long! I would have thought of weekly as the absolute minimum (I do nightly).
> That said, there's gotta be a reason for that behaviour, and I suspect
> that is rubbish left-overs in either the FE / BE; the best way to clean
> them up is to produce a fresh, clean copy, by importing everything into
> a new, blank database (no, compacting and repairing won't clean those
> off). I'd do that on both ends, and re-distribute the new, clean FE to
> the users. I suppose you have given each user their own separate copy of
> the FE, haven't you? If not, this is also a possible reason for what
> you're facing.
>
> HTH,
> Nikos
>
> Nik wrote:
>
| |
| Nikos Yannacopoulos 2005-04-06, 8:05 pm |
| Any code left in the BE? Did you debug / compile it?
Nik wrote:[color=darkred
]
> Yes, Each user has their own FE.
>
> The 4 -6 six month period came about because of the problems when compacting
> on a weekly basis.
>
> I have tried re-creating the BE, but still get the same problem. I usually
> recreate the BE each time I need to repair it (depending on time available).
>
> I haven't tried to rebuild/compact the FE at the same time. I'll attempt
> that next time.
>
> Thanks
>
> "Nikos Yannacopoulos" wrote:
>
>
| |
|
| The BE contains tables only.
The last 'fix' (Monday), I also did a compact/repair and a re-compile on the
FE.
The system has corrupted again today.
"Nikos Yannacopoulos" wrote:
> Any code left in the BE? Did you debug / compile it?
>
> Nik wrote:
>
| |
| Tony Toews 2005-04-06, 8:05 pm |
| Nik <Nik@discussions.microsoft.com> wrote:
>The BE contains tables only.
>The last 'fix' (Monday), I also did a compact/repair and a re-compile on the
>FE.
>
>The system has corrupted again today.
This is an interesting problem.
One thing to try would be to import all the objects in the BE into a
new MDB. Doing a compact/repair on the MDB leave minor corruption in
the MDB.
Other than that I'm not sure what to suggest given how it corrupts
after you do compact and repair.
For more information on corruption including possible causes,
determining the offending PC, retrieving your data, links, official MS
KB articles and a list of vendors who state they can fix corruption
see the Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.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
| |
|
| Some recomandations that can help :
- First review your network and server (disconnects and hardware problems)
- Keep the BE DB Size < 150 Mb, if not is feasible, split the data in
several mdbs
- Use ADO instead of ODBC
- Keep connection to the DB only as needed, and only one
- Do not use a permanent conn to a dummy table when start the application,
this
increase the performance but increase the risk of damage when the app
crashs
- Use W2000 o WNT to store the BE, never use W98 or ME
- Be sure that you have the last service pack of the Jet Database Engine
This links explains some of this issues (from Microsoft) :
Information :
http://support.microsoft.com/defaul...kb;en-us;306204
Fix damage : http://support.microsoft.com/kb/209137
Avoid damage : http://support.microsoft.com/kb/300216
Choosing Db :
http://support.microsoft.com/defaul...kb;en-us;168549
(Old but helps)
Taken from the last URL (Choosing.doc) : "For example, with proper tuning,
the current version of Microsoft Access can certainly handle 50 concurrent
users and 100 megabytes of data"
Hope that it can helps
JP
"Tony Toews" wrote:
> Nik <Nik@discussions.microsoft.com> wrote:
>
>
> This is an interesting problem.
>
> One thing to try would be to import all the objects in the BE into a
> new MDB. Doing a compact/repair on the MDB leave minor corruption in
> the MDB.
>
> Other than that I'm not sure what to suggest given how it corrupts
> after you do compact and repair.
>
> For more information on corruption including possible causes,
> determining the offending PC, retrieving your data, links, official MS
> KB articles and a list of vendors who state they can fix corruption
> see the Microsoft Access Corruption FAQ at
> http://www.granite.ab.ca/access/corruptmdbs.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 Toews 2005-04-06, 8:05 pm |
| "JP77" <JP77@discussions.microsoft.com> wrote:
> Some recomandations that can help :
You probably want to respond to Nik directly rather than to me.
> - First review your network and server (disconnects and hardware problems)
Pretty generic comment.
> - Keep the BE DB Size < 150 Mb, if not is feasible, split the data in
>several mdbs
Why does the size of the BE DB matter?
> - Use ADO instead of ODBC
How would you use ODBC to a Jet BE? What about DAO?
> - Keep connection to the DB only as needed, and only one
> - Do not use a permanent conn to a dummy table when start the application,
>this
> increase the performance but increase the risk of damage when the app
>crashs
Really? I respectfully disagree. I can't see how a permanent
connection to a table or database would increase the risk of damage.
It's not like you're actually updating anything.
And for better performance this is required.
> - Use W2000 o WNT to store the BE, never use W98 or ME
Agreed.
> - Be sure that you have the last service pack of the Jet Database Engine
Agreed.
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
| |
| david epsom dot com dot au 2005-04-06, 8:05 pm |
| > The last 'fix' (Monday), I also did a compact/repair and a re- compile
> on the FE.
This surprises me. Did you do a re-link as well as the
compact and re-compile?
Compacting the BE can lead to 'file corruption' if you
have made changes to the BE design without updating the
FE: I normally just compact the FE: many people re-link,
if you are having problems, you should do both. This is
a problem associated with design changes to the BE, so
theoretically it could also happen after a repair of the
BE, but only if the BE already had undetected damage.
(david)
"Nik" <Nik@discussions.microsoft.com> wrote in message
news:0D31ADD4-B914-4782-AF1E- D445FC9806FF@microso
ft.com...[color=darkred]
> The BE contains tables only.
> The last 'fix' (Monday), I also did a compact/repair and a re-compile on
> the
> FE.
>
> The system has corrupted again today.
>
> "Nikos Yannacopoulos" wrote:
>
| |
|
| Nikos, Tony & David
Thanks for the input.
To summarize :-
After a compact of the back end (and compact/recompile & re-link of the
front end), I get corruption on a daily basis for about 7 to 10 days after.
When fixing the corruption, I copy the back end locally and rename the copy
on the server (paranoia on by part).
I repair the back end.
I create a blank database and import all the tables from the baclk end
(there are only tables in the back end).
I compact the new database, rename to as the back end and put in onto the
server.
I sometimes (but not always, depending on time constraints) repair/compact
the front end and re-link to the back end.
The last compact was done late on Feb 24th. we have now been error free for
3 days, but each working day from Feb 25th to Mar 8th I have had to repair
the back end.
If all goes accordingly, we should remain error free until the next compact
(in about 4 - 6 months time)
Again thanks to all.
"david epsom dot com dot au" wrote:
>
> This surprises me. Did you do a re-link as well as the
> compact and re-compile?
>
> Compacting the BE can lead to 'file corruption' if you
> have made changes to the BE design without updating the
> FE: I normally just compact the FE: many people re-link,
> if you are having problems, you should do both. This is
> a problem associated with design changes to the BE, so
> theoretically it could also happen after a repair of the
> BE, but only if the BE already had undetected damage.
>
> (david)
>
>
>
> "Nik" <Nik@discussions.microsoft.com> wrote in message
> news:0D31ADD4-B914-4782-AF1E- D445FC9806FF@microso
ft.com...
>
>
>
| |
| Tony Toews 2005-04-06, 8:06 pm |
| Nik <Nik@discussions.microsoft.com> wrote:
>To summarize :-
>After a compact of the back end (and compact/recompile & re-link of the
>front end), I get corruption on a daily basis for about 7 to 10 days after.
This is still very weird. And makes no sense at all. Not that I
don't believe you because I do. Your postings are quite detailed and
very credible.
It is possible that mismatched Jet versions on different systems are
causing this problem. That is Jet 4.0 SP8 may be on all systems but
one which is still running SP7.
What I've done is use the various API calls available and am checking
the version number and date/time of a crucial dll, msjetxx.dll, to
ensure it matches what I have on my system. See the Verify
Appropriate Jet Service Pack is installed page at my website for more
details including sample code:
www.granite.ab. ca\access\verifyjets
p.htm
But what is really puzzling is that compact and repair does a compact
as part of the repair process. Thus if it is the compact that is
causing the problem you should continue to be getting the problem.
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
| |
|
| One of my colleagues suggested that the problem may be an indexing issue. A
user hits an invalid index and corrupts the back end. The repair/compact then
fixes the 'broken' index. I am not sure how likely this is because if the
first compact/repair causes problems then surely the other compact/repairs
will do the same thing.
"Tony Toews" wrote:
> Nik <Nik@discussions.microsoft.com> wrote:
>
>
> This is still very weird. And makes no sense at all. Not that I
> don't believe you because I do. Your postings are quite detailed and
> very credible.
>
> It is possible that mismatched Jet versions on different systems are
> causing this problem. That is Jet 4.0 SP8 may be on all systems but
> one which is still running SP7.
>
> What I've done is use the various API calls available and am checking
> the version number and date/time of a crucial dll, msjetxx.dll, to
> ensure it matches what I have on my system. See the Verify
> Appropriate Jet Service Pack is installed page at my website for more
> details including sample code:
> www.granite.ab. ca\access\verifyjets
p.htm
>
> But what is really puzzling is that compact and repair does a compact
> as part of the repair process. Thus if it is the compact that is
> causing the problem you should continue to be getting the problem.
>
> 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 Toews 2005-04-06, 8:06 pm |
| Nik <Nik@discussions.microsoft.com> wrote:
>One of my colleagues suggested that the problem may be an indexing issue. A
>user hits an invalid index and corrupts the back end. The repair/compact then
>fixes the 'broken' index. I am not sure how likely this is because if the
>first compact/repair causes problems then surely the other compact/repairs
>will do the same thing.
Yes, indexes can get corrupt. That's the cause of the "Search key
not found" (or similar) message.
But it still doesn't make any sense why corruptions would occur for a
few days or a week and then stop for several months until the next
time you do a compact & repair.
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
|
|
|
|
|