I have done a lot of research on DAO and database corruption and have
not yet found any solid information for my problem. I use DAO
extensively in my Access 97 databases (back-end on network, front-ends
on clients' computers). I'm having some difficulties with the
multi-user environment (my databases corrupt frequently) and I'm
thinking it has to do with how I'm using DAO to read/write to
recordsets.
Up until now, I have been setting recordsets without specifying the
type (dbOpenDynaset, etc). Is this asking for multi-user issues
leading to database corruption?
What ends up happening is the user comes to me with an error caused by
the back-end becoming suddenly marked corrupt, and I have to create a
new back-end because I can't delete the old back-end (even with
everyone logged out, Access won't allow me to repair or compact the
file, saying that another user has the file open).
I'm extremely frustrated at this point. Any help or ideas is greatly
appreciated! 6 3220
DAO as a technology does not cause the corruption. Concurreny issues and
failure to correctly de-reference your object variables might contribute.
Specifying a recordset type will not help prevent corruption. You get a
dbOpenDynaset type by default if you open a recordset on an attached table,
query, or SQL string; you get dbOpenTable type on a local table. If you
split late in your development cycle, then specifying dbOpenDynaset means
that your early testing will be on the same type as the final app even
though the mdb is unsplit, but it will not make any difference to
corruption.
If Access won't let you delete the ldb after all users have logged out,
restarting the machine should kill any spurious connections and allow you to
delete the ldb and then repair the mdb.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mike" <ms*****@hotmail.com> wrote in message
news:1f**************************@posting.google.c om... I have done a lot of research on DAO and database corruption and have not yet found any solid information for my problem. I use DAO extensively in my Access 97 databases (back-end on network, front-ends on clients' computers). I'm having some difficulties with the multi-user environment (my databases corrupt frequently) and I'm thinking it has to do with how I'm using DAO to read/write to recordsets.
Up until now, I have been setting recordsets without specifying the type (dbOpenDynaset, etc). Is this asking for multi-user issues leading to database corruption?
What ends up happening is the user comes to me with an error caused by the back-end becoming suddenly marked corrupt, and I have to create a new back-end because I can't delete the old back-end (even with everyone logged out, Access won't allow me to repair or compact the file, saying that another user has the file open).
I'm extremely frustrated at this point. Any help or ideas is greatly appreciated!
Ok, if specifying the recordset type won't affect corruption issues,
how about setting the LockEdit property to Optimistic instead of
Pessimistic. My users never even see each other's records so
concurrent updating for the same record is impossible. But, with
Pessimistic locking, if the record is in the process of being edited,
the 2K page would be locked, so if someone tried to add or modify a
record prior to the first user's update, that would cause problems.
Am I on the right track here?
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>... DAO as a technology does not cause the corruption. Concurreny issues and failure to correctly de-reference your object variables might contribute.
Specifying a recordset type will not help prevent corruption. You get a dbOpenDynaset type by default if you open a recordset on an attached table, query, or SQL string; you get dbOpenTable type on a local table. If you split late in your development cycle, then specifying dbOpenDynaset means that your early testing will be on the same type as the final app even though the mdb is unsplit, but it will not make any difference to corruption.
If Access won't let you delete the ldb after all users have logged out, restarting the machine should kill any spurious connections and allow you to delete the ldb and then repair the mdb.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.
"Mike" <ms*****@hotmail.com> wrote in message news:1f**************************@posting.google.c om... I have done a lot of research on DAO and database corruption and have not yet found any solid information for my problem. I use DAO extensively in my Access 97 databases (back-end on network, front-ends on clients' computers). I'm having some difficulties with the multi-user environment (my databases corrupt frequently) and I'm thinking it has to do with how I'm using DAO to read/write to recordsets.
Up until now, I have been setting recordsets without specifying the type (dbOpenDynaset, etc). Is this asking for multi-user issues leading to database corruption?
What ends up happening is the user comes to me with an error caused by the back-end becoming suddenly marked corrupt, and I have to create a new back-end because I can't delete the old back-end (even with everyone logged out, Access won't allow me to repair or compact the file, saying that another user has the file open).
I'm extremely frustrated at this point. Any help or ideas is greatly appreciated!
Mike, when I first started developing with Access (version 1, 1992), I used
pessimistic locking because the mindset fitted with my previous database
experience. We began experimenting with optimistic locking, and quickly
realised that it was definitely the go. We now use optimistic in every
Access application we write.
We always include an explanation of the Write Conflict dialog in the manual
and training we give our users, but - as you say - most users never see this
dialog.
Whether optimistic locking could reduce corruption in practice, I cannot say
because we never use pessismistic. I guess it could theoretically have an
effiect since locks are on only very briefly and conflicts are far less
frequent.
What I know about corruption is shared in this article (updated yesterday): http://allenbrowne,com/ser-25.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mike" <ms*****@hotmail.com> wrote in message
news:1f**************************@posting.google.c om... Ok, if specifying the recordset type won't affect corruption issues, how about setting the LockEdit property to Optimistic instead of Pessimistic. My users never even see each other's records so concurrent updating for the same record is impossible. But, with Pessimistic locking, if the record is in the process of being edited, the 2K page would be locked, so if someone tried to add or modify a record prior to the first user's update, that would cause problems. Am I on the right track here? ms*****@hotmail.com (Mike) wrote: I have done a lot of research on DAO and database corruption and have not yet found any solid information for my problem. I use DAO extensively in my Access 97 databases (back-end on network, front-ends on clients' computers). I'm having some difficulties with the multi-user environment (my databases corrupt frequently) and I'm thinking it has to do with how I'm using DAO to read/write to recordsets.
Up until now, I have been setting recordsets without specifying the type (dbOpenDynaset, etc). Is this asking for multi-user issues leading to database corruption?
I'd agree with Allen. It's very doubtful that DAO, so long as you are closing the
recordsets, or locking is causing your problems.
In addition to Allen's page see 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
No, Mike, I don't believe you're on the right track at all. It is unlikely
that your code has anything to do with the problem, and therefore unlikely
that any changes to your code will achieve any improvement at all. From your
description, the two most likely causes of the problem are a) a flaky
network adapter or b) a user or users switching off their PCs without
shutting down the application.
--
Brendan Reynolds
"Mike" <ms*****@hotmail.com> wrote in message
news:1f**************************@posting.google.c om... Ok, if specifying the recordset type won't affect corruption issues, how about setting the LockEdit property to Optimistic instead of Pessimistic. My users never even see each other's records so concurrent updating for the same record is impossible. But, with Pessimistic locking, if the record is in the process of being edited, the 2K page would be locked, so if someone tried to add or modify a record prior to the first user's update, that would cause problems. Am I on the right track here?
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<40***********************@freenews.iinet.net .au>... DAO as a technology does not cause the corruption. Concurreny issues and failure to correctly de-reference your object variables might
contribute. Specifying a recordset type will not help prevent corruption. You get a dbOpenDynaset type by default if you open a recordset on an attached
table, query, or SQL string; you get dbOpenTable type on a local table. If you split late in your development cycle, then specifying dbOpenDynaset
means that your early testing will be on the same type as the final app even though the mdb is unsplit, but it will not make any difference to corruption.
If Access won't let you delete the ldb after all users have logged out, restarting the machine should kill any spurious connections and allow
you to delete the ldb and then repair the mdb.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.
"Mike" <ms*****@hotmail.com> wrote in message news:1f**************************@posting.google.c om... I have done a lot of research on DAO and database corruption and have not yet found any solid information for my problem. I use DAO extensively in my Access 97 databases (back-end on network, front-ends on clients' computers). I'm having some difficulties with the multi-user environment (my databases corrupt frequently) and I'm thinking it has to do with how I'm using DAO to read/write to recordsets.
Up until now, I have been setting recordsets without specifying the type (dbOpenDynaset, etc). Is this asking for multi-user issues leading to database corruption?
What ends up happening is the user comes to me with an error caused by the back-end becoming suddenly marked corrupt, and I have to create a new back-end because I can't delete the old back-end (even with everyone logged out, Access won't allow me to repair or compact the file, saying that another user has the file open).
I'm extremely frustrated at this point. Any help or ideas is greatly appreciated!
"Brendan Reynolds" <br******@removethisindigo.ie> wrote in
news:L9*****************@news.indigo.ie: From your description, the two most likely causes of the problem are a) a flaky network adapter or b) a user or users switching off their PCs without shutting down the application.
I would say either of these things are less likely than a
software-based problem, either on the file serve where the data is
stored or on the workstations, where it could be a substandard
version of Jet or of Access itself.
I have never had either of Brendan's A) or B) cause corruption in
all my years of Access support. I've very, very often had
software-related corruption, and far more of it post-Access2K than
before it (something like 90% of it is is A2K).
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc This discussion thread is closed Replies have been disabled for this discussion. Similar topics
19 posts
views
Thread by adirat |
last post: by
|
47 posts
views
Thread by ship |
last post: by
|
1 post
views
Thread by Jack Orenstein |
last post: by
|
1 post
views
Thread by Another_Access_Dummy |
last post: by
|
2 posts
views
Thread by Vincent |
last post: by
|
2 posts
views
Thread by Barry Edmund Wright |
last post: by
|
13 posts
views
Thread by Vincent |
last post: by
| | | | | | | | | | | | |