Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Newport, Gwent, Wales
    Thanked 0 Times in 0 Posts

    Speed, Database performance that is... (Access 2K Win 2K)

    Dear All

    I know the solution to this question is on here, but with the search facility temporarily 'off' I can't find it.

    I've got a database that uses linked Excel spreadsheets as the data source (has to be like that as the original data can not be linked to). The performance of the PC degrades over a period of time (only when this database is being used), until I eventually give up and reboot my PC. This give me a temporary fix, other symptoms are regular increases in the virtual memory on my PC (Windows gives me that little notification message).

    I know that if I'm using DAO, or ADO in my code to work with data sets I have to set my record sets, database etc to nothing at the end of the code, do I need to be doing something similar to this when I'm opening forms with large queries etc as the dataset? some thing in the "On Close" property along the lines of: - "Me.Recordset = Nothing"? I've never had the problem of a database performance degrading before, let alone having one degrade the performance of my entire PC, but this is the first system I've used none Access data tables in.

    Are there any other areas I should consider with this problem? I've replaced my SQL with pre-defined queries (helped a little). I was considering making the database import the tables each time it was opened, but talking to the potential users this would not be a popular move as the process takes quite a while on my PC, and most of them have older, slower PCs.

    Thanks for the help.


  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Speed, Database performance that is... (Access 2K Win 2K)

    Start by turning off Name Autocorrect if you haven't already done so. It keeps a log of changes and that log can grow tremendously, affecting memory usage. It is also responsible for a lot of other strange behavior in A2k. Also make sure that the queries behind your forms are saved so that Access can optimize them. Don't use a SELECT statement as the rowsource for your forms.

Posting Permissions

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