Size limit/Time to upgrade?

[JHipp]

I have a data application which uses 5 access db's as the
back end. The combined data volume is 300MB. The
largest individual db is 130 MB. The system is licensed
for 15 concurrent users on a 25 WS UTP LAN. The users
have various versions of Win/Office2k and Win XP on their
desktops.

The system crashes (and the db's need to be repaired)
sometimes twice a week.

Is this because the Access db's are too big?

Are there too many users for Access to be comfy?

Could the problems be caused by using diparate WS OS's
and versions of Office?

Is it time to upgrade the back end to SQL server?

 

[david epsom dot com dot au]

Certainly there should not be a problem with 300MB of
data, 5 datafiles, and 15 users. But I can't guarantee
that you will easily be able to find the problem (I had
one consulting site where the user didn't notice that
the power went off 2 or 3 times a day). Apply all
service packs, turn off OPLOCKS, limit the number of
different versions of Office and Windows, and consider
your options.

Are there too many users for Access to be comfy?

Each user of Access (MSACCESS.EXE) is a single user.
Access uses a local database engine (MSJET40.dll)
to access the database. Each user of MSJET40.dll is
a single user. MSJET40.dll uses the network file
system to access the data. The network file system
can fail and corrupt data when pushed (there is really
no competition in the file-server area anymore). Are
there too may users for the file-server to be comfy?
No, it's not really the number of users as such, it's
just having two users try to use the same file at the
same time: MS network clients and servers have had
bugs in that area, and most people never notice.

Is this because the Access db's are too big?

No, it's just MS Windows sometimes has trouble when
two people try to use the same file.

Could the problems be caused by using disparate WS OS
and versions of Office?

Yes, or by bugs, or by power or network failures.

Is it time to upgrade the back end to SQL server?

That is one solution. It may be more work than you
are expecting. And remember that SQL server is a
security risk: SQL Server viruses have twice brought
the internet to it's knees. SQL Server is not really
a set-it and forget-it solution.

While you are investigating the SQL Server solution,
consider limiting your users to one version of Office
on one version of Windows (the same Workstation version
and Server version). Apply all service packs, and turn
off OPLOCKS.

(david)

 

[Kevin @ 3NF]

Does each user have his own copy of the front-end on his desktop?

 

[Tony Toews]

I have a data application which uses 5 access db's as the
back end. The combined data volume is 300MB. The
largest individual db is 130 MB. The system is licensed
for 15 concurrent users on a 25 WS UTP LAN. The users
have various versions of Win/Office2k and Win XP on their
desktops.

The system crashes (and the db's need to be repaired)
sometimes twice a week.

Is this because the Access db's are too big?
No.

Are there too many users for Access to be comfy?
No.

Could the problems be caused by using diparate WS OS's
and versions of Office?

Not in your situation. Win NT/2000/XP clients accessing Win 9x/ME
servers is definitely troublesome.

Now different versions of the Jet DLLs could be troubles.

What I've done is use the various API calls available and am checking
the version number and date/time of a crucial dll, msjetxx.dll, to
ensure it matches what I have on my system. See the tips page at my
website for more details including sample code: Verify Appropriate Jet
Service Pack is installed www.granite.ab.ca\access\verifyjetsp.htm

Is it time to upgrade the back end to SQL server?

Possible but not yet I'd say.

The first two things to do would be to review the Corruption Causes
and Determining the workstation which caused the corruption pages at
the Corruptions FAQ page at my website.

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]

limit the number of
different versions of Office and Windows,

He does state that he only has Office 2000 so that's likely not a
problem. As far as different versions of Office/Windows goes I'm
going to disagree with you.

A client has been happily running a mixture of NT4.0 clients and
Windows 2000 Terminal Server/Citrix against NT 4.0 and later Windows
2000 server along with half Access 97 and half Access 2000 for over a
year with very few problems.

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

 

[JHipp]

Yes I believe they do.

I can double check later this week, but for the time
being it is a fair assumption that they do.

Jonathan Hippisley