Database speed degenerates over time 1

[TimPen]

I have an Access 2000 database that runs on a peer-to-peer network. If I rebuild it, it is very fast but slowly the speed The problem is the database tends to run slowly over time. If I rebuild it it is very fast but slowly the speed degenerates
I want you to look at his network because I want to know if upgrading his hardware will help .
I regulary compact both the front & back end databases.

Is this an indication of a poorly designed database?
Will upgrading the network hardware help?

I also want to upgrade to Office 2010

Thank in advance

 

[MichaelRed]

Do you compact / repair the db often?

What EXACTLY do you mean by "Rebuild"

Can't " ... look at his ... ". Would need access which is improbable. Also looking at network would be better asked in networking fora (but again would need the access).

Upgrading to 1010 version could be a LARGE undetaking, particularly if using DAO in the 2000 ver,




MichaelRed

 

[TimPen]

Do you compact / repair the db often?
Access built in "Compact & Repair"

What EXACTLY do you mean by "Rebuild"
I create a new database and import all tables (back end) and a new database and import queries, forms etc (front end)

Upgrading to 1010 version could be a LARGE undetaking, particularly if using DAO in the 2000 ver,
Why?

 

[lameid]

What about defragmenting the harddrive the database file is on?

I have seen databases on file shares where a bunch of other office docs are created.... Not only does the database fragment over time sometime the database file does as well (although compact and repair should fix). For serious use, put access databases on at least their own logical volume.

Bad Design? Likely. Short of fragmentation or transient issues like number of users connecting etc, this leaves design. Are there appropriate indexes defined?

The only other thought I have is VBA code become corrupt from repeated changes.... You could try the decompile command line switch to test that scenario out.

 

[MichaelRed]

TYpically, creating the new DBs is no more effective than the builtin Compact and repair. Creating the new dbs and importing the ob jects does not preserve relationships (if you had / have any).

MS Access 2010 doesn't support DAO? DAO and ADO have significant differences in their deffinition and access protocalls, so all of them MUST be changed. Since I konlw of no automation tool to accomplish this, each occurance must be individually found and corrected.



MichaelRed

 

[lameid]

TimPen,

Please post what resolved your issue as this may be helpful to others.

MS Access 2010 doesn't support DAO? DAO and ADO have significant differences in their deffinition and access protocalls, so all of them MUST be changed. Since I konlw of no automation tool to accomplish this, each occurance must be individually found and corrected.

Interesting... I still actively code in DAO in older versions (2003) of Access as for connecting to Jet data the consensus here has been DAO is faster than ADO.

MichaelRed,
Back to the OP question about migrating to 2010... Have you noticed a material difference in performance in moving databases to 2010 when DAO to ADO conversion is required? Also what seems to be the hardware required to make Access 2010 work efficiently?

 

[TimPen]

Please post what resolved your issue as this may be helpful to others.

I will cerainly do so

 

[MichaelRed]

Re DAO vs ADO ... I have little actual exprience with this. I re-did a few small dbs, in ADO (from DAO) and found them to be somewhat slower. More general experience says that this is true for JET db-engine, but is reversed in the larger db-engines (SQL Server or Oracle). More detailed descriptions (of the situations and approached) suggest the approach to coding may be more relevant ... coding to minimize connection operations would benefit both but I know of no detailed analysis which might give quantative results for directly comparable situations.




MichaelRed

 

[PHV]

Anyway, DAO is still supported by ac2010.
The reference is:
Microsoft Office 14.0 Access database engine Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Thanks for the clarification PHV! Have a star.

...and nomination for the wall of shame for Microsoft for changing it... Then again it does better describe the Object model...

New question then, with the new name do I still use the DAO prefix with that reference?

As in...

Dim db as DAO.database

 

[PHV]

Yes

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TimPen]

I have found the reasons for forms being very slow to load.
They include:
1. Having active filters on the form
2. Having combo boxes which allow users to navigate / select records

If I remove these I don't experience and speed issues.

To allow users to navigate / select records, I have a cmd button on the form. When clicked it opens a pop up form allowing the user to select the record of choice. I store the record id as a variable, close the pop up form and filter the recordset of the form e.g
select * from tblInvoice where InvoiceId = tmpInvoiceId

works for me!