Disappearing Records

[Neil]

I am running an Access 2000 MDB against a SQL 7 back end, using ODBC linked
tables over a LAN and a WAN. The system has been operational for years with
relatively few problems.

Recently, WAN users have been reporting several hundred records disappearing
at a time. The records are all sequential, and they're in a table that
contains about 60,000 records. This all started last week at about the same
time (not sure if before or after) that the WAN went down for a couple of
hours for an unknown reason. Since then, every once in a while, a WAN user
will report that several hundred records in a block are just "missing."
Then, an hour or two later, they reappear.

Any ideas as to what's going on or what can be done to rectify this?

Thanks!

Neil

 

[Stefan Hoffmann]

hi Neil,

Any ideas as to what's going on or what can be done to rectify this?

Bad WLAN performance, but the network stack is not aware of it. So your
session thinks it is okay, but it's not.


mfG
--> stefan <--

 

[Neil]

Any ideas on what can be done to rectify it?

hi Neil,

Bad WLAN performance, but the network stack is not aware of it. So your
session thinks it is okay, but it's not.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Neil,

Any ideas on what can be done to rectify it?

Try using a permanent open recordset.
Use filterted recordsets.
Use serverside filters (views).


mfG
--> stefan <--

 

[Neil]

OK, thanks. I guess I was thinking that, since this system has been in place
for years without these problems; and since these problems just started last
week when the WAN went down for a few hours; that perhaps there was
something on the network end that can be done to rectify it. This has never
been a problem before, so something must have happened to cause it. The
database hasn't changed very much in years.

Thanks,

Neil

 

[Larry Linson]

OK, thanks. I guess I was thinking that, since this system has been in
place for years without these problems; and since these problems just
started last week when the WAN went down for a few hours; that perhaps
there was something on the network end that can be done to rectify it.
This has never been a problem before, so something must have happened to
cause it. The database hasn't changed very much in years.

It is surprising how often only one record is needed for a particular
business function, if it exists, or none, if it does not. Limiting the
number of records retrieved by Query Criteria is a very good way to speed up
client-server performance, and you'll never "lose" several hundred records
on a one-record retrieval. A client-server application which retrieves
hundreds or thousands of records, or more, and then "finds" the one of
interest is not very efficient and effective.

That said, the "fix" is to correct the LAN/WAN problems. I once observed a
company who got a really noticeable improvement when they replaced about 95%
of their network support staff. { or :-(, depending on whether you were
part of the new or the old staff} That said, because a LAN is totally
within control of the network support team, it's much easier to fix than a
WAN, which relies on external providers for some of its services.

Larry Linson
Microsoft Access MVP

 

[Neil]

Larry,

I agree with what you wrote 100%. The application was inherited by me after
it was converted to an Access back end from an off-the-shelf product.
There's much that needs to be improved with it. One of the major changes
that we are looking to make is to change the way it works with records in
the way you describe. Bringing over tens of thousands of records is
ridiculous. I was considering giving the user options to work with small,
pre-defined sets of records based on business needs, with the additional
option of a custom set based on search criteria (with the sets being
compiled in the back end, of course, and then brought over). But your note
has made me rethink this. In what situations would the user need anything
*but* a custom set? So I'm rethinking that paradigm, and may just have the
user pull over whatever set of records they need. So thanks for that.

Getting back to the network situation, the problem only seems to manifest
itself in the WAN. The LAN users aren't experiencing this problem. And, as
noted, it only started about a week ago (after many years of the database
and WAN being up and running without this problem) and on the same day that
the WAN went down for several hours. So this tells me that SOMETHING
happened on that day that hasn't yet been rectified. But I know what the
network guy's going to say: everything's working fine now; he doesn't see
any problem with anything. And round and round we go......

Thanks,

Neil

 

[Ed Murphy]

Getting back to the network situation, the problem only seems to manifest
itself in the WAN. The LAN users aren't experiencing this problem. And, as
noted, it only started about a week ago (after many years of the database
and WAN being up and running without this problem) and on the same day that
the WAN went down for several hours. So this tells me that SOMETHING
happened on that day that hasn't yet been rectified. But I know what the
network guy's going to say: everything's working fine now; he doesn't see
any problem with anything. And round and round we go......

Is he denying that the WAN outage is responsible for the problem, or is
he just claiming that the WAN outage was an isolated incident and he
doesn't know what caused it and thus he doesn't know how to prevent
future occurrences?

In the former case, you could test the issue by deliberately cutting a
workstation's WAN connection and seeing whether the problem recurs.

 

[Neil]

As for what he believes, here's what he wrote:

"It sounds as if it is using cached information and not updating from the
database directly. We are having intermittent issues with the T1s,
but that has been going on for the last few months. Last week the T1s went
down for over two hours this issue was brought to my attention
right after that incident so I am not sure if it is related.

"When I get in this afternoon I'll start sniffing around on the network and
see if there is anything going on at the Network layer.
Later tonight I will reset both the routers and firewalls on both end as
well to see if that helps. I will let them run over the weekend to see what
kind of data we can collect on errors, interface resets etc."

Note that he says the issue was brought to his attention right after the T1s
went down; yet he still isn't sure if the two are related!

Re. testing for the problem, it's hard to do because the problem is very
intermittent. Also, the WAN computers get their data from the remote
location.

Thanks,

Neil