Data Loss in Access 2000

  • Thread starter Thread starter GeorgeMar
  • Start date Start date
G

GeorgeMar

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 said:
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?
Click to expand...


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

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

had
to
Click to expand...

.
Click to expand...
 
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]


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]

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
Click to expand...

.
Click to expand...
Click to expand...
 
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]


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


.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top