database corruption

[Guest]

This maybe a repost.

How can I get my frontend to stop crashing? I have a server based frontend
that has multiple users inputing data into various fields at one time. I
Have locking code in place to limit users in the data entry areas.

The frontend corrupts from time to time and would like suggestions in ways
to handle this problem.

Thanks

 

[Arvin Meyer [MVP]]

Place a copy of the front-end on each workstation. Never share a front-end
file on the server. To do so is just begging for problems.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

How then, by placing a copy of the frontend on every PC, will users that
input data not write on top of one another? The reason that the frontend is
on the server is that there is code that only allows 1 user at a time to
enter into the data entry areas.

 

[Larry Daugherty]

Don't know what other advice you've received as I see only your posts.

Access is inherently multi-user. You can lock optimistically or
pessimistically at the record level. Throughput is significantly
better in a multi-user configuration. Optimistic locking can lead to
occasional warnings that "another user has altered the record, whose
data do you want to save". That implies that the users are editing
exactly the same record. The only way that the first user's entry
will be overwritten is if the person receiving the warning says "Take
my data, over write the other guy" In other words, over writing is
intentional.

Yes, for best results there should be a copy of the front end on every
user's machine. Look at the FrontEnd Updater on Tony Toews' site for
help in easing the maintenance pain. If you insist that the front end
also be on the server then each user should have their own copy there.
by some means such as giving each user near exclusive access to their
folder on the server.

The above will alleviate a lot of the database corruption. The other
things you might do is backup frequently and compact frequently.
Frequently being as often as you can.

HTH

 

[Arvin Meyer [MVP]]

How then, by placing a copy of the frontend on every PC, will users that
input data not write on top of one another? The reason that the frontend
is
on the server is that there is code that only allows 1 user at a time to
enter into the data entry areas.

Access, and many other databases, use page locking to ensure that only a
single person can edit any given record. A page of records in Access is 2K,
so it can handle from 1 to as many records as will fit. Under certain
circumstances, you can lock a single row of data, but the the back-end must
support that. Additionally, when doing large updates, you can lock an entire
table. Rest assured that 2 people cannot simply edit the same record at the
same time. You will get a notice when you try to save, if that situation
occurs. Whomever wrote that code did not understand how the database engine
works or he/she would have used that time more productively.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

"Whomever wrote that code did not understand how the database engine
works or he/she would have used that time more productively."
Arvin Meyer, MCP, MVP

I wrote the database....and I am as noob as it gets in access so thanks for
the comment.

The database is for a company that literally 100's of people can or will be
on it at a given time. Most are just looking at data and for that reason I
am creating some forms that will be read only. So, for the folks that are
just looking to retrieve data I have a fix. My problem again is
understanding how access will treat people that are editting records. I have
a [Text1] on a given form. Anyone that goes in to edit this field will begin
to enter data, but will be unaware of anyone else doing the same thing (this
is a PC based frontend). I don't want anyone to have the ability to select
whether or not they want to keep their data over someone elses. As both
would be trying to enter data at the same time in the same field. With a
server based frontend I can code for only 1 user on that form at a time
allowing only 1 user the ability to input data. When they are finished and
exit out of that form....the next user can get into that form and add their
data.

So again the question remains....what is better in my case....PC based or
server based frontend?

As I understand it.....if 2 users have a PC based frontend and both
login....they see code independantly. This means that I cannot code lock
anyone. Am I correct?

 

[Larry Daugherty]

PC based is the right answer.

Your assumption about locking and conflicts is wrong. When users are
using the application they shouldn't be looking at the code but at the
*data*. If you don't ever want someone to have the choice about
trashing someone else's entry then use pessimistic locking. In that
case, the second and subsequent users trying to edit the same *record*
will be notified that s/he can't because it is being edited by another
user. That will be true no matter how many users are in the
application at the same time but it only occurs when people are trying
to edit the same *record*.

In fairness to you, be aware that while the Front End on each user's
PC is the correct solution, there are some possible negative
consequences once you reconfigure. While Front End problems should
all go away, you may notice more frequent Back End problems until
things stabilize. If any users are in the habit of powering off their
system with applications (Access) open it will eventually cause
problems. If there are any flaky NICs on any of the user PCs or even
the server then there can be data problems.

The best solution to data at risk is to Compact & Repair frequently
and to backup, backup, backup. Notice that the Compact on close and
Backup on close features only apply to the Front Ends. For the Back
End you'll have to take separate and explicit action. As you are
probably aware, to do those things you'll need exclusive access to the
Back End. The backups performed by IT are not sufficient to all of
your needs.

Don't despair. As you solve problems at one level you'll always see
some new ones at the next level. Your users will be the grateful
beneficiaries of the change (after the pains that might arise in
getting there). The crashes will be less and less frequent. If users
are deliberately crashing their systems in spite of good advice and
warnings it is possible to track who was in the database at the time
of the crash. Accumulate enough of those instances and you should be
able to isolate the culprits. Just make it known that you can ... If
flaky NICs are in the mix they too can be isolated and swapped. Also,
the throughput of the database will be significantly greater than it
is now.

Post again with more questions or to let us know what course of action
you've decided.

HTH
--
-Larry-
--

"Whomever wrote that code did not understand how the database engine
works or he/she would have used that time more productively."
Arvin Meyer, MCP, MVP

I wrote the database....and I am as noob as it gets in access so thanks for
the comment.

The database is for a company that literally 100's of people can or will be
on it at a given time. Most are just looking at data and for that reason I
am creating some forms that will be read only. So, for the folks that are
just looking to retrieve data I have a fix. My problem again is
understanding how access will treat people that are editting records. I have
a [Text1] on a given form. Anyone that goes in to edit this field will begin
to enter data, but will be unaware of anyone else doing the same thing (this
is a PC based frontend). I don't want anyone to have the ability to select
whether or not they want to keep their data over someone elses. As both
would be trying to enter data at the same time in the same field. With a
server based frontend I can code for only 1 user on that form at a time
allowing only 1 user the ability to input data. When they are finished and
exit out of that form....the next user can get into that form and add their
data.

So again the question remains....what is better in my case....PC based or
server based frontend?

As I understand it.....if 2 users have a PC based frontend and both
login....they see code independantly. This means that I cannot code lock
anyone. Am I correct?

 

[Guest]

Larry,

Thank you for that great information. As sound as the advice seems to be
and while almost all other posts point to a PC based Frontend....I must
admmit that before I implement that change I will have to make a mockup and
run some testing. However, I am 99.9% sure that when I am complete with the
v6 of the database format that it will be PC based.

Would someone be able to give me just an idea of what compliling the
database really does?

 

[Guest]

Also, just for my own head ... what do you mean by

Pessimistic locking??

 

[Arvin Meyer [MVP]]

Perhaps this from an earlier version of the Access help file will help:

About choosing a record-locking strategy in a multiuser environment

When you edit a record, Microsoft Access can automatically prevent other
users from changing the record before you have finished editing it.
Giving one user exclusive access to a record is called locking. There are
three locking strategies to choose from:

· No Locks - Microsoft Access does not lock the record you're editing. When
you try to save changes to a record that another person has also changed,
Microsoft Access displays a message giving you the options of overwriting
the other user's changes to the record, copying your version of the record
to the Clipboard, or discarding your changes. This strategy ensures that
records can always be edited, but it can create editing conflicts between
users.
· Edited Records - Microsoft Access locks the record you're editing, so no
other user can change it. It might also lock other records that are stored
nearby on your disk. If another user tries to edit a record that you've
locked, Microsoft Access displays the locked record indicator in the
other user's datasheet. This strategy ensures that you can always finish
making changes that you start. It is a good choice if you don't have editing
conflicts often.

· All Records - Microsoft Access locks all records in the form or datasheet
(and underlying tables) you're editing for the entire time you have it open,
so no one else can edit or lock the records. This strategy is very
restrictive, so choose it only when you know you're the only person who
needs to edit records at any one time.

For information on specifying one of these options, click .

Note When you edit data in a linked SQL database table using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the record-locking
setting you choose for your database, Microsoft Access always acts as though
the No Locks setting has been selected.

Another comment: Be careful. Access allows 255 users to be connected (but
not editing). The practical limits are MUCH LOWER. Many people suggest
around 20. I've had success with well-designed databases with over 50
people.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Whomever wrote that code did not understand how the database engine
works or he/she would have used that time more productively."
Arvin Meyer, MCP, MVP

I wrote the database....and I am as noob as it gets in access so thanks
for
the comment.

The database is for a company that literally 100's of people can or will
be
on it at a given time. Most are just looking at data and for that reason
I
am creating some forms that will be read only. So, for the folks that are
just looking to retrieve data I have a fix. My problem again is
understanding how access will treat people that are editting records. I
have
a [Text1] on a given form. Anyone that goes in to edit this field will
begin
to enter data, but will be unaware of anyone else doing the same thing
(this
is a PC based frontend). I don't want anyone to have the ability to
select
whether or not they want to keep their data over someone elses. As both
would be trying to enter data at the same time in the same field. With a
server based frontend I can code for only 1 user on that form at a time
allowing only 1 user the ability to input data. When they are finished
and
exit out of that form....the next user can get into that form and add
their
data.

So again the question remains....what is better in my case....PC based or
server based frontend?

As I understand it.....if 2 users have a PC based frontend and both
login....they see code independantly. This means that I cannot code lock
anyone. Am I correct?

 

[Larry Daugherty]

Pessimistic locking locks a record as soon as the first user lands on
that record with edits enabled. Subsequent users who land on that
record are warned that they can't edit that record because another
user has it locked.

Optimistic locking takes the view that not everyone who lands on a
record with edits enabled will actually edit that record therefore
lets let them all play and only raise a flag when conflicts actually
arise ....

Compiling your database does a lot of things foremost among them being
that the raw code is replaced with compiled code throughout your
database for execution. That compiled code is more compact and
executes more quickly than uncompiled code. Executing uncompiled code
requires that a lot more reference and other checking be done during
execution. When you make your application available to your users it
should be in the form of an MDE file. Before making the MDE you
should compile your application.

HTH

 

[Guest]

"Pessimistic locking locks a record as soon as the first user lands on
that record with edits enabled. Subsequent users who land on that
record are warned that they can't edit that record because another
user has it locked."

Due to the sensitivity of the data that is collected I must implement a
Pessimistic Locking System. How do I achieve this?
I see that Access defaults to an Optimistic Locking System.

 

[Larry Daugherty]

Tools|Options|Advanced back in Acc 97 it may still be there...

HTH

 

[Guest]

Hi,

I really enjoyed reading all of your comments. I love it when people have
discussions like this especially smart experienced people. I am new to
Access Database. I created a database and I have splitted the database in
the Share Drive. There is a back end file and a front end file. I have all
the user's PC installed the front end and asked people to copy and paste in
their My Document folder in each PC because some of the users are in other
countries.

The question I have is when I need to update or make changes to the database
such as creating another table (creat a date column, or product family column
into my original data table) or changing the form or switchboard should I use
my copy database (the database that I made a copy before splitting) or should
I makes the changing on the front end or back end of my split database? If I
make the changes on my front end or backend split database does that mean I
have to ask all my database user to re-copy all the updated front end
database to their own PC? If I created changes in the split database do I
need to re-split the database again?

I have read a lot of comments on putting a copy of the front end of the
database to each user's pc and it seems to work best because if you don't
have copies on each user's pc, other users will not ble able to query if
another user is in the table database making changes. The query user will
have to wait for the other user working on the tables to get out before he
can use query and I think that is the biggest draw back. However, there is
one benefit that I experience about not giving copies of the front end to
each user is that when you have changes you don't have to makes changes to
copies of each pc user. Which why I am here asking where the the best place
for making changes or update to the already split database.

Please help!

Perhaps this from an earlier version of the Access help file will help:

About choosing a record-locking strategy in a multiuser environment

When you edit a record, Microsoft Access can automatically prevent other
users from changing the record before you have finished editing it.
Giving one user exclusive access to a record is called locking. There are
three locking strategies to choose from:

· No Locks - Microsoft Access does not lock the record you're editing. When
you try to save changes to a record that another person has also changed,
Microsoft Access displays a message giving you the options of overwriting
the other user's changes to the record, copying your version of the record
to the Clipboard, or discarding your changes. This strategy ensures that
records can always be edited, but it can create editing conflicts between
users.
· Edited Records - Microsoft Access locks the record you're editing, so no
other user can change it. It might also lock other records that are stored
nearby on your disk. If another user tries to edit a record that you've
locked, Microsoft Access displays the locked record indicator in the
other user's datasheet. This strategy ensures that you can always finish
making changes that you start. It is a good choice if you don't have editing
conflicts often.

· All Records - Microsoft Access locks all records in the form or datasheet
(and underlying tables) you're editing for the entire time you have it open,
so no one else can edit or lock the records. This strategy is very
restrictive, so choose it only when you know you're the only person who
needs to edit records at any one time.

For information on specifying one of these options, click .

Note When you edit data in a linked SQL database table using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the record-locking
setting you choose for your database, Microsoft Access always acts as though
the No Locks setting has been selected.

Another comment: Be careful. Access allows 255 users to be connected (but
not editing). The practical limits are MUCH LOWER. Many people suggest
around 20. I've had success with well-designed databases with over 50
people.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Whomever wrote that code did not understand how the database engine
works or he/she would have used that time more productively."
Arvin Meyer, MCP, MVP

I wrote the database....and I am as noob as it gets in access so thanks
for
the comment.

The database is for a company that literally 100's of people can or will
be
on it at a given time. Most are just looking at data and for that reason
I
am creating some forms that will be read only. So, for the folks that are
just looking to retrieve data I have a fix. My problem again is
understanding how access will treat people that are editting records. I
have
a [Text1] on a given form. Anyone that goes in to edit this field will
begin
to enter data, but will be unaware of anyone else doing the same thing
(this
is a PC based frontend). I don't want anyone to have the ability to
select
whether or not they want to keep their data over someone elses. As both
would be trying to enter data at the same time in the same field. With a
server based frontend I can code for only 1 user on that form at a time
allowing only 1 user the ability to input data. When they are finished
and
exit out of that form....the next user can get into that form and add
their
data.

So again the question remains....what is better in my case....PC based or
server based frontend?

As I understand it.....if 2 users have a PC based frontend and both
login....they see code independantly. This means that I cannot code lock
anyone. Am I correct?

 

[Larry Daugherty]

For changes that affect only the Front End, see Tony Toews' Front End
Updater for a solution you can adapt to your needs.

For changes to the tables you have to change both Front and Back ends.
One more reason for getting a good handle on your data design before
you deploy your application.

I think Tony also has some information and recommendations about
whether to use an Access Back End when it is remote from the LAN. I
think that Albert Kallal also has relevant info:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

HTH
--
-Larry-
--

"E-mail report using Lotus Notes rather t"

Hi,

I really enjoyed reading all of your comments. I love it when people have
discussions like this especially smart experienced people. I am new to
Access Database. I created a database and I have splitted the database in
the Share Drive. There is a back end file and a front end file. I have all
the user's PC installed the front end and asked people to copy and paste in
their My Document folder in each PC because some of the users are in other
countries.

The question I have is when I need to update or make changes to the database
such as creating another table (creat a date column, or product family column
into my original data table) or changing the form or switchboard should I use
my copy database (the database that I made a copy before splitting) or should
I makes the changing on the front end or back end of my split database? If I
make the changes on my front end or backend split database does that mean I
have to ask all my database user to re-copy all the updated front end
database to their own PC? If I created changes in the split database do I
need to re-split the database again?

I have read a lot of comments on putting a copy of the front end of the
database to each user's pc and it seems to work best because if you don't
have copies on each user's pc, other users will not ble able to query if
another user is in the table database making changes. The query user will
have to wait for the other user working on the tables to get out before he
can use query and I think that is the biggest draw back. However, there is
one benefit that I experience about not giving copies of the front end to
each user is that when you have changes you don't have to makes changes to
copies of each pc user. Which why I am here asking where the the best place
for making changes or update to the already split database.

Please help!

Perhaps this from an earlier version of the Access help file will help:

About choosing a record-locking strategy in a multiuser environment

When you edit a record, Microsoft Access can automatically prevent other
users from changing the record before you have finished editing it.
Giving one user exclusive access to a record is called locking. There are
three locking strategies to choose from:

· No Locks - Microsoft Access does not lock the record you're editing. When
you try to save changes to a record that another person has also changed,
Microsoft Access displays a message giving you the options of overwriting
the other user's changes to the record, copying your version of the record
to the Clipboard, or discarding your changes. This strategy ensures that
records can always be edited, but it can create editing conflicts between
users.
· Edited Records - Microsoft Access locks the record you're editing, so no
other user can change it. It might also lock other records that are stored
nearby on your disk. If another user tries to edit a record that you've
locked, Microsoft Access displays the locked record indicator in the
other user's datasheet. This strategy ensures that you can always finish
making changes that you start. It is a good choice if you don't have editing
conflicts often.

· All Records - Microsoft Access locks all records in the form or datasheet
(and underlying tables) you're editing for the entire time you have it open,
so no one else can edit or lock the records. This strategy is very
restrictive, so choose it only when you know you're the only person who
needs to edit records at any one time.

For information on specifying one of these options, click .

Note When you edit data in a linked SQL database table using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the record-locking
setting you choose for your database, Microsoft Access always acts as though
the No Locks setting has been selected.

Another comment: Be careful. Access allows 255 users to be connected (but
not editing). The practical limits are MUCH LOWER. Many people suggest
around 20. I've had success with well-designed databases with over 50
people.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Whomever wrote that code did not understand how the database engine
works or he/she would have used that time more productively."
Arvin Meyer, MCP, MVP

I wrote the database....and I am as noob as it gets in access so thanks
for
the comment.

The database is for a company that literally 100's of people can or will
be
on it at a given time. Most are just looking at data and for that reason
I
am creating some forms that will be read only. So, for the folks that are
just looking to retrieve data I have a fix. My problem again is
understanding how access will treat people that are editting records. I
have
a [Text1] on a given form. Anyone that goes in to edit this field will
begin
to enter data, but will be unaware of anyone else doing the same thing
(this
is a PC based frontend). I don't want anyone to have the ability to
select
whether or not they want to keep their data over someone elses. As both
would be trying to enter data at the same time in the same field. With a
server based frontend I can code for only 1 user on that form at a time
allowing only 1 user the ability to input data. When they are finished
and
exit out of that form....the next user can get into that form and add
their
data.

So again the question remains....what is better in my case....PC based or
server based frontend?

As I understand it.....if 2 users have a PC based frontend and both
login....they see code independantly. This means that I cannot code lock
anyone. Am I correct?

 

[Arvin Meyer [MVP]]

Tools ... Options ... Advanced

Then in the Default Record Locking section, check the Edited Record option

The choices are:
No locks - optimistic locking
All Records - table is locked
Edited Record - Pessimistic locking

To reitterate, 2 people cannot in any of the above scenarios edit the same
record. Optimistic locking only warns that it is locked if you try to save
it while it is being edited. In either case, after the first person edits
and saves the record. it can be edited again. If you want to keep a record
from ever being edited again, you need to set a flag. (i.e. a Boolean field
that tells the form to not allow that record to be edited ever again)
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com