How to make queries run faster in VB.

[matrekz42]

Good afternoon Gurus!

I don't know if this is possible, but I'm running code behind a button in a form that runs multiple queries, about (30). Is there a way to make these run faster? I'm using the following code:

stDocName = "Query_Name "
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me![MyProgress].Value = 5

stDocName = "Query_Name "
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me![MyProgress].Value = 10

stDocName = "Query_Name "
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me![MyProgress].Value = 15

stDocName = "Query_Name "
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me![MyProgress].Value = 20

[jeffstl]

The querys themselves will be whats important here.

If you can combine querys using JOINS or INNERJOINS , etc you may be able to reduce your number of querys from 30 to a smaller number.

Other then that it will also depend greatly on how much data the querys have to index through, and how well organized the table relationships are.

If everything is normalized and your tables are indexed, and your querys are structured efficiently it really shouldnt be that slow.

Other wise I would take a look at the querys and see if they can be re-written or combined.

Just to give you an example though, I have written ASP pages and access apps that loop through querys well over 30+ times in a loop and have not head any slowness issues. So it sounds like you are dealing with a huge amount of records possiby? Or is perhaps the access database being utilized by more then say 25 users simultaneously? Or the drive that the access database is stored on may also have accessibility issues (high volume of traffic on the network drive?)

[matrekz42]

The querys themselves will be whats important here.

If you can combine querys using JOINS or INNERJOINS , etc you may be able to reduce your number of querys from 30 to a smaller number.

Other then that it will also depend greatly on how much data the querys have to index through, and how well organized the table relationships are.

If everything is normalized and your tables are indexed, and your querys are structured efficiently it really shouldnt be that slow.

Other wise I would take a look at the querys and see if they can be re-written or combined.

OK, will do. Thanks!