I have an application with distributed front ends on local
drives in Access 2002 and the backend sits in Win 2000 is
Acess 2000.

Everything works well until more than 10 users are doing
data entry. In the users words, the system goes into
meltdown.

Some users have also experienced data loss and have had to
rekey their data.

I have followed all the guidelines regarding FE and BE as
well as guidelines for working in a multiuser environment.

Is the slowing down and the data loss due to to Access or
the LAN?

What is the cutoff for Access ( no of users or
transactions) before you have to go to SQL server?

regards
george

George,

I've heard of numbers of users exceeding 200, so my guess is this is not
your problem. It may be network performance, but that's not the only
possibility. Have you taken any measures re. performance? Take a look at the
following link in Tony Toews' site:

http://www.granite.ab.ca/access/performancefaq.htm

HTH,
Nikos

"GeorgeMar" <(E-Mail Removed)> wrote in message
news:1a9be01c44ecd$f0e94c60$(E-Mail Removed)...
> I have an application with distributed front ends on local
> drives in Access 2002 and the backend sits in Win 2000 is
> Acess 2000.
>
> Everything works well until more than 10 users are doing
> data entry. In the users words, the system goes into
> meltdown.
>
> Some users have also experienced data loss and have had to
> rekey their data.
>
> I have followed all the guidelines regarding FE and BE as
> well as guidelines for working in a multiuser environment.
>
> Is the slowing down and the data loss due to to Access or
> the LAN?
>
> What is the cutoff for Access ( no of users or
> transactions) before you have to go to SQL server?
>
> regards
> george
>

GeorgeMar wrote:

>I have an application with distributed front ends on local
>drives in Access 2002 and the backend sits in Win 2000 is
>Acess 2000.
>
>Everything works well until more than 10 users are doing
>data entry. In the users words, the system goes into
>meltdown.
>
>Some users have also experienced data loss and have had to
>rekey their data.
>
>I have followed all the guidelines regarding FE and BE as
>well as guidelines for working in a multiuser environment.
>
>Is the slowing down and the data loss due to to Access or
>the LAN?
>
>What is the cutoff for Access ( no of users or
>transactions) before you have to go to SQL server?


The typical causes for this effect is less than optimal
design/implementation on your part, a network with flakey
hardware and/or an overloaded server. There are some things
to think about on Tony Toews' web site:

http://www.granite.ab.ca/access/performancefaq.htm
--
Marsh
MVP [MS Access]

Thank you both for your reply.

I have read Tony Toew's website many times in the course
of developing this application. I went over it again
after your comments and found that the only two things not
implemented are the use of BeginTrans and CommitTrans, and
the use of a persistent recordset.

Currently, my startup form is unbound but it remains open
until the user exits. The article recommends a hidden
form, but since the startup form is always open, I may
base my form on a table linked to the Back end database.

That takes care of the opening and deleting of the LDB.
In Tony's article under "Refreshing table links can also
be quite slow" is he referring to a refreshing of links
everytime the backend database is called or the refreshing
when the application is deployed.

Finally, is there anything else I may need to look at?

your help is most appreciated
george


>-----Original Message-----
>GeorgeMar wrote:
>
>>I have an application with distributed front ends on
local
>>drives in Access 2002 and the backend sits in Win 2000
is
>>Acess 2000.
>>
>>Everything works well until more than 10 users are doing
>>data entry. In the users words, the system goes into
>>meltdown.
>>
>>Some users have also experienced data loss and have had
to
>>rekey their data.
>>
>>I have followed all the guidelines regarding FE and BE
as
>>well as guidelines for working in a multiuser
environment.
>>
>>Is the slowing down and the data loss due to to Access
or
>>the LAN?
>>
>>What is the cutoff for Access ( no of users or
>>transactions) before you have to go to SQL server?
>
>
>The typical causes for this effect is less than optimal
>design/implementation on your part, a network with flakey
>hardware and/or an overloaded server. There are some
things
>to think about on Tony Toews' web site:
>
>http://www.granite.ab.ca/access/performancefaq.htm
>--
>Marsh
>MVP [MS Access]
>.
>

George,

I believe it is referring to refreshing of links everytime the backend
database is called... depluyment is a once off (or once in a while) thing,
and wouldn't be a considerable problem.
Now, on the persistent recordset, it does make a difference! I would suggest
you make a very small dummy table (rather than use a functional one), and
either open it as a recordset on your main form's on open event, or add a
non-visible control to your form and bind it to a field in the table (which
will most likely have only one field and 1-23 records anyway). I use the
recordset method, so I can keep my main form unbound.

HTH,
Nikos

"GeorgeMar" <(E-Mail Removed)> wrote in message
news:1b1a701c44f7a$3179b240$(E-Mail Removed)...
> Thank you both for your reply.
>
> I have read Tony Toew's website many times in the course
> of developing this application. I went over it again
> after your comments and found that the only two things not
> implemented are the use of BeginTrans and CommitTrans, and
> the use of a persistent recordset.
>
> Currently, my startup form is unbound but it remains open
> until the user exits. The article recommends a hidden
> form, but since the startup form is always open, I may
> base my form on a table linked to the Back end database.
>
> That takes care of the opening and deleting of the LDB.
> In Tony's article under "Refreshing table links can also
> be quite slow" is he referring to a refreshing of links
> everytime the backend database is called or the refreshing
> when the application is deployed.
>
> Finally, is there anything else I may need to look at?
>
> your help is most appreciated
> george
>
>
> >-----Original Message-----
> >GeorgeMar wrote:
> >
> >>I have an application with distributed front ends on
> local
> >>drives in Access 2002 and the backend sits in Win 2000
> is
> >>Acess 2000.
> >>
> >>Everything works well until more than 10 users are doing
> >>data entry. In the users words, the system goes into
> >>meltdown.
> >>
> >>Some users have also experienced data loss and have had
> to
> >>rekey their data.
> >>
> >>I have followed all the guidelines regarding FE and BE
> as
> >>well as guidelines for working in a multiuser
> environment.
> >>
> >>Is the slowing down and the data loss due to to Access
> or
> >>the LAN?
> >>
> >>What is the cutoff for Access ( no of users or
> >>transactions) before you have to go to SQL server?
> >
> >
> >The typical causes for this effect is less than optimal
> >design/implementation on your part, a network with flakey
> >hardware and/or an overloaded server. There are some
> things
> >to think about on Tony Toews' web site:
> >
> >http://www.granite.ab.ca/access/performancefaq.htm
> >--
> >Marsh
> >MVP [MS Access]
> >.
> >

Be sure to double check the LAN traffic patterns when things
are going south, especially look for errors and retry rates.
This typically requires special equipment and an experienced
person.

Make sure that your combo/list boxes and bound
forms/subforms only retrieve the minimum number of records
from the server.
--
Marsh
MVP [MS Access]


GeorgeMar wrote:

>Thank you both for your reply.
>
>I have read Tony Toew's website many times in the course
>of developing this application. I went over it again
>after your comments and found that the only two things not
>implemented are the use of BeginTrans and CommitTrans, and
>the use of a persistent recordset.
>
>Currently, my startup form is unbound but it remains open
>until the user exits. The article recommends a hidden
>form, but since the startup form is always open, I may
>base my form on a table linked to the Back end database.
>
>That takes care of the opening and deleting of the LDB.
>In Tony's article under "Refreshing table links can also
>be quite slow" is he referring to a refreshing of links
>everytime the backend database is called or the refreshing
>when the application is deployed.
>
>Finally, is there anything else I may need to look at?
>
>your help is most appreciated
>george
>
>
>>-----Original Message-----
>>GeorgeMar wrote:
>>
>>>I have an application with distributed front ends on
>local
>>>drives in Access 2002 and the backend sits in Win 2000
>is
>>>Acess 2000.
>>>
>>>Everything works well until more than 10 users are doing
>>>data entry. In the users words, the system goes into
>>>meltdown.
>>>
>>>Some users have also experienced data loss and have had
>to
>>>rekey their data.
>>>
>>>I have followed all the guidelines regarding FE and BE
>as
>>>well as guidelines for working in a multiuser
>environment.
>>>
>>>Is the slowing down and the data loss due to to Access
>or
>>>the LAN?
>>>
>>>What is the cutoff for Access ( no of users or
>>>transactions) before you have to go to SQL server?
>>
>>
>>The typical causes for this effect is less than optimal
>>design/implementation on your part, a network with flakey
>>hardware and/or an overloaded server. There are some
>things
>>to think about on Tony Toews' web site:
>>
>>http://www.granite.ab.ca/access/performancefaq.htm
>>--
>>Marsh
>>MVP [MS Access]
>>.
>>

Thank you Nikos.

I will try this with my next release.


>-----Original Message-----
>George,
>
>I believe it is referring to refreshing of links
everytime the backend
>database is called... depluyment is a once off (or once
in a while) thing,
>and wouldn't be a considerable problem.
>Now, on the persistent recordset, it does make a
difference! I would suggest
>you make a very small dummy table (rather than use a
functional one), and
>either open it as a recordset on your main form's on open
event, or add a
>non-visible control to your form and bind it to a field
in the table (which
>will most likely have only one field and 1-23 records
anyway). I use the
>recordset method, so I can keep my main form unbound.
>
>HTH,
>Nikos
>
>"GeorgeMar" <(E-Mail Removed)> wrote
in message
>news:1b1a701c44f7a$3179b240$(E-Mail Removed)...
>> Thank you both for your reply.
>>
>> I have read Tony Toew's website many times in the course
>> of developing this application. I went over it again
>> after your comments and found that the only two things
not
>> implemented are the use of BeginTrans and CommitTrans,
and
>> the use of a persistent recordset.
>>
>> Currently, my startup form is unbound but it remains
open
>> until the user exits. The article recommends a hidden
>> form, but since the startup form is always open, I may
>> base my form on a table linked to the Back end database.
>>
>> That takes care of the opening and deleting of the LDB.
>> In Tony's article under "Refreshing table links can also
>> be quite slow" is he referring to a refreshing of links
>> everytime the backend database is called or the
refreshing
>> when the application is deployed.
>>
>> Finally, is there anything else I may need to look at?
>>
>> your help is most appreciated
>> george
>>
>>
>> >-----Original Message-----
>> >GeorgeMar wrote:
>> >
>> >>I have an application with distributed front ends on
>> local
>> >>drives in Access 2002 and the backend sits in Win 2000
>> is
>> >>Acess 2000.
>> >>
>> >>Everything works well until more than 10 users are
doing
>> >>data entry. In the users words, the system goes into
>> >>meltdown.
>> >>
>> >>Some users have also experienced data loss and have
had
>> to
>> >>rekey their data.
>> >>
>> >>I have followed all the guidelines regarding FE and BE
>> as
>> >>well as guidelines for working in a multiuser
>> environment.
>> >>
>> >>Is the slowing down and the data loss due to to Access
>> or
>> >>the LAN?
>> >>
>> >>What is the cutoff for Access ( no of users or
>> >>transactions) before you have to go to SQL server?
>> >
>> >
>> >The typical causes for this effect is less than optimal
>> >design/implementation on your part, a network with
flakey
>> >hardware and/or an overloaded server. There are some
>> things
>> >to think about on Tony Toews' web site:
>> >
>> >http://www.granite.ab.ca/access/performancefaq.htm
>> >--
>> >Marsh
>> >MVP [MS Access]
>> >.
>> >
>
>
>.
>

Thank you Marsh.

All my forms and combos were modified a long time ago to
get the minimum amount of data and the record source and
rowSources are created On Form Load. One thing I believe
may be hapenning is that I am not clearing the record
source and rowSource on closing the form. The forms and
combos may still have the sources after closing.

Now that you mention forms and combos, the form that is
the culprit has a subform of a datasheet form with 6
combos for each record. The user selects a value from A
to E which is stored as 1 to 5 in the table. The combos'
rowSource Type are Value List and the RowSource is
A,5,B,4,C,3,D,2,E,1.

Since the combos have a Value List, they shouldn't be
slowing down the data entry, should they?

many thanks
george

>-----Original Message-----
>Be sure to double check the LAN traffic patterns when
things
>are going south, especially look for errors and retry
rates.
>This typically requires special equipment and an
experienced
>person.
>
>Make sure that your combo/list boxes and bound
>forms/subforms only retrieve the minimum number of records
>from the server.
>--
>Marsh
>MVP [MS Access]
>
>
>GeorgeMar wrote:
>
>>Thank you both for your reply.
>>
>>I have read Tony Toew's website many times in the course
>>of developing this application. I went over it again
>>after your comments and found that the only two things
not
>>implemented are the use of BeginTrans and CommitTrans,
and
>>the use of a persistent recordset.
>>
>>Currently, my startup form is unbound but it remains
open
>>until the user exits. The article recommends a hidden
>>form, but since the startup form is always open, I may
>>base my form on a table linked to the Back end database.
>>
>>That takes care of the opening and deleting of the LDB.
>>In Tony's article under "Refreshing table links can also
>>be quite slow" is he referring to a refreshing of links
>>everytime the backend database is called or the
refreshing
>>when the application is deployed.
>>
>>Finally, is there anything else I may need to look at?
>>
>>your help is most appreciated
>>george
>>
>>
>>>-----Original Message-----
>>>GeorgeMar wrote:
>>>
>>>>I have an application with distributed front ends on
>>local
>>>>drives in Access 2002 and the backend sits in Win 2000
>>is
>>>>Acess 2000.
>>>>
>>>>Everything works well until more than 10 users are
doing
>>>>data entry. In the users words, the system goes into
>>>>meltdown.
>>>>
>>>>Some users have also experienced data loss and have
had
>>to
>>>>rekey their data.
>>>>
>>>>I have followed all the guidelines regarding FE and BE
>>as
>>>>well as guidelines for working in a multiuser
>>environment.
>>>>
>>>>Is the slowing down and the data loss due to to Access
>>or
>>>>the LAN?
>>>>
>>>>What is the cutoff for Access ( no of users or
>>>>transactions) before you have to go to SQL server?
>>>
>>>
>>>The typical causes for this effect is less than optimal
>>>design/implementation on your part, a network with
flakey
>>>hardware and/or an overloaded server. There are some
>>things
>>>to think about on Tony Toews' web site:
>>>
>>>http://www.granite.ab.ca/access/performancefaq.htm
>>>--
>>>Marsh
>>>MVP [MS Access]
>>>.
>>>
>
>.
>

You're right, a value list combo is not going to the cause
of slowing things down. It's the querys that cause the I/O
that takes the time.

Setting a form's RecordSource property should not be an
issue, but, if you want to try it, you can be sure that none
of that Filter/OrderBy junk gets in the way by always
using:
DoCmd.Close acForm, Me.Name, acSaveNo

If you've gone through all of Tony's items, I guess I'm at a
loss for any other suggestions.
--
Marsh
MVP [MS Access]



GeorgeMar wrote:

>Thank you Marsh.
>
>All my forms and combos were modified a long time ago to
>get the minimum amount of data and the record source and
>rowSources are created On Form Load. One thing I believe
>may be hapenning is that I am not clearing the record
>source and rowSource on closing the form. The forms and
>combos may still have the sources after closing.
>
>Now that you mention forms and combos, the form that is
>the culprit has a subform of a datasheet form with 6
>combos for each record. The user selects a value from A
>to E which is stored as 1 to 5 in the table. The combos'
>rowSource Type are Value List and the RowSource is
>A,5,B,4,C,3,D,2,E,1.
>
>Since the combos have a Value List, they shouldn't be
>slowing down the data entry, should they?
>
>many thanks
>george
>
>>-----Original Message-----
>>Be sure to double check the LAN traffic patterns when
>things
>>are going south, especially look for errors and retry
>rates.
>>This typically requires special equipment and an
>experienced
>>person.
>>
>>Make sure that your combo/list boxes and bound
>>forms/subforms only retrieve the minimum number of records
>>from the server.
>>--
>>Marsh
>>MVP [MS Access]
>>
>>
>>GeorgeMar wrote:
>>
>>>Thank you both for your reply.
>>>
>>>I have read Tony Toew's website many times in the course
>>>of developing this application. I went over it again
>>>after your comments and found that the only two things
>not
>>>implemented are the use of BeginTrans and CommitTrans,
>and
>>>the use of a persistent recordset.
>>>
>>>Currently, my startup form is unbound but it remains
>open
>>>until the user exits. The article recommends a hidden
>>>form, but since the startup form is always open, I may
>>>base my form on a table linked to the Back end database.
>>>
>>>That takes care of the opening and deleting of the LDB.
>>>In Tony's article under "Refreshing table links can also
>>>be quite slow" is he referring to a refreshing of links
>>>everytime the backend database is called or the
>refreshing
>>>when the application is deployed.
>>>
>>>Finally, is there anything else I may need to look at?
>>>
>>>your help is most appreciated
>>>george
>>>
>>>
>>>>-----Original Message-----
>>>>GeorgeMar wrote:
>>>>
>>>>>I have an application with distributed front ends on
>>>local
>>>>>drives in Access 2002 and the backend sits in Win 2000
>>>is
>>>>>Acess 2000.
>>>>>
>>>>>Everything works well until more than 10 users are
>doing
>>>>>data entry. In the users words, the system goes into
>>>>>meltdown.
>>>>>
>>>>>Some users have also experienced data loss and have
>had
>>>to
>>>>>rekey their data.
>>>>>
>>>>>I have followed all the guidelines regarding FE and BE
>>>as
>>>>>well as guidelines for working in a multiuser
>>>environment.
>>>>>
>>>>>Is the slowing down and the data loss due to to Access
>>>or
>>>>>the LAN?
>>>>>
>>>>>What is the cutoff for Access ( no of users or
>>>>>transactions) before you have to go to SQL server?
>>>>
>>>>
>>>>The typical causes for this effect is less than optimal
>>>>design/implementation on your part, a network with
>flakey
>>>>hardware and/or an overloaded server. There are some
>>>things
>>>>to think about on Tony Toews' web site:
>>>>
>>>>http://www.granite.ab.ca/access/performancefaq.htm
>>>>--
>>>>Marsh
>>>>MVP [MS Access]
>>>>.
>>>>
>>
>>.
>>

Thanks Marsh.

I try it and I'll persevere.


>-----Original Message-----
>You're right, a value list combo is not going to the cause
>of slowing things down. It's the querys that cause the
I/O
>that takes the time.
>
>Setting a form's RecordSource property should not be an
>issue, but, if you want to try it, you can be sure that
none
>of that Filter/OrderBy junk gets in the way by always
>using:
> DoCmd.Close acForm, Me.Name, acSaveNo
>
>If you've gone through all of Tony's items, I guess I'm
at a
>loss for any other suggestions.
>--
>Marsh
>MVP [MS Access]
>
>
>
>GeorgeMar wrote:
>
>>Thank you Marsh.
>>
>>All my forms and combos were modified a long time ago to
>>get the minimum amount of data and the record source and
>>rowSources are created On Form Load. One thing I
believe
>>may be hapenning is that I am not clearing the record
>>source and rowSource on closing the form. The forms and
>>combos may still have the sources after closing.
>>
>>Now that you mention forms and combos, the form that is
>>the culprit has a subform of a datasheet form with 6
>>combos for each record. The user selects a value from A
>>to E which is stored as 1 to 5 in the table. The
combos'
>>rowSource Type are Value List and the RowSource is
>>A,5,B,4,C,3,D,2,E,1.
>>
>>Since the combos have a Value List, they shouldn't be
>>slowing down the data entry, should they?
>>
>>many thanks
>>george
>>
>>>-----Original Message-----
>>>Be sure to double check the LAN traffic patterns when
>>things
>>>are going south, especially look for errors and retry
>>rates.
>>>This typically requires special equipment and an
>>experienced
>>>person.
>>>
>>>Make sure that your combo/list boxes and bound
>>>forms/subforms only retrieve the minimum number of
records
>>>from the server.
>>>--
>>>Marsh
>>>MVP [MS Access]
>>>
>>>
>>>GeorgeMar wrote:
>>>
>>>>Thank you both for your reply.
>>>>
>>>>I have read Tony Toew's website many times in the
course
>>>>of developing this application. I went over it again
>>>>after your comments and found that the only two things
>>not
>>>>implemented are the use of BeginTrans and CommitTrans,
>>and
>>>>the use of a persistent recordset.
>>>>
>>>>Currently, my startup form is unbound but it remains
>>open
>>>>until the user exits. The article recommends a hidden
>>>>form, but since the startup form is always open, I may
>>>>base my form on a table linked to the Back end
database.
>>>>
>>>>That takes care of the opening and deleting of the
LDB.
>>>>In Tony's article under "Refreshing table links can
also
>>>>be quite slow" is he referring to a refreshing of
links
>>>>everytime the backend database is called or the
>>refreshing
>>>>when the application is deployed.
>>>>
>>>>Finally, is there anything else I may need to look at?
>>>>
>>>>your help is most appreciated
>>>>george
>>>>
>>>>
>>>>>-----Original Message-----
>>>>>GeorgeMar wrote:
>>>>>
>>>>>>I have an application with distributed front ends on
>>>>local
>>>>>>drives in Access 2002 and the backend sits in Win
2000
>>>>is
>>>>>>Acess 2000.
>>>>>>
>>>>>>Everything works well until more than 10 users are
>>doing
>>>>>>data entry. In the users words, the system goes
into
>>>>>>meltdown.
>>>>>>
>>>>>>Some users have also experienced data loss and have
>>had
>>>>to
>>>>>>rekey their data.
>>>>>>
>>>>>>I have followed all the guidelines regarding FE and
BE
>>>>as
>>>>>>well as guidelines for working in a multiuser
>>>>environment.
>>>>>>
>>>>>>Is the slowing down and the data loss due to to
Access
>>>>or
>>>>>>the LAN?
>>>>>>
>>>>>>What is the cutoff for Access ( no of users or
>>>>>>transactions) before you have to go to SQL server?
>>>>>
>>>>>
>>>>>The typical causes for this effect is less than
optimal
>>>>>design/implementation on your part, a network with
>>flakey
>>>>>hardware and/or an overloaded server. There are some
>>>>things
>>>>>to think about on Tony Toews' web site:
>>>>>
>>>>>http://www.granite.ab.ca/access/performancefaq.htm
>>>>>--
>>>>>Marsh
>>>>>MVP [MS Access]
>>>>>.
>>>>>
>>>
>>>.
>>>
>
>.
>