Access file damage from premature program termination

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Our company is using Terminal Server to run an Access program but it appears
Access is having corruption problems when Terminal Server "times out" and
closes down the entire session.

I would like to document this for my company explaining to them that the
"time out" feature in TS is actually causing corruption to the Access
environment and, subsequently, the underlying SQL Server database.

Can anyone help me find details to this problem? Thanks so much in advance.
 
If you want "straight-from-the-horse's mouth" explanation, you might want to
point to Microsoft's Knowledge Base article:

How to keep a Jet 4.0 database in top working condition in Access 2000
http://support.microsoft.com/kb/300216/en-us

You might want to dig around if you're using a different version of Access,
and they want on specific for your version. There's a KB article on this
topic for each version of Access.

There isn't anything specifically about terminal servers in these articles,
but they do emphasize the importance of a consistantly reliable connection to
the server that won't have unexpected disconnections such as you describe.

In particular, you'll want to draw to their attention the paragraph headed
"ADDITIONAL BEST PRACTICES FOR NETWORK ENVIRONMENTS", which states:

"Microsoft Jet is a file-sharing database system. A file-sharing database is
one in which all the processing of the file takes place at the client. When a
file-sharing database, such as Microsoft Jet, is used in a multiuser
environment, multiple client processes are using file read, write, and
locking operations on the same shared file across a network. If, for any
reason, a process cannot be completed, the file can be left in an incomplete
or a corrupted state. Two examples of when a process may not be completed is
when a client is terminated unexpectedly or when a network connection to a
server is dropped."

Hope that helps!

David
 
In particular, you'll want to draw to their attention the paragraph headed
"ADDITIONAL BEST PRACTICES FOR NETWORK ENVIRONMENTS", which states:

"Microsoft Jet is a file-sharing database system. A file-sharing database is
one in which all the processing of the file takes place at the client. When a
file-sharing database, such as Microsoft Jet, is used in a multiuser
Click to expand...

But he said that the back-end database is SQL Server. Are you saying
that your SQL Server database is actually corrupting? That's not easy
to do.

Are each of your TS users using a separate copy of the front-end Access
application? They should be.

You may also want to check that you aren't opening forms with large
updatable recordsets. A good rule is "read many, update one". We use
read-only passthrough queries for continuous forms to allow the user to
select just the record they want to edit. Then we open a bound form on
that one record only.
 
You're correct. As difficult as it is to believe, two of us have confirmed
that SQL records are being corrupted, probably because someone was in the
middle of editing a SQL record and lost connectivity. Neither of us here
believed it, either, but a week after we repaired the particular table
(recreated and populated it), it was corrupt again - not a common thing.

I think we're probably on the same thought here - holding a record open in a
connected (versus disconnected) environment exposes it to some pretty ugly
side effects. Right now we're guessing it's that held-open connection that is
damaging the record in SQL (of course, we have no direct evidence because
users are very vague describing the problem).

Thanks for your help!
 
Jerry V. said:
You're correct. As difficult as it is to believe, two of us have confirmed
that SQL records are being corrupted, probably because someone was in the
middle of editing a SQL record and lost connectivity. Neither of us here
believed it, either, but a week after we repaired the particular table
(recreated and populated it), it was corrupt again - not a common thing.

I think we're probably on the same thought here - holding a record open in a
connected (versus disconnected) environment exposes it to some pretty ugly
side effects. Right now we're guessing it's that held-open connection that is
damaging the record in SQL (of course, we have no direct evidence because
users are very vague describing the problem).
Click to expand...

This should *not* be happening. I've asked my fellow Access and SQL
Server MVPs to take a look at this problem. We'll post back if we
have any suggestions or comments.

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
 
Jerry V. said:
You're correct. As difficult as it is to believe, two of us have confirmed
that SQL records are being corrupted, probably because someone was in the
middle of editing a SQL record and lost connectivity. Neither of us here
believed it, either, but a week after we repaired the particular table
(recreated and populated it), it was corrupt again - not a common thing.

I think we're probably on the same thought here - holding a record open in a
connected (versus disconnected) environment exposes it to some pretty ugly
side effects. Right now we're guessing it's that held-open connection that is
damaging the record in SQL (of course, we have no direct evidence because
users are very vague describing the problem).
Click to expand...

Here's a query from a SQL Server MVP.

Is this "SQL record" a row in an Access database? That is at least how
I interpret the post. Or do you imply that it is a row in an SQL
Server database? In such case, exactly how this "corruption" express
itself? Are there DBCC errors, or just plain garbage in application
data?

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
 
If you like feel free to post here and email me directly with your
replies. I will then copy it directly in the MVP newsgroup. I'm
sure the SQL Server MVPs won't be reading this newsgroup on a regular
basis. <smile>

Tony tony at granite dot ab dot ca.
--
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
 
There is no explicit mention of SQL-Server as the back-end database accessed
through an ODBC linked table or a sql passthrough query. The mention « the
underlying SQL Server database » used by a newbie could apply as well to Jet
as the backend database as to SQL-Server.

This kind of corruption is a classical issue and easy to reproduce with
JET/Access but extremely rare and difficult to reproduce with SQL-Server, so
until there is a clear confirmation that this problem is really related to
an installation of SQL-Server and not to a MDB file used as the backend; I
would tend to believe that the real backend database used here is a MDB file
and not an installation of SQL-Server.

Of course, I might be totally wrong; so the OP should make a clear statement
about the nature of the backend database that they are using.
 
Back
Top