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

Albert,

I've used a hidden form with persistent connections on a few front ends and
at least once it caused a memory leak and shot CPU usage to 100%. Other
times it works just fine, and it does speed them up when it works. I agree.

Our company is still languishing with some cat5 at 10Mbps while some are at
100Mbps. I can live with the speed at 100, but my co-workers at 10Mbps get
bogged down big time. Just to run a union query that draws data from two
other queries takes well over twice as long on the slower connections. We've
done the comparisions.

I'm thinking SQL server is the next step, to help reduce network traffic
dependence. But that's no small change.

Mike

"Albert D.Kallal" wrote:

Linked front ends are just too slow and time is
money in all businesses.

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 he
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

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

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/AlbertKal.../ridesrpt.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Let's see if we can get past the frustration and the personal friction and
deal with the technical issues, shall we?

To the best of my knowledge, neither Roger nor Klatuu work for Microsoft. I
certainly do not work for Microsoft. So there is no reason to expect any of
us to know the reasoning behind any of Microsoft's design decisions.

For whatever it may be worth, I believe I recall a post from Michael Kaplan
(a person who is extremely knowledgeable about Access) some years ago,
probably in the comp.databases.msaccess newsgroup, in which he indicated
that the change was necessary because design changes without exclusive
access could cause corruption. This is, as I say, just my memory of
something someone else has written, so please don't quote me on that.

The solution is to split the 'front-end', application MDB from the
'back-end' data MDB, so that you always make design changes to a local copy
of the application MDB, which you can then copy to the users' PCs without
overwriting the data in the data MDB. I gather from your other recent post
in this thread that you may be having some issues with this approach. (Some
of the earlier posts in this thread are no longer visible to me, so I do not
have the full picture at this stage.) If so, those issues can almost
certainly be resolved - this approach works well for many people, it can
almost certainly be made to work well for you. I suggest waiting a day or
two to see if this thread may lead to a resolution of that problem, and if
it does not starting a new thread on that issue.

--
Brendan Reynolds

"mikebris@bmcd" wrote in message
...
"Brendan Reynolds" wrote:

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.

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

--
Brendan Reynolds (MVP)

"mikebris@bmcd" wrote in message
...
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!

"Roger Carlson" wrote:

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/...UBED1=ACCESS-L

"mikebris@bmcd" wrote in
message
...
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


"Klatuu" wrote:

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.



"mikebris@bmcd" wrote:

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/comm...c.access.forms

"mikebris@bmcd" wrote in message
...
Albert,

I've used a hidden form with persistent connections on a few front ends
and
at least once it caused a memory leak and shot CPU usage to 100%. Other
times it works just fine, and it does speed them up when it works. I
agree.

Hum, I never seen the above problem (memory leak, or 100% cpu being "due"
to the above). So, the above seems to be a VERY isolated case here.

To be fair, I personally use a global reocrdset to keep the connection
open. However, I not seen ANY posts complaining that this causes a memory
leak, and/or a 100% cpu problem. I just have not seen this.

Do note that the CPU issue jumping to 100%, that was a COMMON problem
in a97, but this was not related "invisible" form, and this problem was
fixed
in later versions. So, I seen nothing in the way of newsgroup posts
to suggest the CPU issue being related to a persistent connection.

I would suggest that you distribute a mde to each station. And, just in case
there
is any diffiltiy in the IT deparmet as to "why" you split, you can read the
follwoing:

http://www.members.shaw.ca/AlbertKal...plit/index.htm

I like the above article very much, as I explay "why" you split, not just
tell you
that you "should".

Give the above the read, as it also explaines why stablity improves so much
when you do split (and, it also explains why you should use a mde).

Our company is still languishing with some cat5 at 10Mbps while some are
at
100Mbps. I can live with the speed at 100, but my co-workers at 10Mbps
get
bogged down big time. Just to run a union query that draws data from two
other queries takes well over twice as long on the slower connections.
We've
done the comparisions.

Interesting, but you are talking about a connection that is 10 times slower.
So, only suffering a loss 1/2 is not bad....

You can actually make application work on a much less bandwidth then a
"10-t", and I explain some solutions he

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal