Db Corruption

[Guest]

Good afternoon,

I have a db which has about 30 users, probably 3-5 logged on simultaneously.
However, they typically are not working on the same records (would really be
exceptional). Lately there seems to be db corruption where entries are
replaced by #######? I know that they have an unstable network. Could this
be a cause? If so what settings could I change to try and improve this? I
don't know much about db settings, any good websites to recommend.

Thank you,

Daniel

 

[Arvin Meyer [MVP]]

The number 1 cause of corruption is power interruption during a database
write, or users turning off their machines, particularly if there is a memo
or ole field. The number 2 cause is a flakey network. Any file server
handing out large files must have a stable network or corrupt files will be
the result. Access is particularly sensitive because there are multiple
users connected to the same file.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Sylvain Lafontaine]

The fact that this occurs only since a relatively short period of time
("lately") is disturbing. If the flaky network was the source of the
corruption, you should have observed this phenomenon much much earlier. So
it's possible that something happened to the network recently or that you
did not followed the proper method of having a distributed database (ie.
having a splitted database into a frontend and a backend and each user
having his own private copy of the backend) or that some modifications have
been made to the backend and some frontends have been updated properly.

If you cannot solve this problem with the usual solutions, the only way to
stop them will be to use TS/Citrix or to replace the backend with MSDE or
SQL-Server. With one of these two, you should be pretty immune to network
problems.

 

[Joseph Meehan]

Good afternoon,

I have a db which has about 30 users, probably 3-5 logged on
simultaneously. However, they typically are not working on the same
records (would really be exceptional). Lately there seems to be db
corruption where entries are replaced by #######? I know that they
have an unstable network.

Weak networks are a major cause of such problems, but not the only
cause. #1 is likely someone turning off their computer when Access still
had the database open.

Is the database properly split with frontends on each user's PC and a
backend on a server?

 

[Guest]

The db is properly split (front-end and back-end) and each individual has
there own mde.

 

[Guest]

It just hit me, but they are currently in the process of making major changes
to their network (migrating from NT domain to active directory) could this be
a contributing factor in your opinion (I do not have any details on what has
been done to date)?

 

[Joseph Meehan]

It just hit me, but they are currently in the process of making major
changes to their network (migrating from NT domain to active
directory) could this be a contributing factor in your opinion (I do
not have any details on what has been done to date)?

I would say anytime someone is messing with the network settings, that
would be my first place to suspect.

Remember, back up early and often.

 

[Tony Toews]

I have a db which has about 30 users, probably 3-5 logged on simultaneously.
However, they typically are not working on the same records (would really be
exceptional). Lately there seems to be db corruption where entries are
replaced by #######? I know that they have an unstable network. Could this
be a cause? If so what settings could I change to try and improve this?

Not really. There are next to no software settings within Access that
affect corruption. One possibility is different Jet DLL versions on
different systems.

But what do you mean by unstable network?

any good websites to recommend.

Mine. For more information on corruption including possible causes,
determining the offending PC, retrieving your data, links, official MS
KB articles and a list of vendors who state they can fix corruption
see the Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm

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

 

[Tony Toews]

It just hit me, but they are currently in the process of making major changes
to their network (migrating from NT domain to active directory) could this be
a contributing factor in your opinion (I do not have any details on what has
been done to date)?

Absolutely. This probably means that they are changing hardware too.
And there could be a slightly flaky network card or new switch or some
other fancy device causing problems.

Trouble is proving it is another matter.

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

 

[Guest]

I this specific case I know that the network is slow... Would any of the
advanced option improve stability (I know they exist but do not know what
they actually do so I've never messed with them)?

OLE/DDE Timeout
Refresh interval
Number of update retries
ODBC refresh interval
Update retry interval

Thanks,

Daniel

 

[Tony Toews]

I this specific case I know that the network is slow...

Ouch. Especially if there is a real slowdown at intermittent
intervals. Access is the proverbial canary in a gold mine.

Would any of the
advanced option improve stability (I know they exist but do not know what
they actually do so I've never messed with them)?

OLE/DDE Timeout
ODBC refresh interval

The above no is they have nothing to do with a Jet database.

Refresh interval
Number of update retries
Update retry interval

These maybe. Kinda doubtful but worth experimenting I guess.

One thing to do would be to setup some logic behind the startup form
and a hidden form to log the entries and exits of PCs from the backend
MDB. Any for which you don't see exists means that they exited
abnormally. If only one PC shows up there then you can assume that
PC is the problem. If multiple PCs you might be able to figure out
if they are on one particular switch.

BTW is there a WAN of some sort involved? Multiple buildings? Or are
all the PCs n one building.

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

 

[Guest]

Yes there is a WAN invovled.... DNS server half way around the world, thus
the latent network.

There is also a number of user logging into the db from pc as well as a
number logging in through a citrix server.

Daniel

 

[Tony Toews]

Yes there is a WAN invovled.... DNS server half way around the world, thus
the latent network.

DNS server shouldn't be a big deal as once you've established a link
to a server once on your system that should be fine thereafter.

There is also a number of user logging into the db from pc as well as a
number logging in through a citrix server.

A client had a situation where one of the four Citrix servers which
ran the app in a load balancing system was causing corruptions. So
the client decided to run all the occurrences of my app on one Citrix
server and never a problem after that.

However where is the Access front end stored? On the Citrix server,
on the local server or on the users home PC which could be half way
around the world.

Also are all those PCs on the local LAN or some on a WAN. Besides
those who come in via Citrix.

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

 

[Guest]

The computers are on a local LAN which is part of a much larger WAN.

Citrix because many of the users are on UNIX machines.

The front and back-ends are stored on a local server as users on UNIX
machines are not allowed to utilize the HDs of their machines nor on the
citrix server or desktop (they can on use the designated server space). So
they actually have to run their front-ends from a local server... (not ideal,
I know ...)

Daniel

 

[Tony Toews]

The computers are on a local LAN which is part of a much larger WAN.

Citrix because many of the users are on UNIX machines.

The front and back-ends are stored on a local server as users on UNIX
machines are not allowed to utilize the HDs of their machines nor on the
citrix server or desktop (they can on use the designated server space). So
they actually have to run their front-ends from a local server... (not ideal,
I know ...)

No, that's not ideal but that same client of mine was doing exactly
the same thing with their Citrix system. The individual FEs resided
on the same share and server as did the backend.

Well, one thing to think about might be to move all the users to the
Citrix servers. Should there be any disconnects between the client
systems and the Citrix server that won't cause any corruptions.

If corruptions still happen then it's definitely the IT departments
problem.

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

 

[Guest]

I will see what can be done, however I know they have already maxed out their
licenses.

Thank you for your time and insight into this problem!

Daniel

 

[Tony Toews]

I will see what can be done, however I know they have already maxed out their
licenses.

Oh well, that was worth a shot.

Try the idea of recording the stations that caused the failure to help
document the problem I mentioned in a previous posting. This will
help by either pinpointing one particular system or many.

Thank you for your time and insight into this problem!

You're welcome. Best wishes

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

 

[Guest]

Tony,

I tried some of your other suggestions but nothing seems to have worked thus
far. I am interested in implementing what you suggested (see posting below)
but am unfamiliar with it. Could you explain some more or if you have a
sample code I could use it would be appreciated!


Thank you,

Daniel

 

[Tony Toews]

I tried some of your other suggestions but nothing seems to have worked thus
far. I am interested in implementing what you suggested (see posting below)
but am unfamiliar with it. Could you explain some more or if you have a
sample code I could use it would be appreciated!

You have an activity table with the computer name and network account.
You create a form which is opened on startup somehow but hidden.. In
it's OnOpen event you create a record in the activity table and record
the autonumber ID in a field in the form. Then in that forms On Close
event you update that record as closed. That even will only get
triggered when you close the database.

Thus any entries in the table which haven't been closed mean that
computer somehow disconnected abnormally.

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