Access limitations/suitablity

L

Leif

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?

I've seen that MS recommends at maximum of about 5 users at once for Access.
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?

Regards,
Leif
 
K

Klatuu

If your application is running smoothly and user response time is acceptable,
the only walls would be if your back end databaes starts to get to the 2Gb
maximum size or if you add additional users and response begins to suffer.

Converting the SQL is not too hard, but not a breeze, either. The upsizing
wizard give you a start, but I have found you have to do some clean up work
afterwards.
 
L

Leif

Klatuu,

Thanks for your reply. The other hard limit I'm aware of is 255 (or 256)
simultaneous users.

Your advice applies for existing applications. But what about a new
application where you "think" it "might" work in Access, and you expect it to
be well short of 2 GB and 255 users? Anyone have experience where due to
performance they had to convert to something like SQL Server? If so, what
were the issues? Size? Users?

Regards,
Leif
 
T

Tony Toews [MVP]

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/
 
K

Klatuu

Sorry for my delayed response. I have been out of town.
I think Tony has some great thoughts on upsizing as to when and how. In my
experience, an important consideration is your indexing structure. We
recently did a conversion on an application and one query that does a mass
delete actually become much slower in SQL Server 2005. After investigating
the problem, we found a large multi field, multi data type primary key was
causing the delay because it has to rebuild the indexes after every delete.
We deleted the index and changed to an autonumber primary key and it went
from over 20 minutes to a few seconds.
 
L

Leif

Thanks Tony for your insights.

I don't recall where I read the 5 user "suggestion". As I recall it was not
represented as a hard limit, but rather a recommendation as a dividing point
between products like Access and SQL server. I did a quick google and could
not find it... In any case, with faster servers and networks that number is
certainly out-of-date.

I don't really have a good feel for the balance between updaters versus
query, even for existing databases. Everyone does both. But we do run 20
users with no slow down.

It would be bad, but not a disaster if you lost a day's worth of data. The
application has been very solid, and we back up nightly, so it has not been a
concern.

Regards,
Leif
 
T

Tony Toews [MVP]

Klatuu said:
Sorry for my delayed response. I have been out of town.
I think Tony has some great thoughts on upsizing as to when and how. In my
experience, an important consideration is your indexing structure. We
recently did a conversion on an application and one query that does a mass
delete actually become much slower in SQL Server 2005. After investigating
the problem, we found a large multi field, multi data type primary key was
causing the delay because it has to rebuild the indexes after every delete.
We deleted the index and changed to an autonumber primary key and it went
from over 20 minutes to a few seconds.

<chuckle> There's a huge thread cross posted to a number of
newsgroups, including comp.databases.theory where folks from that
newsgroup are heckling me for using autonumber primary keys.

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/
 
L

Leif

Dave,

Thanks for your input. I try to do a good design, which includes using a
autonumber field on all tables which require a PK (which is almost all). I
also try to index all foreign keys. I try to normalize as much as I can and
in general I've not found I needed to back off that for performance. I'm
amazed at how well Access can handle a 7 or 8 table/query join.

Regards,
Leif
 
K

Klatuu

I use Autonumber primary keys. Regardless of what some think, I find them
useful. For example, this problem.
 
F

Fred Boer

Hey Tony!

Is that the "Separate PK in Jxn Tbl?" thread? It goes on forever! I haven't
bothered reading it (since I have a life), but it must be a record!
 
A

Armen Stein

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.

It's true, there's definitely a range of effort and it depends very
much on how well-structured and standardized the application is, and
how much performance must be wrung out by moving the "heavy lifting"
to SQL Server. I have a PowerPoint slide deck on this, called Best of
Both Worlds, at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp.
Also don't expect a performance improvement. Jet is surprisingly
efficient.

Tony, it really depends on the requirements. We have Access
client-server apps that would not perform acceptably with a Jet
back-end, period. SQL Server can handle some activities, especially
complex aggregate calculations, much faster than Jet. But I agree
that if an Access-Jet application is performing adequately now, the
move to SQL Server will not result in a dramatic improvement.
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.

We're consultants, and we do use ASP.NET and SQL Server for web
applications when they're required. But for rapid development in a
business environment, we use Access as an front-end to SQL Server all
the time. It's a great combination.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
T

Tony Toews [MVP]

Fred Boer said:
Hey Tony!

Is that the "Separate PK in Jxn Tbl?" thread? It goes on forever! I haven't
bothered reading it (since I have a life), but it must be a record!

Yuppers. It's been a hoot. I've been plonked and called a
gentleman. By two different people.

And yes I'm still right. <smile>

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/
 
M

mcnews

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?

I've seen that MS recommends at maximum of about 5 users at once for Access.
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?

Regards,
Leif

the biggest problem i've run into is unraveling nested queries.
i inherited several systems that were created by people who were not
trained or knew anything about IT.
so things were built as needed the best the person could figure out at
the time. some of them have some parts that were hired out. hodge-
podge.
embedded functions can slow you down a bit too.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top