Unstable dbs

  • Thread starter Thread starter Crystal
  • Start date Start date
C

Crystal

I have a database which, one month ago, used to be quite
stable. I haven't made any changes to it but all of a
sudden it's begun to act flakey. Nearly every time you
shut down the database and attempt to open it again, it
says either 1) the database needs to be repaired or 2) the
database is in an unexpected state.

Neither situation can be corrected. If I run the repair,
it gets stuck in the middle. I usually have to restore it
from the server.

Any suggestions as to what might be wrong?

Crystal
 
Crystal,

It could be any of a number of things, for instance:
(a) writing and deleting lots of temporary objects (tables, queries etc)
(b) a bad network with lots of noise and/or frequent outages
(c) a multi-user database with a common front end shared by all users
(d) a monolithic multi-user database
(e) a combination of several of the above

The list is not necessarily exhaustive, it's just the reasons I can think
of. Mind you, the further down you walk from (a) to (e) the worse it gets!
Post what your scheme is (multi-user? split? common FE?) so you can get more
specific help.

HTH,
Nikos
 
The database is split. Tables are linked to the front end
but are still native Access tables. Users do utilize a
common front end. Also, I do run a lot of delete and
append queries when the database is updated. That happens
about three times per 24 hour period.

Currently, I'm moving the tables to SQL Server and re-
linking them. I'm hoping that this will help stabalize
the database.

Crystal
 
The database is split. Tables are linked to the front end
but are still native Access tables. Users do utilize a
common front end.
Click to expand...

Which MDB gets corrupted? The FE or the BE?

In either case but especially if the FE is getting corrupted the first
thing to do is to give each user their own copy of the FE. See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. It also supports Terminal Server/Citrix quite nicely.

If it's the BE then the first thing to do is to check the OpLocks
setting on the server. And go down the list at Microsoft Access
Corruption FAQ at http://www.granite.ab.ca/access/corruptmdbs.htm. Or
see if the corruption is caused by one workstation.
Also, I do run a lot of delete and
append queries when the database is updated. That happens
about three times per 24 hour period.
Click to expand...

Then you should be doing compacting reasonably often.
Currently, I'm moving the tables to SQL Server and re-
linking them. I'm hoping that this will help stabalize
the database.
Click to expand...

If it's the BE that is corrupting then this will help. If the FE then
probably not. However this can be a lot of work. Also see my Random
Thoughts on SQL Server Upsizing from Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.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
 
Back
Top