Link to home
Start Free TrialLog in
Avatar of rfreud
rfreud

asked on

Trouble opening a recordset on a query in MS Access VBA

I am having trouble opening a recordset on a query in an MS Access application based on a string variable that I am passing to it.    The variable I am passing a string and it is scoped as a public variable.  The value of the string variable is determined in a different recordset (named rst1).

The flow is like this:
1.  I open the first recordset (rst1) and inspect the resulting records one by one and if a particular condition is met, the code passes the string variable to a function that opens a second recordset (rst2) to do a specialized analysis on the records in that recordset.  

For testing, I typed in three values on the criteria line of the query that produces the first recordset (rst1).  When I run the code, everything is fine, i.e. when rst1 finds a record that requires special processing, it passes the value to the statement that opens the rst2 and it does what it supposed to do.  When it completes that step, control is returned to the rst1 where it continues to examine records and so on.    

The problem comes when I remove the specific enumeration of the records in the query that produces (rst1), and expect it to run through the whole list of records, passing the string variable to rst2 when necessary and then looping back to rst1 for the next record to examine.  

What happens is the following line (that opens rst2) just hangs.

rst2.Open "Select * From qry_Myquery where id = """ & strMyString & """"

I have a debug.print line immediately preceding the line that opens the second recordset and it confirms that the string variable contains a correct string.  

Thank you in advance for your help.
Ron
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rfreud
rfreud

ASKER

Dear Rey,
No - no error message, just the twirling circle.  
If I put that value on the criteria line the query does produce valid records.  In the mean time I did some research and made a change that may have solved the problem, although I am not certain.  

I did not have a connection statement to the ADODB.recordset.  I put that in the code and it may have solved the problem.  The other thing that I did was that I further parameterized the query that creates rst1 so that the only records rst1 produces are ones that require processing through rst2.  I set that in motion for the first 25 records and it ran.  So then I set the query that produces rst1 to produce all the records where processing through rst2 is necessary (247 of them) and it is running now.  

Another thing about all this is that I am running this through a VPN connection to a network that runs as slowly as molasses.  What I may have been interpreting as the query on rst2 hanging may only have been the extreme amount of time it was taking to process the first record through rst2.  

I will let you know how this turns out.   Thanks for you help.
Ron
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rfreud

ASKER

To both experts - Thanks very much for your input and suggestions.  I am sure I will use these techniques the next time I get tangled up with a similar problem.  I really appreciate your help!
Can you let us know what the ultimate solution was?
Avatar of rfreud

ASKER

I am not entirely sure.  While I was waiting for the timely comments from both experts, I came upon the fact that I had not included a "connection" statement in my code.  At the same time, I trimmed down the results of rst1 to only those records that required processing through rst2.  There also was a line later in the code that referred to a counter that was no longer part of the picture, and though I don't think it had any effect on things, I eliminated that as well.

The bottom line is, (I think) that the string variable was being passed correctly to the code that opened rst2, but other factors, external to that particular function had an impact AND also that fact that I was running this against a Sybase system over a VPN probably had the biggest impact.  When I was watching the little twirling circle for over a minute and not seeing any process through debug lines that show up in the Immediate window, I was presuming that the damn thing was hung - BUT in fact it may have been symptomatic of how slow the network is on the other end of the VPN connection.  
Hope this explains it....
Thanks again,
Ron
ok, thanks