Access 2000 – poor performance (2000)

Topic

New Reply

I am experiencing quite poor performance in Access 2000. Set up is a front end running locally on Win 2000, linking to a back end on a network drive, plus links to a couple of other network databases. It was originally an Access 97 database, but rather than converting, I created a new database in 2000 and imported the 97 objects. The sub-datasheet name property is set to ‘None’ in the back end. I have noticed that the more databases I link to the slower it gets. When opening an unlinked table with one record, it takes up to 10 seconds to open. Removing all the linked tables makes opening the table instantaneous, even though the table I’m opening is not a linked table. I have created a persistent link to the main BE with little or no improvement. Would persistent links to all linked databases help?

Any ideas would be appreciated. Ask me a question if I’ve missed anything.

Reply | Quote

Replies

Are all the back-end databases Access 2000, or are some or all still Access 97?

Reply | Quote

All are Access 2000. Because I was creating a new seceurity workgroup MDW file, I created new databases and imported the objects, rather than a straight conversion.

Reply | Quote

Are all the back-end databases Access 2000, or are some or all still Access 97?

Reply | Quote

Make sure the front end tables also have their subdatasheet property set to None, and turn off Name Autocorrect (Tools–>Options–>General). Linking over a network can also be slow because of the network, not just because of settings in Access. If the back ends were not converted and are still 97, that will cause a huge performance hit because 2000 supports unicode and 97 doesn’t, so there is a lot of translation going on.

Reply | Quote

Hi – I discovered this option was set to [Auto] on some FE tables – I set it to [None] and that fixed it for those tables. The autocorrect thing is turned off, plus I have set up persistant connections to all the databases that link to the front end. Although all the above has helped, it’s still slower than Access 97 on the same network – I think perhaps only SQL Server will solve these speed issues…

Reply | Quote

May I ask why you have links to multiple network databases? I haven’t seen a significant difference in performance between properly tweaked 2000/2002 databases and their 97 equivalents. If your code came forward from 97 or earlier, there may be some stuff in there that is inefficient and can be replaced with new functions added in the later 2 versions. If you are using a lot of SQL Select statements to populate forms, comboboxes, etc., instead of saved queries, you’ll see a hit the first time you open a form because Access has to compile the SQL. If most of your delay is in loading forms, you probably need to change your approach to the way you do some things. If you use a lot of form references in queries that populate other forms or reports, that’s another thing that can sap performance. The query engine changed significantly between 97 and 2000, so be sure to check all your queries and make sure they function as expected and don’t have any nasty surprises. One irritating difference is that query parameters that were entered in 97 may wind up as strings if you didn’t put all the square brackets in around every element in the parameter.

Reply | Quote

As well as the usual back end database links, I link to a couple of other tables in 2 other databases – one contains our company’s store information and the other contains user details – they are set up like this so other databases can use them as well. I don’t think this is part of the problem though.

I will look into the other things you mention, especially the queries – the database was originally written in 97.

Many thanks

Reply | Quote

As well as the usual back end database links, I link to a couple of other tables in 2 other databases – one contains our company’s store information and the other contains user details – they are set up like this so other databases can use them as well. I don’t think this is part of the problem though.

I will look into the other things you mention, especially the queries – the database was originally written in 97.

Many thanks

Reply | Quote

May I ask why you have links to multiple network databases? I haven’t seen a significant difference in performance between properly tweaked 2000/2002 databases and their 97 equivalents. If your code came forward from 97 or earlier, there may be some stuff in there that is inefficient and can be replaced with new functions added in the later 2 versions. If you are using a lot of SQL Select statements to populate forms, comboboxes, etc., instead of saved queries, you’ll see a hit the first time you open a form because Access has to compile the SQL. If most of your delay is in loading forms, you probably need to change your approach to the way you do some things. If you use a lot of form references in queries that populate other forms or reports, that’s another thing that can sap performance. The query engine changed significantly between 97 and 2000, so be sure to check all your queries and make sure they function as expected and don’t have any nasty surprises. One irritating difference is that query parameters that were entered in 97 may wind up as strings if you didn’t put all the square brackets in around every element in the parameter.

Reply | Quote

Hi – I discovered this option was set to [Auto] on some FE tables – I set it to [None] and that fixed it for those tables. The autocorrect thing is turned off, plus I have set up persistant connections to all the databases that link to the front end. Although all the above has helped, it’s still slower than Access 97 on the same network – I think perhaps only SQL Server will solve these speed issues…

Reply | Quote

Make sure the front end tables also have their subdatasheet property set to None, and turn off Name Autocorrect (Tools–>Options–>General). Linking over a network can also be slow because of the network, not just because of settings in Access. If the back ends were not converted and are still 97, that will cause a huge performance hit because 2000 supports unicode and 97 doesn’t, so there is a lot of translation going on.

Reply | Quote