Our work environment is a WinXP\A2003 split Departmental db (SIMS) w/
7 users on a corporate network (12k other computer users corporate-wide).

During the "day", performance on SIMS is very "slow". Six (6) of the SIMS
users enter data into cases as they investigate them. This may take 45
minutes each instance though the "instances' may only occur twice a week. My
primary function is to retrieve data and report the information they've
collected. To do this and avoid the performance "slowness" during the 'day',
I have a complete "Desk" copy of SIMS on my HD (C;\Program Files\SIMS\Desk\)
which includes a copy of the backend on my HD. This essentially puts a
'demo' version of SIMS on a separate directory on my HD for my use. When I
get ready to "work" , I copy the current <SIMS_Backend.mdb> from the network
location to my "Desk" copy (bringing in the most current data of that
moment) and then work off of my "C" drive, making performance 'lightning
fast'. I run my queries and produce my reports "in nothing flat".



The problem is for the other 6 users. Even though all of the performance
enhancements recommended in various Access websites in this NG have been
done, whenever they enter the data and switch screens and enter data again
in another form, the "slowness" of the network is painful. Depending upon
the time of day for instance, in typing the words, "Once a upon a time..."
may take 30-40 seconds to completely appear in the text box whereas if I
typed those same words into my "Desk" copy, it would only take as long as it
would take for me to actually type those words onto the keyboard (2.6
seconds- er, I'm not the fastest typist around).



Although all I'm doing is extracting data and not inputting data, I'm
wondering if in effect what I'm doing is an abbreviated form of 'replication'.
If that is so, would Access 'replication' be a viable alternative for the
other 6 users to "speed things up'? Would that entail those 6 users have a
"travel copy' of SIMS as well as retaining the network copy they are
currently using?



Thanks for your thoughts...

Earl

"Earl Anderson" <isobadd (AT) optonline (DOT) net> wrote



So, you have a split database.
You install the front end on each computer
You using a mde in place of a mdb
You have persisting connection at at times.

If you done the above, I see little, if any reason for performance. The next
thing I would look at is your network. Are you talking about a office LAN,
or a WAN?

I explain the difference here:

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


You not mentioned how large your tables are (like in the millions of
records), but you might want to provide some more information on the above
speed of your network, and then that of how much data you are pulling into
forms (and, MAKE SURE you have that persisting connection).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

1. We are not using a 'mde'. Isn't the purpose of a 'mde' is to keep the
users from 'screwing around' in Design View?. Our users have no idea of
what Design View is, what it does or how to get to it. All menu options and
toolbar icons relative to design changes have been disabled.

2. We are using a file share in a WAN. I know...I know. This is bad, but
we are in the IT process-loop of moving to SQL Server. Luckily, we have not
had corruption problems in over 2 years. Then again, thru this NG and other
sources, I have come to believe that a client server system pretty much
eliminates corruption problems, but does not necessarily improve db
performance.

3. We are nowhere close to 'millions' of records...more like 50k records.
There are 12 tables and 14 forms/subforms associated with this particular
segment of the app.

4. The article was info-packed and thought provoking regarding network
questions. I will read and re-read the article in preparation of asking
questions of our IT folks...I know they are going to come back with some
techno mumbo-jumbo because they hate dealing with Access. hopefully, out of
the mumbo-jumbo, I will be able to discern the speed of our network.

5. The article also covered 'Replication' which was my original question.
It seems that replication would overcome our particular network crawl
problems although most text and articles on the subject make it seem too
difficult to 'tame' to be useful.

On to trying to get answers to the network questions...

Thx
Earl


"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote

David,
As I read Albert's article on Thin Client Technology, TS seems to be the way
to go. I will investigate the possibility in our company.
Thx...
Earl

"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote

"Earl Anderson" <isobadd (AT) optonline (DOT) net> wrote

Sure, one great benefit of using a mde is it prevents users from changing
things. However there are are few other issues that come up in terms of
making an application perform better.

First and foremost a mde will always ensure that your code is compiled
without errors. If you distribute a mdb to each user, there's always the
possibility that the code is not necessarily compiled (or worse contains
compile errors). Thus some forms can be sluggish because the applciaton is
not compiled and that code has to be compiled when that form loads. This
problem will also cause a good increase in bloat of the database. This is
not usually a problem, but I've seen some of my databases that have a real
tough time staying compiled for some reason. So, I do recommend that you
distribute a compiled front end as it is smaller, runs faster.

One significant benefit of using mde's is any un-handled error no matter
where in your application will NOT cause variables to be reset. This feature
alone tends to make your application far more robust and trouble freee in in
general. (imagine...no error handleing needed, and your arabiles never get
re-set!!).

So using an MDE is simply an around good idea and practive as a devloper.
It's kinda one of those last check listings you do and it does force you to
ensure that you code is compiled and free of compile errors before
distribution to your users.

The above likely pinpoints the issue of slow performance here on your
network.

The above is correct. And while sql server often don't make that much of a
difference on a well running LAN, the differences in performance can start
to be **significant** when runing on limited bandwidth connections such as a
wan. You'll likely require some additional effort to take advantage of SQL
server with an MS access, but when doing so there are significant
advantages.

About the only thing you can do in limited bandwidth situations is to ensure
that you never simply open up a form attached to a large table without using
some type of where clause. in fact even when I don't have limited bandwidth
situations, I'd never open up a main form to more than one record. If you're
only dragging one record across the network, then you're not transfering a
lot of data. I explain this concept of searching in only bringing one record
into a form here:

http://www.members.shaw.ca/AlbertKal...rch/index.html

So your requirement here is to simply reduce the number of records
transferred into a form when you load it, and that simply means ask the user
what record to work with before you launch the form, and then open up the
form to the one record by using the forms where clause. so your forms can
continue to be bound, but you wanna use the where clause to restrict the
number of records pulled across the network into that form.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

TS is a great technology, and would make a big difference. But note that
30-40 seconds just to type something is absurdly slow, and there might be
something else going on that you can fix. Even in a poorly-designed system,
it shouldn't be that bad.

I noticed that you said you had a split db. But you didn't confirm that you
have the front end installed on each user's local drive. I realize that it
makes little sense to have a split db otherwise; but, for the sake of
looking into all issues here, just wondering if perhaps you didn't do that.

Also note that it's possible to open a form in Data Entry mode. If your
users are just entering data, then you can give them two "modes" for the
form: View/Edit mode, and Data Entry mode. Data Entry mode would only show
records that they've added in that session (which should be 1 record, from
what I'm gathering), and should be lightening fast. To open a form in Data
Entry mode, set the Data Mode argument of the OpenForm command to acFormAdd.

Or, since you only enter one record at a time, why not just turn your form's
Allow Additions property to False; give the users an Add New button; add a
record for them in the underlying recordset; and then open the form to that
single, new record for editing? When they add new records, they'd only be
working with that one record. Again, would be very fast.

So there are a few ideas for you. Bottom line: TS is a great solution; but
it's also an extreme solution, requiring a lot of time and expense to
implement. Your problem may have a simple solution. 30-40 seconds just to
type a few words is far, far outside the range of what you should be
experiencing, even on a poorly designed system. Sometimes there's a simple
solution.

I recall once I had a user who was using the front end on his home computer,
and was connecting to the back end (SQL Server) over the Internet. It took
him 10 minutes just to open the program. When I looked into it, it turns out
it had nothing to do with using a SQL Server vs. an Access back end. For
some reason, him accessing the MDW (security) file over the network is what
was slowing everything down. I found that when I distributed a copy of the
MDW file to his local machine, and had his shortcut point to that local
copy, the database opened right up! But when it had to get user information
from the MDW file over the Internet, it bogged down and took 10 minutes to
open. Go figure.

So sometimes it's something simple like that. Again, don't just give up on
your system. Something is terribly wrong here. There might be a simple fix.

One other thing. You wrote:

"whenever they enter the data and switch screens and enter data again in
another form, the "slowness" of the network is painful."

Are you saying that the slowness doesn't occur until they begin to enter
data in that second form? That entering data in the first form is fine? If
so, then please describe what's going on between these two forms. What's
their relationship? Are both forms open at the same time? Do they share any
common data? Etc.

Neil



"Earl Anderson" <isobadd (AT) optonline (DOT) net> wrote

"Neil" <nrgins (AT) gmail (DOT) com> wrote


Yes, each user has the frontend on their individual pc.

The users (investigators) enter new cases and edit previous cases so we're
on View/Edit mode.

I've decided to do something like your suggestion. Instead of opening "All"
records, I'm going to either have a dropdown on the Switchboard after
selecting their territory, a name selection box whereby they select their
name and then only their cases are pulled along with a "Add New Case" button
to enter new cases. The other way would be based upon their initial logon
whereby based upon that logon, when they select their teritory from the
Switchboard, only their cases and a "Add" button appears.

I should add that the typing "slowness" also occurs sometime in Word or
Outlook, leading me to believe the network has a lot to do with it.


That is essentially what I've done with my "Desk" copy as described in my
initial post.

The first form (tab) captures initially reported information in textboxes
and a memo field and the second tab captures the details of the
investigation in textboxes and a memo field. There are also four other tabs
for capturing certain types of information. What I was describing occurs in
the either one of the memo fields as the textboxes are mostly static value
dropdowns. You can imagine how typing a narrative and waiting for the words
to appear can be frustrating.

Thanks to all for the tips and thoughts...
Earl

"Earl Anderson" <isobadd (AT) optonline (DOT) net> wrote:

Possibly but maybe not. Maybe those systems have insufficient RAM or a wierd anti
virus problem.

This is the problem to concentrate on. Which you are already aware of but some of
the folks in this thread haven't quite grasped. One suggestion that you are going
to implement to reduce the number of records may help with this.

Has SP3 and the combo box hotfix been installed on these systems?

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/

I think that'll make a big difference. But you could take it one step
further: unless they need to see more than one case at a time (e.g., if they
need to go back and forth between their cases), why not just always have
them work with only one record? Either they select a case, and you bring up
that record; or they click Add New, and you bring up a new record? Make it
as lean as possible, unless there's a need for them to work with multiple
records.

And, if there is a need to have them work with multiple reocrds, but that's
only some of the time, then have that be a separate mode. Have the default
be Add/Edit, where they are either adding or editing a single record, and
the alternate mode is where they can work with multiple records (and even
there you can have them select the records they want to work with from a
list, so you don't have to pull in ALL of their records).

Another thing to consider are your lookups. Do you have a lot of data in
lookups? Access has to pull all of that. If it doesn't change often, you can
put that in local tables. Or, if it's in network tables, you have have the
list only be populated if the user actually uses the lookup (in the
control's Got Focus event). This way, if they don't go to that control,
there's no need to pull in the data, and you'll have faster performance.

Do you have subforms? That will slow you down as well. If you can put your
subforms on pop-ups, that will keep it from having to pull in the data until
needed. Or, alternativesly, can put each subform on its own tab in a tabbed
control and only populate the subform with data when the user clicks on that
tab.

Etc.

Sounds like it. Sounds like you may need to upgrade your network or get
someone in who can resolve the problem.

No, the back end was still accessed over the server. I'm saying all he did
was bring in the MDW file to his local machine, nothing else. Still accessed
the data across the WAN. But moving the MDW file to his local machine made a
HUGE difference.

Is there a need to have all of the tabs open at once? Each one is a live
connection, which uses network resources. Can you close one before moving on
to the next?

Also, is the initial info and investigation info stored in the same table?
If so, then why not just list the initially reported info in read-only
fields in the investigation info form? That way, you only have one form open
to that table.

You should play with this a bit, see if there's any difference in
performance if you work with one form open vs. multiple forms open (results
might vary depending on network traffic at the time, so should pick either a
high-traffic time, or a dead time, like the middle of the night). Could be a
little tweaking here might make a big difference, and if you could avoid
having multiple tables or multiple forms open concurrently, that might help.
But play around with it and see.

Another thought is that you could move to unbound forms, so that you're not
connected to the network at all, except when retrieving data or updating
data. The users would, essentially, be working with a local copy of the data
as they type, with that data being written to the network when they update.
A bit more work, and requires lots more overhead to maintain when you make
changes.

No prob! Let us know how it goes.

Neil

"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote

But you have a experience setting it up, and probably in related things as
well. Take someone with no experience in those areas, I think it would take
a bit longer.

I just assumed they'd buy a dedicated server for that purpose, and not use
an existing server.

And then you have the issue that, since TS users share a file, there can be
no local tables unless it's configured such that each user has their own
copy of the front end (multiple FE copies in user directories; each user has
their own shortcut that points to their copy of the front end; need to
maintain that when updating the FE; etc.)

Neil