DAO and database corruption

[Mike]

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!

[Allen Browne]

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]

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!

[Allen Browne]

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?

[Tony Toews]

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

[Brendan Reynolds]

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!

[David W. Fenton]

"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