|
|
Hello Everyone
Please accept my apologies if I've posted to the wrong section - just
move me along if necessary.
I'm helping someone to improve a small, unsecured Access 2003 database
for his company. For years they have used just a single table, and the
consistency of data entry in the table was sad to see. The new db will
use only four tables with non-cascading enforced referential
relationship, queries, forms (with appropriate data validation) and
reports.
I'm more familiar with Access97 and have some questions - I can't find
a definitive* answer anywhere<s>.
Q1. FrontEnd on the server
I have done my best to extoll the virtues of the BE on the server and
the FE mde on each PC but, for whatever reason, it is not going to
happen.
Is it correct, or feasible, that it would be better to at least split
the db and put the FrontEnd mde on the server for sharing, rather than
have the db as a single file?
Q2 FrontEnd on server
One of forms makes heavy use of using unbound 'criteria' boxes at the
bottom of the continuous form and me.filter = "xyz" and me.filteron.
How does Access handle a shared .mde FE during simultaneous user
filters? Is there a conflict that may damage the BackEnd, or - I've
never thought to question this - can an .mde be corrupted!
Q3. Record-level locking
Obviously this is new me since it's not an option in Access97. During
my research, turning off record-level locking *seems* to be one of the
solutions for various locking or bloating problems, yet others
recommend it. So, not much help there<s>. I appreciate our decision
is unique to us, but what do you advise - your input will help.
Background info if it matters: there are two users who enter many new
records every day; I think editing the records is far less common.
Q4. Record - level locking
I've read that the Record-level locking option applies only if
the .mdw is shared - is that true?
Q5. Record- level locking - assuming Q3 is not true...
To quote Help, please note the use of the words 'the current'
database:
"To make record-level locking the new default setting for the current
Microsoft Access database, select the Open databases using record-
level locking check box."
My testing indicates that the setting is global, would you please
clarfiy for me - is the setting global or database specific?
Thank you very much for any help, regards
Marguerite
|
|
hi Marguerite,
mjt7email-ggroups[ at ]yahoo.co.uk wrote:
[Quoted Text] > Q1. FrontEnd on the server
> Is it correct, or feasible, that it would be better to at least split
> the db and put the FrontEnd mde on the server for sharing, rather than
> have the db as a single file?
http://allenbrowne.com/ser-01.html
It is important that each user gets its local copy of the frontend.
> Q2 FrontEnd on server
No problem when each user has its own local copy.
> Q3. Record-level locking
> Background info if it matters: there are two users who enter many new
> records every day; I think editing the records is far less common.
Under normal circumstances you can use the defaults (No locking).
> Q4. Record - level locking
> I've read that the Record-level locking option applies only if
> the .mdw is shared - is that true?
No. This has nothing to do with Access security. Locks are handled via
the .ldb file, which Access/Jet handles automatically.
> Q5. Record- level locking - assuming Q3 is not true...
> "To make record-level locking the new default setting for the current
> Microsoft Access database, select the Open databases using record-
> level locking check box."
> My testing indicates that the setting is global, would you please
> clarfiy for me - is the setting global or database specific?
This is an Access global setting.
mfG
--> stefan <--
|
|
Hello Stefan
Thank you for fast reply!
[Quoted Text]
I've done absolutely EVERY thing I can find on Allen's site, and other
guru's, to do with 2003 so if it's been mentioned, then I've done
it... except of course for the blindingly obvious<s>.
Q3
>> Under normal circumstances you can use the defaults (No locking).<<
Yes, I've left 'Default record locking' to No locks because that's the
way I'm used to. It is the (new to me) 'record-level locking' option
that is the 'potential' problem - but only if it really is responsible
for untoward locking and/or file bloat.
Q4
>>No. This has nothing to do with Access security. Locks are handled via the .ldb file, which Access/Jet handles automatically.<<
I'm used to working with a secure database and It didn't sound right
to me, but I had to ask<s>.
Thank you for your help, regards
Marguerite
|
|
<mjt7email-ggroups[ at ]yahoo.co.uk> wrote in message
news:898839ac-7c90-4ba4-81e3-e6313d7dac8e[ at ]s9g2000prm.googlegroups.com...
[Quoted Text] > Hello Everyone
>
> Please accept my apologies if I've posted to the wrong section - just
> move me along if necessary.
>
> I'm helping someone to improve a small, unsecured Access 2003 database
> for his company. For years they have used just a single table, and the
> consistency of data entry in the table was sad to see.
So your job here is to give them some decent advice and how to do things
correctly so as to avoid the above mess in the future.
>
> Q1. FrontEnd on the server
> I have done my best to extoll the virtues of the BE on the server and
> the FE mde on each PC but, for whatever reason, it is not going to
> happen.
Why should we bother giving you any advice here then? If you not going to
listen to advice here (or they are not), then why bother to ask any
questions at all? You might as well tell a doctor to smoke a cigarette and
watch ashes fall into the patient's body during open heart surgery because
he doesn't want to listen to any good advice.
So basically you telling me for whatever reason anything you do or say
they're not going to listen to you? At the end of day why are you bothering
with this stuff then?
I'm not try to be mean here, I'm just try to point out that you're saying
I'm asking here for some good advice, but you can't listen to that good
advice! it just seems a bit strange, that's all.
For the last twenty years likely that company has installed every other
piece of software on each comptuer, and now you going to break that rule? If
they can't tell the difference between data and an application that gets
installed on each PC, then we are in serious trouble here already. I can try
to help you here, but if the horse will not drink the water, then there's
not really a lot you can do here. The following article of mine not only
tells you to split, but explains *why* you should split, and compares it to
every other application that you've installed on that computer.
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
>
> Is it correct, or feasible, that it would be better to at least split
> the db and put the FrontEnd mde on the server for sharing, rather than
> have the db as a single file?
It's better to separate the data and the application. Then you as a
developer or people within the organization can then work on the next great
release or version of that software without affecting the current user base.
Imagine for a second if while Microsoft was working on the next great verson
of Excel, you could not use the existing verson of Excel? The same goes for
the application your building with MS access. You need to be able to work on
a separate copy the application to fix bugs, create enhancements etc.
If you don't split, then to make changes everybody will have to stop working
and go home for the day. So, one great reason why we split is so you can
develop on something while users are working on a previous version (copy).
On the other hand, maybe the company has lots of extra money to burn, and
when you have to make modifications to the application everyone simply shuts
up shop and goes home and gets a holiday.
>
> Q2 FrontEnd on server
> One of forms makes heavy use of using unbound 'criteria' boxes at the
> bottom of the continuous form and me.filter = "xyz" and me.filteron.
> How does Access handle a shared .mde FE during simultaneous user
> filters? Is there a conflict that may damage the BackEnd, or - I've
> never thought to question this - can an .mde be corrupted!
As a general rule conflicts arise because you're modifying some part of the
application, and you have more than one user in it. Can you imagine somebody
in the building of 200 users have modified what the default printer is going
to be for word, and every other single desktop in the building is now
subject to having that default changed? You are simply asking for trouble
because what one user does can now affect other users running that same
application. While modifications made to filters generally only apply to the
given user, with multiple users hitting the same application at the same
time you're just asking for trouble. I mean so what if a filter screws up,
it's not going to damage the back end, but it's certainly going to cause
problems. The general experience here is that splitting an application means
that the application data is less subject to corruption and damage. This is
not because some filter goes bad, it is just the fact that you stressing out
the network and you have multiple users in the same application.
What happens if one user has a problem with the application and it freezes
up? Now we have a situation where that application could be in an undefined
state and everybody else the building using the application is also screwed
up.
The answer here is very simple:
If the company wants an unstable application that's going to cost them a lot
of support dollars and they don't care about reliability, then allow many
users into that one copy of the application. On the other hand don't come
crying back here when the developers of the application get fired or the
company realizes these people can not build a reliable application. Keep in
mind if you damage the users confidence in this application, often you can
recover from this. After you break users confidence, it's a real uphill
battle to win them back. What happens next is they pay some real money to
bring in some real consultant that can build them an application, and they
often one of staying away more than they should.
>
> Q3. Record-level locking
> Obviously this is new me since it's not an option in Access97. During
> my research, turning off record-level locking *seems* to be one of the
> solutions for various locking or bloating problems, yet others
> recommend it. So, not much help there<s>. I appreciate our decision
> is unique to us, but what do you advise - your input will help.
> Background info if it matters: there are two users who enter many new
> records every day; I think editing the records is far less common.
Row level locking does not change how the application functions or runs. You
still have the same locking abilities as you had before. The ONLY difference
here is that row locking allows you to lock one reocrd where is in previous
versions the whole page would be locked. In most cases the locking of a
whole page in database meant that a few extra records beside the one you're
working on would be locked. In most cases this was not a big problem in
previous versions of access. However if your record size was quite small,
often a page lock could wind up locking quite a few records, and if you had
quite a few users in the application at the same time, then this could wind
up becoming an inconvenience. So the new row locking feature simply avoids
this problem, but the locking settings you have (have to set) in each form
still determines how that form going to function, and this is not changed
since access 97.
So, if you don't set the forms locking (and note that setting the locking
defaults does not change existing forms and the application, you have to set
them on an individual who buy individual basis), then you get the default as
before. So, if two uses start editing the same record, one person's going to
have to be the victim (they'll get that familiar message about the records
been modified by someone else). Note that in access 97 you could set a form
to lock the current record, and if another user tries to start editing it
they would NOT be able to edit it. So, you had this feature before, but it
locked a database page, not a row. The new row locking feature simply means
that reocrds adjacent to the record will not be locked now.
Only you can decide in your given situation if its worth to set up a form to
lock the current record. If you need locking of the record in a form, then I
would actually enable row record locking. By enabling this feature you will
get additional bloat in the back end. However, this just means you increase
the frequency in which to compact the backend. So instead of compacting the
backend weekly, you compact daily or every other day. It's really not a huge
issue, and of course they NOW must have some procedural in place to deal
with compacting of databases which has to be done as part of regular
maintenance of the system...right?
So, that regular maintenance could be a nightly script that the server runs
every other day (so it's not really going to increase your labor costs, or
their labor costs to increase the frequency of compacting the backend).
>
> Q4. Record - level locking
> I've read that the Record-level locking option applies only if
> the .mdw is shared - is that true?
No, the .mdw is the workgroup file, and if you're not using workgroup
security, then each user can have their own copy that file and it don't
really doesn't matter locking wise. However if you are using workgroup
security, then once again all users will share the same copy of that work
group file which should be placed on the server along where the backend is.
However, there's no relationship of ANY type to of the workgroup file and
that of row locking. Don't confuse the workgroup security file (.mdw) with
that of a locking file that access must create (.ldb). If MS access cannot
create that locking file where the backend is located, then access actually
kicks into single user mode and additional users who will not be able to
modify that database file at all.
> My testing indicates that the setting is global, would you please
> clarfiy for me - is the setting global or database specific?
Yes, it does seem to be a global setting. Thus, one should likey set this in
the statup routines for that particular database.
Application.SetOption "Use Row Level Locking", True
My spiders sense tells me that I would avoid using this feature unless you
really need it, but if you need it, it's there for good reason.
note that if one user has a setting set to page locking, and another user
opens a data base with row level locking, it simply means that one user will
be opening records with row locking, and the other with page locking. As a
last note, the trick that access uses to implement row locking is simply to
expand the record size to that of one page! So, at that point then ms-access
uses page locking but ONLY one record is fit into the page! At the end of
the day, there still really is only page locking implemented by jet, and to
lock a reocrd jet simply expands the size of the record to fill one page.
Therefore you get row locking in a roundabout sense, but at the end of the
day the database engine is still locking a page of data in the database
file.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
|
|
Dear Mr Kallal
Hmm...well.
Whether or not you approve of my posting the questions, thank you for
taking the time to respond with a detailed reply.
[Quoted Text] I remember downloading the entire page years ago so I could study it
carefully. I learnt a great deal from you then, as I do still.
Regards
Marguerite
On Dec 30, 12:22 pm, "Albert D. Kallal"
<PleaseNOOOsPAMmkal...[ at ]msn.com> wrote:
> <mjt7email-ggro...[ at ]yahoo.co.uk> wrote in message
>
<snipped>
|
|
On Dec 30, 12:22 pm, "Albert D. Kallal"
<PleaseNOOOsPAMmkal...[ at ]msn.com> wrote:
[Quoted Text] > <mjt7email-ggro...[ at ]yahoo.co.uk> wrote in message
>
<snipped>
I understand Mr Kallal may not wish to communicate with me any
further, but I would appreciate some more help.
Mr Kallal stated the following and it doesn't equate with an
experiment I've just done.
>>Yes, it [Use Row Level Locking option] does seem to be a global setting. Thus, one should likey set this in the statup routines for that particular database.<<
I created and tested a couple of databases with possible variations
between a) Use Row Level Locking = True or False and b) a form = No
locks or Edited record
I made sure I closed Access after toggling the 'Open databases using
record-level locking' setting so it actually 'sticks'. From my small
experiment, it appears that it is not possible to "set this in the
statup routines for that particular database" as stated. It was
consistently global to the Access installation.
Whether or not that is true, it seems to me that a database design
should never rely on the Use Row Level Locking option unless it is
truly crucial for data integrity, for instance where the BackEnd is
Access97 and the FrontEnd might be any one of the later Access
versions (in which case False).
In my case this is not true, therefore I believe the Use Row Level
Locking should be totally ignored and left to any other database
within the company where it IS vital. If the user's PC setting for Row
level locking is False and the form is No locks, then the occasional
inconvenience users might suffer if another user is also amending the
same record is not to be compared with possible (or probable) data
corruption in an entirely different database - such as my previous
example using an Access97 BackEnd.
Have I got any of this right?
Thank you for any help, regards
Marguerite
|
|
hi Marguerite,
mjt7email-ggroups[ at ]yahoo.co.uk wrote:
[Quoted Text] > It was consistently global to the Access installation.
Correct.
> Whether or not that is true, it seems to me that a database design
> should never rely on the Use Row Level Locking option unless it is
> truly crucial for data integrity, for instance where the BackEnd is
> Access97 and the FrontEnd might be any one of the later Access
> versions (in which case False).
Row Level Locking has - almost - nothing to do with database design. It
is vital under some circumstances when you have to implement business logic.
This is a minor difference, but it is indeed a different layer.
> In my case this is not true, therefore I believe the Use Row Level
> Locking should be totally ignored and left to any other database
> within the company where it IS vital.
Correct. As long as you don't need it to get any kind of logic (code) to
work, then you should use the default.
> Have I got any of this right?
Yes, I think so.
mfG
--> stefan <--
|
|
Hello Stefan
Thank you for your reply, the clarification and further explanation is
gratefully received.
Regards
Marguerite
On Dec 31, 12:05 pm, Stefan Hoffmann <ste...[ at ]ste5an.de> wrote:
[Quoted Text] > hi Marguerite,
>
> mjt7email-ggro...[ at ]yahoo.co.uk wrote:
> > It was consistently global to the Access installation.
>
> Correct.
>
> > Whether or not that is true, it seems to me that a database design
> > should never rely on the Use Row Level Locking option unless it is
> > truly crucial for data integrity, for instance where the BackEnd is
> > Access97 and the FrontEnd might be any one of the later Access
> > versions (in which case False).
>
> Row Level Locking has - almost - nothing to do with database design. It
> is vital under some circumstances when you have to implement business logic.
> This is a minor difference, but it is indeed a different layer.
>
> > In my case this is not true, therefore I believe the Use Row Level
> > Locking should be totally ignored and left to any other database
> > within the company where it IS vital.
>
> Correct. As long as you don't need it to get any kind of logic (code) to
> work, then you should use the default.
>
> > Have I got any of this right?
>
> Yes, I think so.
>
> mfG
> --> stefan <--
|
|
mjt7email-ggroups[ at ]yahoo.co.uk wrote:
[Quoted Text] >Q1. FrontEnd on the server
>I have done my best to extoll the virtues of the BE on the server and
>the FE mde on each PC but, for whatever reason, it is not going to
>happen.
Then fire the client. I too hate to sound harsh but having a split
Access system with FEs for each user is a definitive rule that has
been in place for a long time.
See my page on this topic which you've probably already seen.
"Splitting your app into a front end and back end Tips" page at
http://www.granite.ab.ca/access/splitapp/ for more info. See the Auto
FE Updater downloads page http://www.granite.ab.ca/access/autofe.htm
to make this relatively painless.. The utility also supports
Terminal Server/Citrix quite nicely.
>Is it correct, or feasible, that it would be better to at least split
>the db and put the FrontEnd mde on the server for sharing, rather than
>have the db as a single file?
It would be slightly better in that at least you could work on the FE
MDB on your own system and then replace the FE MDB on the server with
your copy.
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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
<mjt7email-ggroups[ at ]yahoo.co.uk> wrote in message
news:b15da39d-39ec-49f4-b9f0-3a3b117cf8e4[ at ]f40g2000pri.googlegroups.com...
On Dec 30, 12:22 pm, "Albert D. Kallal"
<PleaseNOOOsPAMmkal...[ at ]msn.com> wrote:
[Quoted Text] > <mjt7email-ggro...[ at ]yahoo.co.uk> wrote in message
>
<snipped>
>>Yes, it [Use Row Level Locking option] does seem to be a global setting.
>>Thus, one should likey set this in the statup routines for that particular
>>database.<<
>I created and tested a couple of databases with possible variations
between a) Use Row Level Locking = True or False and b) a form = No
locks or Edited record
>I made sure I closed Access after toggling the 'Open databases using
record-level locking' setting so it actually 'sticks'. From my small
experiment, it appears that it is not possible to "set this in the
statup routines for that particular database" as stated.
I pretty much said that it is a **global** setting and if you set it, it
will effect all the databases in question. However I also did suggest that
you *can* in your startup routines set this setting. Of course setting this
will affect all other applications also. If you consider this a problem,
then the solution would be simply to read the setting of the database at
startup, set row locking and then set it back in your shutdown code. So I
never suggested or hinted that is not a global setting here, I was only
suggesting that if you REALLY need the feature enabled, you might want to
set this in your startup code if it's really critical for your application.
On the other hand, keep in mind that most users do not set up their forms to
explicitly lock a record in the table. Keep in mind that changing the
setting in the database (or at startup as I suggested) will NOT row lock
**unless** the form **explicitly** itself is set up to a lock the edited
row. What this means is that for the most part changing this user setting is
not that big of a deal. However if you're worried about this setting, then
you have to put in extra code to put the setting back the way it was on
shutdown. This is not an ideal solution, but it's about the only one you've
got. As I said for the most part if they're not running a bunch of other
applications then it's not that huge of a deal to change this setting.
Furthermore if they are running other applications that have a bunch of
forms this setting will do absolutely nothing for them *unless* they've
actually set their forms to lock the particular row. So at the very end of
the day, yes it is a global setting, but then at the end of the day it's not
that huge of a deal to flip the switch on your users anyway, is it?
>Whether or not that is true, it seems to me that a database design
should never rely on the Use Row Level Locking option unless it is
truly crucial for data integrity, for instance where the BackEnd is
Access97 and the FrontEnd might be any one of the later Access
versions (in which case False).
The above situation does not apply, because if you're connecting to an older
version of a mdb file then the row locking feature is likely ignored and
doesn't do anything anyway.
>If the user's PC setting for Row
level locking is False and the form is No locks, then the occasional
inconvenience users might suffer if another user is also amending the
same record is not to be compared with possible (or probable) data
corruption in an entirely different database
Record locking and data corruption are complete two entirely different
issues. Again 100% different issues here! You have two locking choices here
on the forms data setting property sheet. One setting where you set is no
locks. In this case there is the victim (and two users can edit the same
record at the same time, but the last man out who tries to save the record
will get a message about the record having been modified - do you want to
discard or overwrite the users changes.
As you can see in the above with absolutely no record locking turned on you
don't have corruption of data, there is simply that there's going to be a
victim here and either and that victim can choose to save the data and
overwrite someone else's changes, or discard their changes (I don't exactly
call this data corruption, but it's not an ideal situation if it occurs a
lot for your users).
The 2nd choice you have is to set the form's locking to the edited record.
In this example one user starts editing a record, the second user into the
same record WILL NOT BE ABLE to type or change or modify anything. And, on
the left side of the form in the record selection bar you'll see a little
round circle that looks like a Ghostbusters symbol ( a O with a slash
through it). This setting will prevent two users from editing record at the
same time and was available even in access 97. The ONLY difference of the
row locking is row locking settings = true says are you going to lock a row,
and if you turn it off then you be locking a page. This ONLY applies
**WHEN** using "edited record" locking. if you've not set the form's to
edited record for locking, then setting the row locking does nothing and
doesn't affect your database.
> - such as my previous
>example using an Access97 BackEnd.
As I said if you using access 97 backend, then likely the role locking
feature does not change anything because that feature was not available for
access 97 databases. However, you can still set your forms to edited record,
and two users will not be able to edit this same record at the same time,
and this always worked for a97.
>Have I got any of this right?
I think you're on the right track, but am at a bit of a loss as to the
suggestions that you should never use row locking? Why not?, if you need it
it's there for the taking.
As I mentioned for years many people got away with using page locking, and
for the most part it didn't affect users operations of the database that
much. And as I've appointed out above, changing the setting really doesn't
affect operations of the database for user point much at all either.
The main reason to use row locking is as mentioned if you have a lot of
small records, and you need to enabled edited record locking on forms. With
small records a page lock tends to lock quite a few records, and that will
often prevent other users from tring to edit a DIFFERNT record, but one that
is adjacent to the one that some other user is currently editing. This is
the **ONLY** reason or purpose for this new row locking feature. In other
words, the new row lock feature is to eliminate this inconvenience of
another user being locked out and not being able to edit a record because it
happens to be in the same page as another user who is working on a different
record.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
|
|
|