VB6 + MS-Access97: Problem with RecordCount and EOF

Hi,

I'm using VB6 with MS-Access and have a little problem with RecordCount and EOF. In SQL I need something like this:
SELECT COUNT(DISTINCT survey_id) FROM question1 WHERE survey_id IN (SELECT survey_id FROM temp_surveys)

But unfortunatly, it's not supported by Access. So I tried this:

stmnt = "SELECT DISTINCT survey_id FROM question1 WHERE survey_id IN (SELECT survey_id FROM temp_surveys)"

rsBase.Open stmnt, dbBusiness, adOpenStatic, , adCmdText
base = rsBase.RecordCount
rsBase.Close



But in this snippet base is 1 even if the table temp_surveys has no records. The online-help says "Use .MoveLast", but it doesn't work either.

So, could anyone give me a hint how to solve this problem?

My second problem is related to the first one. I have an empty recordset (I'm sure it is!) and run the following code:

rsResults.Open stmnt, dbBusiness, adOpenStatic, adLockPessimistic, adCmdText
If Not rsResults.EOF then rsResults.MoveFirst
Do While Not rsResults.EOF
resultAbsolute(rsResults!option_id, subset) = rsResults!absolute
resultPercentage(rsResults!option_id, subset) = Round(rsResults!percentage, subset)
rsResults.MoveNext
Loop
rsResults.Close




Even when table on which rsResults is based is
empty, my program enters the while-loop.

I can't understand why.

Any help would be greatly appreciated.

Alex.

For Vb communicating with Access there is a problem with recordcount and EOF for that u will have to use these two statements to get the correct recordcount and eof status. they are as below, after u open the recordset move the recordpointer to the last record and then move the recordpointer to the first, then u will get the recordcount status, but for an eof u cannot movelast and movefirst which will generate an error (EOR or BOF error), so for that just create a new instance of ur recordset befor opening the recordset, bcos the same recordset might have been used and not properly reset so for that u might have to reset ur recordset before u open by using the following command,

set <your recordset Name> = new recordset

this will solve ur rpoblem, if u dont get a solution from the above then mailing ur code will be highly appreciated for us to help u in this regard (provided if the code is not confidential)

Pl get back to us on this
reg

The Ultimate Solution Providers

Authors

Sriman & Jayaraman

Email : [email protected]
[email protected]
Tech Talk : http://clubs.yahoo.com/clubs/solutionproviders


Hand Phone : +(6) 016 2237147 (Malaysia)