Re: Replication as a Performance Enhancer?

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?

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


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

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

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. 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!

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

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.

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.

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.

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.

Thanks to all for the tips and thoughts...

No prob! Let us know how it goes.

Neil


.