Final Project Questions

[Christoph]

Hi,

with the help of this group, I was able to create a fairly
sophisticated database for a group of users that is not very computer
savy. I have a few final point/issued I would like to implement or get
clarified before I deploy this project:

1. When viewing data in a form, what's the easiest way to delete a
record currently showing? I noticed in the form property/data section,
there is a "allow deletions" setting but I don't see anything in the
navigation bar on the bottom that would allow for the user to delete a
record.

2. What do I need to pay attention to when deploying this database to
about 5 employees. What happens when one user opens a record and
another one wants to update the same record. I noticed record locks in
the properties of a form but haven't played with them yet. Does Access
apply record locks automatically?

Any help is - as always - greatly appreciated.

Regards,
Christoph

 

[Albert D. Kallal]

1. When viewing data in a form, what's the easiest way to delete a
record currently showing?

There's several ways to delete this record, a comman way is to click on the
record selector on the left side of the form, and hit the delete key. You
can check this "record select" setting on the forms design mode, and check
the setting called:

Record Selectors (set to yes).

If you've turned off the record selector, then the next way is the user can
go

edit->delete reocrd

I noticed in the form property/data section,
there is a "allow deletions" setting but I don't see anything in the
navigation bar on the bottom that would allow for the user to delete a
record.

As mentioned, a common way to delete records is to click on the on the
reocrd selector and hit delete.

2. What do I need to pay attention to when deploying this database to
about 5 employees. What happens when one user opens a record and
another one wants to update the same record. I noticed record locks in
the properties of a form but haven't played with them yet. Does Access
apply record locks automatically?

Yes, for the most part the record locking is automatic, and there's two
types of record locking.

Pessimistic record locking a means that both users can start editing the
record at the same time, but the last person who exits will get an error
message to about the fact that the form data's been changed, and if they
answer yes, then their changes will "overwite" the prevous user. (so, last
man out wins).

Optimistic record locking means that the instant one user starts editing a
record, you'll see a little Ghostbusters "circle" with a "/" through it in
the record selector. Assuming as per instructions and in the first part of
my answer you have a record selectors enabled. Note you also see a little
pencil if the record is currently dirty (been edited) with the record
selector.

So record locking is automatic, and you don't really have to do anything.

However if you'll need to read the following article as to how and why you
deploy software in a multiuser environment, and is simply is you split your
database into two parts.

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Ray C]

Hi,

Access does provide shortcut keys to delete the current record, but that's
not the proper way to delete records. What I normally do is add a Delete
button on the form and write the code to properly delete the record. There
may be cases where related records on another table will prevent you from
deleting the current record. For example, you wouldn't want to delete a
customer if the customer has orders in the database. In that case I write
code that asks the user if she wants to delete the orders as well.

As for your second point, Access does not lock records when two users open
the same record. However, Access simplifies things by managing who finishes
last. For example, if the first user modifies the record and saves it while
the second user modifies the same record, then Access will pop up a message
on the second user telling her that the record was modified.

Hope this helps

Ray

 

[Ken Sheridan]

Christoph:

1. The standard forms toolbar includes a delete button. There is also a
Delete Record item on the Edit menu. If you are using custom menu and /or
toolbars you can include these in them. You could also include a 'Delete
record' button on the form with the following code in its Click event
procedure:

Const COMMANDUNAVAILABLE = 2046
Const DELETECANCELLED = 2501
Const MESSAGE = "The Delete command is unavailable."

On Error Resume Next
RunCommand acCmdDeleteRecord
Select Case Err.Number
Case 0
' no error
Case DELETECANCELLED
' anticipated error
Case COMMANDUNAVAILABLE
MsgBox MESSAGE, vbExclamation, "Invalid Operation"
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select

The error handling is to cater for the user cancelling the deletion in the
confirmation dialogue which pops up before a record is deleted, and for a
user attempting to delete an unsaved new record (which should be undone not
deleted).

2. The default locking strategy is 'optimistic locking', i.e. no locks.
This enables two or more users to edit a record concurrently. When one user
saves the edited record the other user(s) will be informed of this and given
the opportunity to substitute their edit for the first users or to abandon
their edit. The other locking strategy is 'pessimistic locking', which is
now the generally recommended one. To implement this set the form's
RecordLocks property to 'Edited Record'. This locks a record as soon as a
user begins to edit it so other users are prevented from doing so. The
record selector icon changes to Ø, so its essential to show the record
selectors in the form for users to know why they are prevented from editing a
record.

Ken Sheridan
Stafford, England

 

[Klatuu]

My preferred way to delete the current record in a form is to use SQL.
Put something like this in the Click Event of a command Button:

Dim strSQL As String

If MsgBox ("Delete This Record", vbQuestion + vbYesNo) = vbYes Then
strSQL = "DELETE * FROM TheTable WHERE PrimaryKeyField = " & _
Me.txtPrimaryKeyControl
Currentdb.Execute strSQL, dbFailOnError
Me.Requery
End If

Don't worry about the locking at the form level. Just be sure you set
Default Open Mode to Shared and Default Record Locking to No Locks in Tools,
Options, Advanced tab. With only 5 users, the probability you will have this
issue is too minimal to worry about. If there are conflicts, Access will
throw errors before it damages the data and you should be fine.

Now, I hope you have split your database and that each user has their own
copy of the front end on their own computer. This is the correct
configuration.

 

[Ken Sheridan]

Christoph:

I should perhaps have made it clear that with optimistic locking, a user is
only informed of another user's edit when they attempt to save their edit,
not when the first user saves their changes. This can lead to a lot of
wasted effort on the part of user two, which is one reason why pessimistic
locking is generally preferred these days. Optimistic locking used to be the
preferred strategy, but only because in early versions of Access it was the
'page' which was locked not the record per se. This could mean that other
records on the page as well as the current one would be locked unnecessarily.
This is no longer the case, so by and large pessimistic locking (Edited
Record) now makes a lot more sense.

Ken Sheridan
Stafford, England

 

[Christoph]

Oh boy, I guess then all my work is somewhat wasted because I put
everything, the UI, queries, and tables into one file. I'm not giving
up yet though. I came so far developing this database in over 3 months
worth of time. I hope nobody gets annoyed by me asking further
questions, but how do I separate the front end from the UI (forms).
And where do the queries sit?

Thanks much for all your help, guys!
Christoph

 

[Douglas J. Steele]

Um, the front end IS the UI. It should contain the queries, forms, reports,
macros and modules. The back end should contain the tables and relations.
Only the back end should be on the server: each user should have his/her own
copy of the front end, ideally on his/her hard drive.

For details on splitting, see what Albert Kallal has at
http://members.shaw.ca/AlbertKallal/Articles/split/index.htm and/or what
Tony Toews has at http://www.granite.ab.ca/access/splitapp/index.htm

 

[Ken Sheridan]

No, your work is far from wasted. All you have to do is split the database,
for which access includes the Database Splitter wizard, and about which you
can find useful information at the links Doug gave you.

There are one or two things which don't work with linked tables in the same
way as with local tables, but its probably unlikely you'll need to make any
changes to the front end after you've split the file.

Splitting the database into front and back ends also makes maintaining the
front end much easier as you can do your development work on your own copy
and then replace the other users' front ends with the amend version without
having to worry about the data. Also you can easily back up just the data
regularly as its just the back end file which needs backing up for this, so
it can even be done from within the front end.

Ken Sheridan
Stafford, England

 

[David W. Fenton]

Pessimistic record locking a means that both users can start
editing the record at the same time, but the last person who exits
will get an error message to about the fact that the form data's
been changed, and if they answer yes, then their changes will
"overwite" the prevous user. (so, last man out wins).

Optimistic record locking means that the instant one user starts
editing a record, you'll see a little Ghostbusters "circle" with a
"/" through it in the record selector. Assuming as per
instructions and in the first part of my answer you have a record
selectors enabled. Note you also see a little pencil if the record
is currently dirty (been edited) with the record selector.

Er, I think you have the definitions of optimistic and pessimistic
record locking reversed.

When you are using pessimistic locking, a user cannot edit the
record if that same record is already being edited by another user.
With optimistic locking, both users get to edit, with the optimistic
assumption that one will save before the other, so there won't be a
collision, though, as you say, the edits of whoever saves last will
win.