[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Access VBA & ADO speed issue

Posted on 2009-03-31
2
Medium Priority
?
216 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 1000 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

590 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