Results 1 to 8 of 8
  1. #1
    raffie77 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    21

    Speedup access 2003

    Hello people,



    I'm making a access database for the company that I work for.
    and it wil be used on a few hundred computers.
    I'm affraid that Access can't handle the speed and it wil become slow.

    is it a good idea to put the data on a SQL server and link the access database to SQL and use the forms and reports from access to display the linked records?

    does that make the system faster?


    Ralph

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Using SQL Server as the backend in itself does not necessarily make the system faster. So, the big question is how much data you are needing to store, if the database will be split (which it should) and will each user have a copy of the frontend on their machine (which it needs to be).

    And when you say, "I'm affraid (sic) that Access can't handle the speed and it will become slow" what exactly do you mean? What speed is it needing? What type of data are you storing? What type of interfacing do the users need? What are they doing in this database?

  3. #3
    raffie77 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    21
    hi boblarson

    let me give some more details

    it's not a split database
    the users are working in the database adding records, its a database for a call center to make appointments for calling back people.
    when I open a callback form the speed or performance is ok, not fast but fair enough, right now there are I think 50 people working on it, not all the time, but at the end there will be I think about 300 users. if the speed or performance is ok now for these few people how will it be when 300 users are going to use it.

    and isn't SQL not a better database technique than access?

    and what's the benefit of splitting a database?


    Ralph

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    First off, I'm surprised that your database hasn't corrupted yet. It MUST be split for multi-user use in order to avoid corruption and to help with speed issues as well as being able to make modifications to the frontends.

    Also, if the database is not split, the limit is officially 255 users but practically it is maybe 10 users. Also, if the database corrupts, you would be at risk of losing ALL of your data AND ALL USERS would be unable to do their work. If you have a split database, the backend holds just the data and is less likely to corrupt. The frontend, can still corrupt but it would likely be for only one user and so everyone else could just continue on without a problem. You would then give the user with the corrupt frontend a new copy and they would go on with work. Not splitting is like playing Russian Roulette with your whole database. If someone is in there and a network disruption occurs for ONE of the connected users, that can corrupt the entire thing.

    So, I hope you have FREQUENT backups because if it goes corrupt then you would need to restore a backup and then RE-ENTER all of the data after that backup point.

    And SQL Server as a backend is a good way to ensure that you have less likelihood that you would lose data. But you still need to have SEPARATE COPIES of the frontend for each user.

  5. #5
    raffie77 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    21
    ok I'll give it a go.
    I'll split the database using the wizard in access

    so it's tables only?
    isn't it faster to put the querys and relations in the backend?
    I can imagine that the calculations of relations en query's, that it's faster to do in the backend then over the network

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Relationships will be defined in the backend. The tables go in the backend the queries should be in the frontend.

    And you wrote:
    I can imagine that the calculations of relations en query's, that it's faster to do in the backend then over the network
    well, if Access were a server then yes that would be the case. But it isn't. It is a file based system and the frontend is going to be processing everything on the user's machine, regardless of whether it is split or not or if the queries are in the backend or on the frontend. If they are in the backend then the objects still need to be transferred over the network for the frontend to run. So, no - having them in the backend is not preferred.

    The relationships can ONLY be set in the backend. That will maintain referential integrity of your data. Setting them in the frontend is pointless really.

  7. #7
    raffie77 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    21
    Quote Originally Posted by boblarson View Post
    Relationships will be defined in the backend. The tables go in the backend the queries should be in the frontend.

    And you wrote:

    well, if Access were a server then yes that would be the case. But it isn't. It is a file based system and the frontend is going to be processing everything on the user's machine, regardless of whether it is split or not or if the queries are in the backend or on the frontend. If they are in the backend then the objects still need to be transferred over the network for the frontend to run. So, no - having them in the backend is not preferred.

    The relationships can ONLY be set in the backend. That will maintain referential integrity of your data. Setting them in the frontend is pointless really.

    ok thanks I'll give it a try

    i'll be here before anyone logs in to split the database
    thanks a lot for your time
    I appreciate that

    Regards,

    Ralph

  8. #8
    joy9393 is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2014
    Posts
    1

    Mohsin

    Also, if the database is not split, the limit is officially 255 users but practically it is maybe 10 users. Also, if the database corrupts, you would be at risk of losing ALL of your data AND ALL USERS would be unable to do their work. If you have a split database, the backend holds just the data and is less likely to corrupt. The frontend, can still corrupt but it would likely be for only one user and so everyone else could just continue on without a problem. You would then give the user with the corrupt frontend a new copy and they would go on with work. Not splitting is like playing Russian Roulette with your whole database. If someone is in there and a network disruption occurs for ONE of the connected users, that can corrupt the entire thing.

    __________________
    Mohsin

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change Access 2003 mdb workgroup using Access 2010
    By metsmaniac in forum Security
    Replies: 6
    Last Post: 04-06-2012, 08:56 PM
  2. Replies: 9
    Last Post: 08-07-2011, 11:21 AM
  3. import excel file to access 2003 yes, access 2007 no
    By stapik in forum Import/Export Data
    Replies: 9
    Last Post: 07-27-2011, 07:09 AM
  4. Replies: 6
    Last Post: 12-01-2009, 11:59 AM
  5. Replies: 1
    Last Post: 09-06-2006, 11:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums