Individual vs.whole dB locks on forms/reports during modification

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

MS underestimates how much on-the-fly use there is out here of access DBs.
In my engineering co we have teams of 5-10 users modifying data directly in
the .mdb files all the time. Linked front ends are just too slow and time is
money in all businesses. And no, we don't restrict users to forms although
we do use forms and reports to meet a particular need and those needs change
on an hourly basis.

Locks should be smart enough to determine that if noone is using a paticular
form or report, modification is ok. How about a lock on the form, not on the
whole database! Same as it is with tables, right?

Here's another data integrity SNAFU, say a user opens a form for modify and
they are notified that they cannot save changes because they do not have
exclusive access, and they select to go ahead and edit the form....everyone
is still locked out, until that user closes the form view and they will never
be able to save their changes until everyone is logged out. A access
stalemate!

MS data integrity means 'prevent everyone from doing anything', or at least
make it hard enough that they lose the urge to try.



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...ee8ac76a107b&dg=microsoft.public.access.forms
 
Is there a question here or do you just want to rant?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

mikebris@bmcd said:
MS underestimates how much on-the-fly use there is out here of access DBs.
In my engineering co we have teams of 5-10 users modifying data directly in
the .mdb files all the time. Linked front ends are just too slow and time is
money in all businesses. And no, we don't restrict users to forms although
we do use forms and reports to meet a particular need and those needs change
on an hourly basis.

Locks should be smart enough to determine that if noone is using a paticular
form or report, modification is ok. How about a lock on the form, not on the
whole database! Same as it is with tables, right?

Here's another data integrity SNAFU, say a user opens a form for modify and
they are notified that they cannot save changes because they do not have
exclusive access, and they select to go ahead and edit the form....everyone
is still locked out, until that user closes the form view and they will never
be able to save their changes until everyone is logged out. A access
stalemate!

MS data integrity means 'prevent everyone from doing anything', or at least
make it hard enough that they lose the urge to try.



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
Click to expand...
http://www.microsoft.com/office/com...ee8ac76a107b&dg=microsoft.public.access.forms
 
He just want to rant. What he wants is to use the tool incorrectly, but it
be smart enough to let him do things that he should not.
Sure, time = money in business, but how much time is he losing by not
properly designing and building a database that will work for him?
Further more, he obviously does not understand what a database really is.
My recommendation would be that he abandon Access and use Excel. It is
designed to allow inconsistent, unusable, and unedited data.
 
Mike,
You obviously don't know much about databases in general and Access in
particular. If you did, you would be able to configure your database to
overcome the issues you are complaining about.

I suggest you do one of two things. Either find someone who is competent to
structure your database so you can use it or "loose your urge to try" and use
Excel instead.
 
I didn't think new groupies would be reading this....

But to answer your question, NO, there is no question, but there are
implicit sugestions which is what the site is trying to collect..hence the
big buttons where you can either agree or disagree to vote for a
....suggestion.

So to spell them out for those who prefer not to analzye what they read, my
suggestions are:

Fix access so that users can once again modify forms while others are logged
in.
Fix acesss so that users can once again modify reports while others are
logged in.
Fix access so that a user is not allowed to put the whole database in a
locked state while others are logged in.
Employ per form and per report level locks exactly like the table locks so
that other users can sill acess data even though someone is creating a report
or form for their own use.
 
Newbie? You must be kidding.
If you know what you are doing, everything you want "fixed" you could do
yourself with the exception of locking forms and reports which really makes
no sense at all. It is reasonable that multiple users can use the same form.
It is the data you want to protect. Reports are output to present data, not
interactive objects at all.

Now, if you would like to improve your life, why not ask some questions here
about how to implement locking the way you need it, and how to configure your
application to improve performance. I'm sure you will find plenty of people
here more than willing to help.
 
Quippy but useless, and how grandios does someone have to be to suppose they
know what someone else does or does not know or how they need to use a
database to perform their work. Do you work for Microsoft?

Do I know VB - enough to get in trouble
Do I know VBA - enough to do what I need to.

Do you have any idea of how I use Access, what kind of data I use it to
process, or how access speeds up my work? NO.

Do you know how we use access to make 65000 wire terminations land in the
right spot at a power plant in Montana with use multiple tables and a couple
of queries and forms?

Then shut up.

Mike
Controls Engineer
 
New groupies...that's rich.

Suggestion: Fix access so that users can once again modify forms while
others are logged in.
Answer: MS deliberately put this in Access 2000 because of the multitude of
problems with earlier versions that allowed people to edit database objects
like forms and reports while other users are in the database. It was a
major cause of database corruption.

Suggestion: Fix acesss so that users can once again modify reports while
others are logged in.
Answer: ditto above. Although you don't want to use Front-Ends, this is the
best practice. You don't get users walking all over each other's design
changes. Way back when Access allowed it, this was a constant headache for
the database developer.

Suggestion: Fix access so that a user is not allowed to put the whole
database in a locked state while others are logged in.
Answer: Not sure what you mean here. If you're talking about database
objects (forms, reports, etc.), then it's the same answer as above. If you
are talking about one user opening the database and then it's Read-Only by
everyone else, this is a Network Rights issue. If you're talking about
record-locking, you have to have something like this in any multi-user
database product.

Suggestion: Employ per form and per report level locks exactly like the
table locks so that other users can still access data even though someone is
creating a report or form for their own use
Answered above. You're repeating yourself.

Frankly, you're deliberately using the product incorrectly then complaining
about it. You don't use a wrench to pound nails and then wonder why it
doesn't work very well, do you?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
All I could possibly know about you is what is in your posts. And, sadly, I
am very under impressed. You really do not know much about Access or how to
configure it.
No sin there, that what this site is for - to learn. It really is not a
bitch blog.

So, please allow me to give you one pointer with your locking problem that
may help:

From your database menu, select Tools-> Options -> Advanced tab
Then set Default Open Mode to Shared
Set Default Record Locking to No Locks

This will allow mutiple users to edit data as much as they like. It will
only lock a record while it is being updated.

I hope this will help your database and improve your demeanor.
 
It's a very good thing that you're so good you don't need any help, because
I fear with that attitude, you won't get any here.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

mikebris@bmcd said:
Quippy but useless, and how grandios does someone have to be to suppose they
know what someone else does or does not know or how they need to use a
database to perform their work. Do you work for Microsoft?

Do I know VB - enough to get in trouble
Do I know VBA - enough to do what I need to.

Do you have any idea of how I use Access, what kind of data I use it to
process, or how access speeds up my work? NO.

Do you know how we use access to make 65000 wire terminations land in the
right spot at a power plant in Montana with use multiple tables and a couple
of queries and forms?

Then shut up.

Mike
Controls Engineer
Click to expand...
http://www.microsoft.com/office/com...ee8ac76a107b&dg=microsoft.public.access.forms
 
Roger,

I gave him the answer to his locking problem. I don't know that he will
bother to try it. He is the kind of guy who will use a screwdriver as a
chisel and complain when it breaks.
 
Amazing...well....I'm sure my opinion will mean as much to you as you think
yours should mean to me.

I'm not impressed with you either, and what's funny is your the one with the
bronze thingy by your name.

You think I haven't seen the advanced tab? What to say.. This is truly as
waste of time, but I have to say I'm having fun. It's been a while since
I've run across someone so presumptuous. Tell me, your not in an IT group
somewhere, are you?

The closest thing from microsoft I can find is the old bug in 2000 described
in 2003 with a 2002/2003 format file, the only difference is it's sporadic,
while microsoft claims the old bug was reproducible and they even tell you
how to do it.

Beyond that problem though I still don't think Microsoft increased data
integrity by changing their stance from allowing form saves in earlier
versions and not allowing form saves in later releases. Again, my suggestion
to Microsoft is that they allow form level locking and saving exactly as they
do for tables.

Why should microsoft care if I'm editing a form and someone modifies the
underlying queries during my edit. If I save the form, have I lost data or
is the form just going to return an error when it can't find the field it's
looking for. It really should'nt be that hard for million dollar programmers.

Mike
 
Please,
My advanced tab and the other users advanced tabs are as you suggested and
I'm telling you I still get the error. Believe it or not bronze boy.

Oh and by the way, if there a red thingy with a question mark then it a
question. If it's a cartoon bubble thingy, it a SUGGESTION!

If you want to be a hero, go answer the questions you goof, quit beating
your drum in my suggestion post.
 
Again quippy. Two peas in a pod these two.

Go be the newbie hero somewhere else Roger....or at least save it for people
who asked a QUESTION!
 
Gosh, thanks. I will try to remember the difference between a question and a
suggestion. I guess I will need some guidance on the difference between a
suggestion and mindless bitching.
 
I think a read through of the entire thread will show whose better at
"mindless bitching." And get some guidance on reading comprehension...that
would be a good start in my book.

That's all for me! Any last words of wisdom? Klatuu the bronze one? Roger?
 
Linked front ends are just too slow and time is
money in all businesses.
Click to expand...

You should not notice ANY difference performance when you used linked
tables.
(just make sure you keep a persistent connection to the back end - if you do
this, then performance should be just fine).

You can read the above trick, and others here:
http://www.granite.ab.ca/access/performancefaq.htm

(however, in my opinion, just using the persistent connection should fix
your performance problem, and bring things back to un-split performance).
And no, we don't restrict users to forms
Click to expand...

You should consider the above issue. By allowing users to edit raw tables,
then you loose much control over data integrity, and risk many problems.
However, if you must continue VERY bad practices, a Front end with linked
tables should work just fine.

However, you have to realize that lack of developer skills, and wrong
approaches to using software means you are willfully wasting company
resources, and those wasted resources could help feed the poor, or reduce
your downtime and costs to run this software (either way, you are willfully
destroying company property and resources on this problem. This willful
destruction of property might not be as bad as Enron employees, but willful
rejection of good advice on how to use company property and resources
eventually makes you responsible for these losses).
Here's another data integrity SNAFU, say a user opens a form for modify
and
they are notified that they cannot save changes because they do not have
exclusive access
Click to expand...

I have to question a environment where end uses change forms all the time.
Can you imagine if your accounting system allowed end users to change the
design of the application?.

Split your database, keep a persistent connection to restore to un-split
performance, and 90% of your problems will go away. Furhter, if you do
things correclty, the end users should get a mde.

As for users modify reports? Again, if you set things up correctly..then you
RARELY should need to build a new query, or a new report.

Take a look at the following report screens...they will give you some ideas
as to building prompt forms for your users. (these screen shots are from a
application with 160 + forms, 27,000+ lines of code, and MANY MANY users.
Note that I have not had a request for a new report in 3 YEARS!! from those
clients....)

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html
 
not worth my time.

mikebris@bmcd said:
I think a read through of the entire thread will show whose better at
"mindless bitching." And get some guidance on reading comprehension...that
would be a good start in my book.

That's all for me! Any last words of wisdom? Klatuu the bronze one? Roger?
Click to expand...
 
A lot of people read these newsgroups using newsreaders such as Outlook
Express, not via Microsoft's web-based interface. Those people see no
question marks, bubbles, or other 'thingies', and the only indication that a
message is a 'suggestion' rather than a 'question' is some text buried away
at the very end of the message. It was, in my opinion, a poor decision by
Microsoft to invite people to submit suggestions to the existing newsgroups
in this way. It might have been better to start a new newsgroup (or groups,
one per product) specifically for suggestions.

BTW: Both Klatuu and Roger are helpful and knowledgeable people, and neither
of them are newbies in terms of either Access or newsgroups.
 
Brendan Reynolds said:
A lot of people read these newsgroups using newsreaders such as Outlook
Express, not via Microsoft's web-based interface. Those people see no
question marks, bubbles, or other 'thingies', and the only indication that a
message is a 'suggestion' rather than a 'question' is some text buried away
at the very end of the message. It was, in my opinion, a poor decision by
Microsoft to invite people to submit suggestions to the existing newsgroups
in this way. It might have been better to start a new newsgroup (or groups,
one per product) specifically for suggestions.

BTW: Both Klatuu and Roger are helpful and knowledgeable people, and neither
of them are newbies in terms of either Access or newsgroups.
Click to expand...

I hope you'll appreciate that I'm not going to attack you and suggest your
an idiot for having an opinion on what MS should or should not have allowed
in newsgroups. And maybe you should let Klatuu and Roger know that this is
an option provided on the web interface and as such they may encounter
opinion from time to time. Then maybe Roger wouldn't post things like, "Is
there a question here or do you just want to rant?" He'd know the answer
without having to ask it like an #$#%@.

1. In the last post, I didn't mean to suggest they were newbies, I meant to
say that they are hero's to newbies...or at least see themsleves that way.
Per my experience they are a couple of primadonna's; which indicates nothing
about their knowledge. I'm sure they can whip up some excellent code. And I
should correct a previous error of mine, an early post in which Klatuu
thought I called him a newbie, really should have read, "news groupies" not
"new groupies". But Klatuu would probably take issue with that as well.

2. I'm sure they've helped many. But, for all I know, they just like rant
posting as much as I do.

3. Niether Roger or Klatuu indciated why (new in 200x) MS requires
exclusive access on forms, reports, and macros, when editing table defs is
allowed without excusive access. I complained because for the way I work with
access, it's a step backward, not forward.

IN FACT, Klatuu gave me bogus advice. Look back at Klatuu's post. He
acutually suggests how to go into the options to change record locking
properties. This has nothing to do with excusive access for creating forms
and macros.

Klatuu wrote:
"You obviously don't know much about databases in general and Access in
particular. If you did, you would be able to configure your database to
overcome the issues you are complaining about."

Klatuu has yet to tell me how to configure access so that I can modify a
report or form without exclusive acess or why I don't need exclusive access
to modify a table.

As for Klatuu's suggestion to use excel.....I'm hoping that's not the
suggestion that display's his true potential.

Mike
 
Back
Top