RE: Access limitations/suitablity

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.
--
Dave Hargis, Microsoft Access MVP


"Leif" wrote:

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

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Leif" wrote:

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
.

Relevant Pages

  • Re: DAO-ADO question
    ... a basic "upsizing" simply leaves you with linked ... >my mdb front end to a SQL Server, or do I have to make my code ADO-compliant ... John Nurick [Microsoft Access MVP] ...
    (microsoft.public.access.conversion)
  • RE: Problem with query causing Access to go to white screen
    ... Access not to refresh/repaint and go white? ... Dave Hargis, Microsoft Access MVP ... When you say "copy the Access DB to the SQL Server I am running the query off" ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access 2002 Upsizing Wizard does not copy forms
    ... Upsizing *.mdb to SQL Server, using Upsizing Wizard, ONLY transfer data from ... Jet database to a SQL Server database (either new or existing, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL Tables
    ... Microsoft Access MVP ... "Graham R Seach" wrote: ... albeit transferred to SQL Server. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: getting into access, can you point me in the right direction?
    ... Your ignorance is exceeded only by your offensive attitude. ... I have no need for training wheels, but you really need to get some ... using SQL Server as the data source is a good way to ... Dave Hargis, Microsoft Access MVP ...
    (microsoft.public.access.gettingstarted)