Are we nuts? Multiple Users in Mission-Critical Access Database

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

Guest

We have a situation where the company that bought us is yanking our AS400
system, and shoehorning us into their Oracle system. However their system
will not hold all our developmental data. Because they do not want us to have
our Own SERVED database, we are looking at creating a multi-user Access
database. The database is fairly mission-crifical. The question is: Are we
nuts?

OK, to be more specific=
- This will be simply an Access database. No server, no VB front end. Just
an Access database sitting out on a hard drive of a FILE server (the FILE
server itself is maintained by our parent company and is out of our control).
- We might have 5 users creating records.
- We might have an additional 15 or so users that may open a record and edit
it, or simply view it.
- Because of our anticipated workflow, some here are thinking that only one
user would actually have a certain record open at one time.
- However, because the database will have related tables, more than one user
might be attempting to read data from/write data to a table and/or field that
is in use by another user.
- Users may be saving their changes while another user is creating/editing
in the database file as a whole.
- Would all users have to be kicked off the database before it could be
backed up? (At this point assume backups would be done via a manual, external
"Copy" of the file. Suggestions welcome.)
- Would all users have to be kicked off the database before a single user
could import data that would be written to multiple tables?

We are receiving strong encouragement to "go cheap" with this project. I
have little Access experience. However, since I have experience with SERVED
databases (SQL Server, Filemaker Pro) I am ..very afraid. So... are we nuts?
Or can we safely make this work?

Access 2003, "accessed" via Windows XP Professional on users machines.

Thanks.
 
wendyjcs said:
We have a situation where the company that bought us is yanking our
AS400 system, and shoehorning us into their Oracle system. However
their system will not hold all our developmental data. Because they
do not want us to have our Own SERVED database, we are looking at
creating a multi-user Access database. The database is fairly
mission-crifical. The question is: Are we nuts?

OK, to be more specific=
- This will be simply an Access database. No server, no VB front
end. Just an Access database sitting out on a hard drive of a FILE
server (the FILE server itself is maintained by our parent company
and is out of our control).
- We might have 5 users creating records.
- We might have an additional 15 or so users that may open a record
and edit it, or simply view it.
- Because of our anticipated workflow, some here are thinking that
only one user would actually have a certain record open at one time.
- However, because the database will have related tables, more than
one user might be attempting to read data from/write data to a table
and/or field that is in use by another user.
- Users may be saving their changes while another user is
creating/editing in the database file as a whole.
- Would all users have to be kicked off the database before it could
be backed up? (At this point assume backups would be done via a
manual, external "Copy" of the file. Suggestions welcome.)
- Would all users have to be kicked off the database before a single
user could import data that would be written to multiple tables?

We are receiving strong encouragement to "go cheap" with this
project. I have little Access experience. However, since I have
experience with SERVED databases (SQL Server, Filemaker Pro) I am
..very afraid. So... are we nuts? Or can we safely make this work?

Access 2003, "accessed" via Windows XP Professional on users machines.

Thanks.
Click to expand...

It would appear you can handle this in Access, assuming a healthy LAN.
However I would make the very important recommendations.

First split the database with each user having their own front end
linked to the data on the back end on the server.

Second make sure you have a very good backup plan. Things happen.

Make sure the data is properly normalized from the start.

Play with it a while and try to break it before going live. Have
everyone try to break it at the same time. You may find some soft spots.

I worked with mission critical government applications for an number of
years from version 97. (We got rid of our AS 400 some years ago.) I would
say there were maybe thee or for problems, most of which were mine, only one
was Access. Backup files saved me every time. We had a mirrored server, a
24 hour backup a 7 day backup and longer although I never needed any longer.
We even had it backed up to a salt mine out west.
 
Per wendyjcs:
We have a situation where the company that bought us is yanking our AS400
system, and shoehorning us into their Oracle system. However their system
will not hold all our developmental data. Because they do not want us to have
our Own SERVED database, we are looking at creating a multi-user Access
database. The database is fairly mission-crifical. The question is: Are we
nuts?

OK, to be more specific=
- This will be simply an Access database. No server, no VB front end. Just
an Access database sitting out on a hard drive of a FILE server (the FILE
server itself is maintained by our parent company and is out of our control).
- We might have 5 users creating records.
- We might have an additional 15 or so users that may open a record and edit
it, or simply view it.
- Because of our anticipat
Click to expand...

I don't think that's out of line as long as you have control over your LAN and
split up/deploy the application/back end properly.

I have a bond trading app that's nearing the end of it's life. A half-dozen
concurrent users - mostly updating, but with some heavy-duty batch reporting
towards the end of the day. I think it's been down once in about seven years -
when somebody yanked a cord in the LAN closet - and that time it was back up
within the hour. The replacement for it may be in production as I write
this... or will be very soon. .NET against an Oracle back end. About three
years development time and 23.5 million dollars last time I checked. (vs less
than 225k for my MS Access version).

I suspect that how you handle the screens can make or break the concurrent user
aspect. I use unbound forms (or, more correctly, forms that are bound to work
tables) so there's zero contention. Others will probably say that they use
bound forms with a half-dozen users and have no problems. I've just never done
it that way, so I cannot offer any experience.

You need control over the LAN because something like a flaky server or a bad NIC
can cause repeated corruptions of the back end - as in several times per day.
Been there....ugly scene when we *knew* that moving to a different server would
make the problem go away but IT wouldn't accommodate us. Instead they replaced
a $70k application by spending over three million on a web-centric server-based
attempted clone of the app that the user ultimately rejected.

Given that it's mission-critical, I'd fight hard to go half way: SQL Server for
the tables and treat it like a .MDB (i.e. ODBC to it and do all the work in the
MS Access front end). Even the lightweight version of SQL Server - whose name
I cannot recall at the moment...

Naturally, whatever you do, you'll have the front/back ends separate and a copy
of the front end on each user's PC.
 
My rule of thumb is that clients should be able be off-line
for an hour, and loss of data back to the last back-up.

Not that it will happpen daily, but it might happen every
couple of years.

If it is mission critical, you need the network people to
understand that it is mission critical. Sometimes these
people believe that all mission critical data should belong
to them, using the most complicated technology available,
and they may casually restore the file server from back
up, overwriting your data with a copy from last week.

(david)
 
wendyjcs said:
We have a situation where the company that bought us is yanking our AS400
system, and shoehorning us into their Oracle system. However their system
will not hold all our developmental data. Because they do not want us to have
our Own SERVED database, we are looking at creating a multi-user Access
database. The database is fairly mission-crifical. The question is: Are we
nuts?
Click to expand...

If you asked this question in an Oracle group, you'd get a different answer
than you will here. Fact is, no matter who you ask, you'll get a preference
for what they're good at. I can only tell you about my apps and let you
judge for yourself.

As the others have stated, you need to split the database and put only the
data on the server. The code, forms, reports, etc. goes on each workstation.
This is critical to success. Do not, under any condition let some network
admin insist on putting everything on the server.

I've only lost a few records in the past 6 years that I've been working with
my current set of apps. All of them were due to Windows XP bugs in a web
front-end interface. None caused by JET problems. I haven't had a corruption
since April of 2003, and that was due to a faulty Dell WiFi NIC. I have 51
concurrent users on a back-end database that runs about a 100 MB in size.
Eight connect through an asp front-end. Nine connect through Access
front-ends running on a Terminal Server, and 34 connect with Access
front-ends over the Lan.

There will come a time when you will eventually need to "upsize" the
back-end to a server database. You will never *need* to change your
front-end from the Access forms and reports.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
A big Thank You to all who replied. You have definitely helped us understand
what we need to do, and what factors are important should we choose Access.
 
Back
Top