Leif said:
I know this is a difficult question to answer, since there are many factors
that come into play. Things such as amount of data, speed of
server/network/user systems, how well designed, complexity of application,
etc. come into play. However, as a rule of thumb, how do you determine if an
application is suitable to Access?
It's more a matter of when it's time to put the data on a database
server system such as SQL Server.
The biggest problems are in stability of the hardware and the number
of users making changes. Reporting or inquiry only users don't make a
difference. Editing/Updating users have been successfully in the ten
to fifteen range.
However your big concern is how mission critical is the data and can
the data be rekeyed if you lose a day.
Mission critical means can you afford to lose an hour if the database
is down? Frequently the data can't be rekeyed. A classic example
being a call centre where you are receiving incoming calls.
Losing a day means that if you have to restore from backup do your
users have the paperwork in place so they can rekey the data? Are
there enough staff to re-enter that data?
I recall a posting by someone working for a large casino/hotel
operation in the mid to late 80s stating to what lengths they spend
over a million dollars duplicating their IBM S/38 mini-computer in
another offside location with data inserts and updates being copied
from the main system to the backup system in under a second.
Thier attitude was that they could never afford to lose a room
reservation. Imagine the mess if they lost a days worth of phone
calls. <shudder> And the newspaper stories by the upset clients.
And expenses while they placate the customers so they don't go to the
newspapers. said:
I've seen that MS recommends at maximum of about 5 users at once for Access.
News to me. Is there a URL that states this? Some ones name? If
name we'll get the folks on the MS Access team to beat up on them.
I think this is well below a reasonable capability. I'm running a
department database that is about 60 MB with about 20 users at one time. It
is running smoothly. However, some are wondering when we might "hit the
wall".
What has been your experience, and if wrong what did you do? Convert to SQL
Server as a backend? If you converted was it difficult?
It can take a bit of work to upsize. Somewhere between a day and two
months depending on the complexity of the system and how well suited
it is to going onto SQL Server.
Also don't expect a performance improvement. Jet is surprisingly
efficient.
The consultants will, of course, tell you that you need to have a DBA
do all the work in setting things up properly in SQL Server. And you
should use a more robust development environment such as .Net, etc,
etc. Which will then take a team a lot longer to recreate than your
system.
See my Random Thoughts on SQL Server Upsizing from Microsoft Access
Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm
There is a new tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/sql/solutions/migration/default.mspx
However it only supports SQL Server 2005.
If you are using SQL Server 2000 then you could install SQL Server
2005 Express on your system and then run some SQL Server utilities to
create schema scripts which you could then execute on SQL Server 2000.
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's Microsoft Access Blog -
http://msmvps.com/blogs/access/