472,952 Members | 2,825 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

DAO and database corruption

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!
Nov 12 '05 #1
6 3454
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!

Nov 12 '05 #2
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!

Nov 12 '05 #3
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?

Nov 12 '05 #4
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
Nov 12 '05 #5
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!

Nov 12 '05 #6
"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
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: adirat | last post by:
I have read a lot on this subject on newsgroups and other access related websites on data corruption, but since we are still not able to isolate the problem – I am posting this detailed explanation...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
1
by: Jack Orenstein | last post by:
My company is developing a PostgreSQL 7.4 application. We don't want our customers to have to manage the database, so we're automating as much maintenance as possible. If the database ever becomes...
1
by: Another_Access_Dummy | last post by:
I am getting database corruption more frequently than is ideal. I have a form used in a call center environment with about 5-10 users at any given time. The application is split between front end...
2
by: Vincent | last post by:
The company I work for has developed a Microsoft Access application. Currently, it is distributed with the Microsoft Access 2002 runtime. Some of our customers have begun purchasing new Vista...
2
by: Barry Edmund Wright | last post by:
Hi All, I installed Office 2003 SP3 and ever since been experiencing database corruption. I normally close the database (compact and repair), do a backup and reopen the database and open a form,...
13
by: Vincent | last post by:
The company I work for has seen an inordinate amount of Access database corruption with those users using one or more Vista computers. We always host the database files on an XP machine, so I...
1
matrekz42
by: matrekz42 | last post by:
Good afternoon, I have an MS Access DB, that I was working on and all of the sudden it just stopped working, I cannot compact and repair, I cannot import, I cannot use Modules, I cannot use the...
2
by: munkee | last post by:
All, Had my first database corruption today which produced the following error when entering the database from the front end. Unrecognized database format 'k:etc... path' I was in the...
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.