Question re: VBA code for a split database

[Guest]

MS Access 2K, Windows XP
====================
Hi,

I have a multi-user database application which is a single MDB file on a
server (yes, I know not a good idea), and I am planning to split it into
multiple FEs and one BE.

I have a lot of VBA code in the forms, some of which runs SQL statements to
edit tables, or get data from them (e.g. populating a list on a form,
deleting and adding records).

Do I need to modify the code in any way to ensure that the split database
will function error-free? What part of code?

Will appreciate any help regarding this, and if you need some specifics (re:
code), please ask me.

Thanks.

-Amit

 

[Guest]

No changes are necessary. There are a few things to watch for:
1. Before you distribute your FE to other users, be sure to use network
addressing rather than drive letter addressing:

Not
F:\MyFolder\MyAccess.mdb
But
\\MyServerName\MyFolder\MyAccess.mdb

You should also put some error handling in whatever form or code you run on
startup to be sure the links to the BE are still good. You can either
display an error message, or cause the links to refresh.

Add Compact on Close to both the FE and BE. The BE compact will only
execute when the last user closes the FE.

Be sure you know what you are doing with record locking.

Good Luck.

 

[Guest]

No changes are necessary. There are a few things to watch for:

Thanks for your response, and I will also check out the Database Splitting
FAQ by Tony.

Be sure you know what you are doing with record locking.

It'll be helpful if you can elaborate on this. I currently have "Open DB
with Record Level locking" checked, and the Default Record Locking is "Edited
Records". Does that need to change?

Thanks.

-Amit

 

[Guest]

I would suggest No Locks. Edited Records means if a user has a record as the
current record, it is locked. No Locks means only lock it for the update.

 

[Guest]

I would suggest No Locks. Edited Records means if a user has a record as the
current record, it is locked. No Locks means only lock it for the update.

Maybe I don't understand this enough, but from what I read (MS Access Help),
"No Lock" means 2 users can edit the same record simultaneously, whereas
"Edited Lock" means only one user can edit the record.

Quoting from MS Access Help:
"In a multiuser database, you can use the No Locks setting if you want to
use optimistic locking and warn users attempting to edit the same record on a
form. You can use the Edited Record setting if you want to prevent two or
more users editing data at the same time."

So, what are the disadvantages of "Edited Lock" over "No Lock"? I'd think
that "Edited Lock" is preferable if I don't want two users to edit the same
record at the same time.

Could you please elaborate on why you recommended "No Lock"?

Thanks.

-Amit

 

[Guest]

You are correct. The Lock only takes place during the update. If the first
user updates the record, the second user will over write whatever the first
user did when he updates the same record. That has do be handled. The
problem with Edited Locks is that the record is locked for the entire time
the user has it as his current record. If the user goes to lunch and leaves
a form open with a record displayed, then no other user can access that
record. I see this as a bigger problem and a more likely event than two
users updating over each other. You can find some good information on
handling concurrency from many sources. The theory is when you are ready to
update, then you read the original record to see if any changes were made
since you read it the first time. If the data has changed, then you have to
decide how to handle the problem, otherwise you just update.

There are multiple approaches to a concurrency conflict. One is to alert
the user the record has changed, and let him make the decision on what to do.
Or, you can examine the record field by field to see what changes were made.
Say if user 1 changed a phone number, and user 2 changed last payment date,
then code could update only user 2's changes. If they both changed the same
field, then warn. Anyway, I think you understand the issue.