Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access Database capacity ... need help

Discussion in 'Information Technology' started by Acustsvcrep, Jul 28, 2006.

  1. Acustsvcrep

    Acustsvcrep
    Expand Collapse
    Guest

    Please help? I was wondering is there a limit to how many users should be in
    an Access 2003 Database?

    We have an Access database that keeps track of different things and we have
    about 25 people accessing it all day. This causes the DB to freeze alot of
    times and to corrupt some of the data.
    I wanted to suggest using SQL Server instead of Access for the functions we
    are trying to accomplish.

    Please advise.
    Thanks.
     
  2. Loading...


  3. Immanuel Sibero

    Immanuel Sibero
    Expand Collapse
    Guest

    Hi Acustsvc,

    > Please help? I was wondering is there a limit to how many users should be

    in
    > an Access 2003 Database?


    Theoretical limit is 255 concurrent users, but 20 is probably the practical
    limit. So depending on what your app does, your 25 users could be pushing
    that limit.
    Make sure you split your app into Front End and Back End so that each of
    your 25 users has his/her own Front End.

    > I wanted to suggest using SQL Server instead of Access for the functions

    we
    > are trying to accomplish.


    Wise suggestion.


    HTH,
    Immanuel Sibero




    "Acustsvcrep" <Acustsvcrep@discussions.microsoft.com> wrote in message
    news:515EFB87-2C64-48B5-8298-06947AC5E044@microsoft.com...
    > Please help? I was wondering is there a limit to how many users should be

    in
    > an Access 2003 Database?
    >
    > We have an Access database that keeps track of different things and we

    have
    > about 25 people accessing it all day. This causes the DB to freeze alot

    of
    > times and to corrupt some of the data.
    > I wanted to suggest using SQL Server instead of Access for the functions

    we
    > are trying to accomplish.
    >
    > Please advise.
    > Thanks.
    >
    >
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Acustsvcrep wrote:
    > Please help? I was wondering is there a limit to how many users
    > should be in an Access 2003 Database?
    >
    > We have an Access database that keeps track of different things and
    > we have about 25 people accessing it all day. This causes the DB to
    > freeze alot of times and to corrupt some of the data.
    > I wanted to suggest using SQL Server instead of Access for the
    > functions we are trying to accomplish.
    >
    > Please advise.
    > Thanks.


    Are you using a split system currently or are the users directly
    accessing the database on the LAN?

    --
    Joseph Meehan

    Dia duit
     
  5. David

    David
    Expand Collapse
    Guest

    Without seeing the design it is hard to determine a good resolution.
    Sometimes adding indexes on frequently used columns helps response.

    My experience has been to split the mdb's into front/back end helps a lot.
    Also, we put the front end on the users workstation and "connect" to the
    back end on the server. We have a utility (VB6 exe) that updates the
    workstation when a new "version" is placed on the network.

    hth.
    David

    "Acustsvcrep" <Acustsvcrep@discussions.microsoft.com> wrote in message
    news:515EFB87-2C64-48B5-8298-06947AC5E044@microsoft.com...
    > Please help? I was wondering is there a limit to how many users should be
    > in
    > an Access 2003 Database?
    >
    > We have an Access database that keeps track of different things and we
    > have
    > about 25 people accessing it all day. This causes the DB to freeze alot
    > of
    > times and to corrupt some of the data.
    > I wanted to suggest using SQL Server instead of Access for the functions
    > we
    > are trying to accomplish.
    >
    > Please advise.
    > Thanks.
    >
    >
     
  6. Acustsvcrep

    Acustsvcrep
    Expand Collapse
    Guest

    Hi Immanuel,

    Thanks so much for the info.

    Can you explain what you mean by front end and back end. The way we have it
    set up is the Access DB file is on a drive and each user puts a link to that
    file on their desktop. Is that what you mean?

    Does Microsoft have documentation with the limitations of Access so I can
    have support when I present this to my manager?

    Thanks
    Acustsvcrep

    "Immanuel Sibero" wrote:

    > Hi Acustsvc,
    >
    > > Please help? I was wondering is there a limit to how many users should be

    > in
    > > an Access 2003 Database?

    >
    > Theoretical limit is 255 concurrent users, but 20 is probably the practical
    > limit. So depending on what your app does, your 25 users could be pushing
    > that limit.
    > Make sure you split your app into Front End and Back End so that each of
    > your 25 users has his/her own Front End.
    >
    > > I wanted to suggest using SQL Server instead of Access for the functions

    > we
    > > are trying to accomplish.

    >
    > Wise suggestion.
    >
    >
    > HTH,
    > Immanuel Sibero
    >
    >
    >
    >
    > "Acustsvcrep" <Acustsvcrep@discussions.microsoft.com> wrote in message
    > news:515EFB87-2C64-48B5-8298-06947AC5E044@microsoft.com...
    > > Please help? I was wondering is there a limit to how many users should be

    > in
    > > an Access 2003 Database?
    > >
    > > We have an Access database that keeps track of different things and we

    > have
    > > about 25 people accessing it all day. This causes the DB to freeze alot

    > of
    > > times and to corrupt some of the data.
    > > I wanted to suggest using SQL Server instead of Access for the functions

    > we
    > > are trying to accomplish.
    > >
    > > Please advise.
    > > Thanks.
    > >
    > >

    >
    >
    >
     
  7. Immanuel Sibero

    Immanuel Sibero
    Expand Collapse
    Guest

    Hi Acustsvcrep,

    > Can you explain what you mean by front end and back end. The way we have

    it
    > set up is the Access DB file is on a drive and each user puts a link to

    that
    > file on their desktop. Is that what you mean?


    No. If I understand you correctly you are using the unsplit design (i.e.
    just one mdb and all users run it).
    A split design is splitting that one mdb into two mdb files. One mdb
    (commonly referred to as the Front End) contains all user interface items -
    everything but tables, and another mdb (commonly referred to as the Back
    End) contains tables only. You would have to link to the tables in the Back
    End from the Front End. Each user would have a separate copy of the Front
    End preferrably located on their local drive.

    For more information (and I highly suggest browsing this site for other
    information also):
    http://www.granite.ab.ca/access/splitapp/index.htm

    > Does Microsoft have documentation with the limitations of Access so I can
    > have support when I present this to my manager?


    Yes, type in "Specifications" in Access Help. You would find hard limits for
    various things, such as max number of tables, fields, queries, etc. But note
    that these are, again, hard (theoretical) limits. Depending on how well your
    application is designed, you may well run into problems long before you
    reach those theoretical limits.

    HTH,
    Immanuel Sibero



    "Acustsvcrep" <Acustsvcrep@discussions.microsoft.com> wrote in message
    news:029AA53E-8F49-4364-96D8-22ACA2EB19DE@microsoft.com...
    > Hi Immanuel,
    >
    > Thanks so much for the info.
    >
    > Can you explain what you mean by front end and back end. The way we have

    it
    > set up is the Access DB file is on a drive and each user puts a link to

    that
    > file on their desktop. Is that what you mean?
    >
    > Does Microsoft have documentation with the limitations of Access so I can
    > have support when I present this to my manager?
    >
    > Thanks
    > Acustsvcrep
    >
    > "Immanuel Sibero" wrote:
    >
    > > Hi Acustsvc,
    > >
    > > > Please help? I was wondering is there a limit to how many users

    should be
    > > in
    > > > an Access 2003 Database?

    > >
    > > Theoretical limit is 255 concurrent users, but 20 is probably the

    practical
    > > limit. So depending on what your app does, your 25 users could be

    pushing
    > > that limit.
    > > Make sure you split your app into Front End and Back End so that each of
    > > your 25 users has his/her own Front End.
    > >
    > > > I wanted to suggest using SQL Server instead of Access for the

    functions
    > > we
    > > > are trying to accomplish.

    > >
    > > Wise suggestion.
    > >
    > >
    > > HTH,
    > > Immanuel Sibero
    > >
    > >
    > >
    > >
    > > "Acustsvcrep" <Acustsvcrep@discussions.microsoft.com> wrote in message
    > > news:515EFB87-2C64-48B5-8298-06947AC5E044@microsoft.com...
    > > > Please help? I was wondering is there a limit to how many users

    should be
    > > in
    > > > an Access 2003 Database?
    > > >
    > > > We have an Access database that keeps track of different things and we

    > > have
    > > > about 25 people accessing it all day. This causes the DB to freeze

    alot
    > > of
    > > > times and to corrupt some of the data.
    > > > I wanted to suggest using SQL Server instead of Access for the

    functions
    > > we
    > > > are trying to accomplish.
    > > >
    > > > Please advise.
    > > > Thanks.
    > > >
    > > >

    > >
    > >
    > >
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 14 Jun 2006 12:33:02 -0700, Acustsvcrep
    <Acustsvcrep@discussions.microsoft.com> wrote:

    >Can you explain what you mean by front end and back end. The way we have it
    >set up is the Access DB file is on a drive and each user puts a link to that
    >file on their desktop. Is that what you mean?


    No. See

    http://www.granite.ab.ca/access/splitapp/index.htm

    for a thorough discussion and instructions. I fully concur with the
    other respondants - for this kind of app I'd say you MUST split to
    avoid bloat, bad performance, and database corruption!

    >Does Microsoft have documentation with the limitations of Access so I can
    >have support when I present this to my manager?


    See the online help for the topic "Specifications".

    John W. Vinson[MVP]
     
  9. Acustsvcrep

    Acustsvcrep
    Expand Collapse
    Guest

    Hi Joseph,

    They are accessing directly on the LAN. We are not using a split system as
    of now.

    Acustsvcrep

    "Joseph Meehan" wrote:

    > Acustsvcrep wrote:
    > > Please help? I was wondering is there a limit to how many users
    > > should be in an Access 2003 Database?
    > >
    > > We have an Access database that keeps track of different things and
    > > we have about 25 people accessing it all day. This causes the DB to
    > > freeze alot of times and to corrupt some of the data.
    > > I wanted to suggest using SQL Server instead of Access for the
    > > functions we are trying to accomplish.
    > >
    > > Please advise.
    > > Thanks.

    >
    > Are you using a split system currently or are the users directly
    > accessing the database on the LAN?
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  10. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    Access Data Projects with SQL Server 2005 Express Edition would handle
    this many concurrent connections without a problem


    MDB is crap.

    nobody should use it ever for anything.

    not for a single record and a single user.




    David wrote:
    > Without seeing the design it is hard to determine a good resolution.
    > Sometimes adding indexes on frequently used columns helps response.
    >
    > My experience has been to split the mdb's into front/back end helps a lot.
    > Also, we put the front end on the users workstation and "connect" to the
    > back end on the server. We have a utility (VB6 exe) that updates the
    > workstation when a new "version" is placed on the network.
    >
    > hth.
    > David
    >
    > "Acustsvcrep" <Acustsvcrep@discussions.microsoft.com> wrote in message
    > news:515EFB87-2C64-48B5-8298-06947AC5E044@microsoft.com...
    > > Please help? I was wondering is there a limit to how many users should be
    > > in
    > > an Access 2003 Database?
    > >
    > > We have an Access database that keeps track of different things and we
    > > have
    > > about 25 people accessing it all day. This causes the DB to freeze alot
    > > of
    > > times and to corrupt some of the data.
    > > I wanted to suggest using SQL Server instead of Access for the functions
    > > we
    > > are trying to accomplish.
    > >
    > > Please advise.
    > > Thanks.
    > >
    > >
     
  11. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Acustsvcrep wrote:
    > Hi Joseph,
    >
    > They are accessing directly on the LAN. We are not using a split
    > system as of now.
    >
    > Acustsvcrep
    >
    > "Joseph Meehan" wrote:
    >


    Splitting it might solve all your problems. Really.

    Access is very demanding of the LAN connection, splitting it greatly
    reduces the traffic on the LAN. Delays and corruption are the usual results
    of not splitting.

    --
    Joseph Meehan

    Dia duit
     
  12. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    dbahooker@hotmail.com wrote:
    > Access Data Projects with SQL Server 2005 Express Edition would handle
    > this many concurrent connections without a problem
    >


    As done Access when it is implemented properly in a split system. If
    you really knew anything about Access you would know that.

    >
    > MDB is crap.


    Well if you don't know how to use it, then I would expect you to feel
    that way.
    ....
    --
    Joseph Meehan

    Dia duit
     
  13. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "David" <dlchase@lifetimeinc.com> wrote:

    >My experience has been to split the mdb's into front/back end helps a lot.
    >Also, we put the front end on the users workstation and "connect" to the
    >back end on the server. We have a utility (VB6 exe) that updates the
    >workstation when a new "version" is placed on the network.


    Your utility sounds similar to my Auto FE Updater utility.
    http://www.granite.ab.ca/access/autofe.htm

    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
     
  14. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    oh Tony.

    if you didn't have to sync queries and tables woudln't your job be
    easier?

    instead of re-updating a 40mb mdb frontend; you could jsut throw away
    the frontend and make a new 1mb copy.

    are you really claiming that your solution is faster than mine?

    I just find it funny; one of these days you guys will be like 'im so
    tired of all these workarounds' and then you can start building real
    applications without having to deal with all the legwork

    -Aaron


    Tony Toews wrote:
    > "David" <dlchase@lifetimeinc.com> wrote:
    >
    > >My experience has been to split the mdb's into front/back end helps a lot.
    > >Also, we put the front end on the users workstation and "connect" to the
    > >back end on the server. We have a utility (VB6 exe) that updates the
    > >workstation when a new "version" is placed on the network.

    >
    > Your utility sounds similar to my Auto FE Updater utility.
    > http://www.granite.ab.ca/access/autofe.htm
    >
    > 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
     
  15. Tony Toews

    Tony Toews
    Expand Collapse
    Guest


    >if you didn't have to sync queries and tables woudln't your job be
    >easier?


    What do you mean by syncing tables?

    >instead of re-updating a 40mb mdb frontend; you could jsut throw away
    >the frontend and make a new 1mb copy.


    Queries take very little room compared to forms and reports.

    >are you really claiming that your solution is faster than mine?


    I never said anything to that effect.

    >I just find it funny; one of these days you guys will be like 'im so
    >tired of all these workarounds' and then you can start building real
    >applications without having to deal with all the legwork


    <shrug>

    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
     

Share This Page