Solved

Access VBA & ADO speed issue

Posted on 2009-03-31
2
205 Views
Last Modified: 2012-06-27
Hi

I am running the following ADO code but the procedure is taking over 10 minutes to finish.

Basically it opens a first recordset to get a user id.  The user id is then used as criteria for a sql string which opens a second recordset based on membership records.  Various tests are performed on the records in the second recordset before this recordset is closed and the next user id is got from the first recordset etc etc

The User table has approx 15000 records whilst the Membership table has approx 27000.

The code works but is slow.  I am fairly new to ADO so my question is simply is there any way I could speed this up by using different cursors or methods

Many thanks

Ken
objcon.Open CurrentProject.Connection
 

        objrst.Open "Users", objcon, adOpenKeyset, adLockOptimistic
 

        

        With objrst
 

        Do Until .EOF = True
 

            lonCurrID = !UserID             
 

          	strSQL2 = "SELECT UserID, StartDate, EndDate, GroupID, TypeID from Membership where UserID = lonCurrID"
 

               objrst2.Open strSQL2, objcon, adOpenKeyset, adLockOptimistic

                   

                        With objrst2

                        

                            	Do Until .EOF = True

                              

                        		select case
 

				<do various tests>
 

			end select                     	    	

                                    

                                	.MoveNext

                            Loop

                        

                        End With

            

                        objrst2.Close
 

            .MoveNext

            Loop
 

        End With
 

        objrst.Close
 

        objcon.Close

Open in new window

0
Comment
Question by:kenabbott
2 Comments
 
LVL 28

Accepted Solution

by:
TextReport earned 250 total points
ID: 24033321
Ultimately using SQL would be quicker rather than the way you are doing but it depends on what the TESTS are that you are doing. For example a UPDATE statement with the criteria required would be quicker than walking through the records.
You should be able to do this by creating a single recordset and looping through that rather than 2 recordsets

Hope this makes sense and you can expand on the tests that you are carrying out.

Cheers, Andrew
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 24033360
try using the recordcount instead of testing the EOF

dim recCnt1 as integer, recCnt2 as integer,x,j

objrst.movelast
recCnt1=objrst.recordcount
objrst.movefirst

for x=1 to recCnt1




next x


i used this in DAO, and prove to run faster than using .EOF


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now