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 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!
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
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...
|
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...
|
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...
|
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 :...
|
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...
|
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...
|
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...
|
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...
| |