Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report problem (Access 2000)

    Greetings,

    Using Access 2000, I have designed & deployed a server-based database table (Be) in the server and distributed the (Fe) to about 10

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report problem (Access 2000)

    1. I would investigate the record source of the report. Even if people are entering a lot of records, there is no a priori reason that a report would not take them into account.

    2. With the number of records being entered, I would compact the backend database at least once a week, perhaps twice.

    3. You're OK in Access for the moment, but I would start thinking about SQL Server when you hit, say, 30,000 records (just a rough figure). It also depends on the complexity of the queries you're running - since all queries are run on the user's PC, they may involve a lot of data transport. SQL Server queries can be run on the server, thus involving less data transport.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report problem (Access 2000)

    Hans,
    Thanks for the reply.
    Do all users have to logout of Access for me to compact & repair the backend?

    Thanks,
    OCM

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report problem (Access 2000)

    As long as a user has a table in the backend open, you won't be able to compact it. My frontend databases always keep a backend table open during the entire session, so the user would have to close the frontend. However, I don't know how your frontend is set up.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report problem (Access 2000)

    You must have exclusive access to the database when it's compacted. Links count, so if someone else has an active link to the database, that will prevent the compact.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report problem (Access 2000)

    Did you mean 300,000 Hans? I would consider 30,000 a very modest size database and nowhere in the SQL Server neighborhood.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report problem (Access 2000)

    I think it depends on what you do with the data. Tables with hundreds of thousands of records don't pose a problem if their use is relatively straightforward. On the other hand, in databases with complicated queries involving lots of calculations, based on tables with some tens of thousands of records, I noticed that performance was pitiful if the data were stored on a network disk, and acceptable if stored locally. In such cases SQL Server would be a better solution.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report problem (Access 2000)

    I would stay with Access for the moment. Migrating the back end to SQL server is not such a big deal in itself, but optimizing performance (and setting up SQL Server) is more work.

  9. #9
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report problem (Access 2000)

    1.When users have the frontend open and I try to open the backend table, I see Access lockfile. So I assume that during the entire session the frontend keeps the backend table open. Thus, I have to compact the backend table when users stop working on the frontend.

    2. In regard to using Sql server for future, yes the database has a lot of complex cross tab queries, calculations etc. At this moment I

  10. #10
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report problem (Access 2000)

    Hans,
    Thanks for your suggestion. As you mentioned, I'll stay with Access for now.

    Regards,
    OCM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •