DAO SQL Query not returning all data 1

[Raydr]

Hi there.

Using Visual Basic 6.0 and an Access database.

In the past I wrote a database program that used ADO. Whenever I needed a whole bunch of data, I'd use a statement like this:

SELECT * FROM Table WHERE Variable = Condition

Of course, if there was more than one recordset that met the above condition, I'd get all of the recordsets.

Here is my problem:

SQL = "Select * from Cards Where EEPROMCAMID = '" & CAMID & "'"
Set SubDB = OpenDatabase(App.Path & "\satsubs.mdb")
Set rs = SubDB.OpenRecordset(SQL)

Now, the above statement should return 3 records, because I have 3 records in which EEPROMCAMID is equal to the CAMID variable. However, it's only returning the first recordset.

I've tried doing many things, tested with different data and in different tables, and it still does the same thing (only returns the first recordset, rather than all that match). The only way I could get it to return all of the data was to just select the entire table. That isn't how I need this to work.

I don't understand why my old program works correctly and my new one refuses to work. Some different in DAO and ADO? Maybe I'm missing a command in the connection to the DB?

I'm pulling my hair out here!

Thanks to anyone who has any ideas/solutions.
--

 

[LeahLady79]

I may be on the wrong track... but maybe you should try nesting your SQL statement inside a loop...

Do while EEPROMCAMID = "camid"
SQL = "Select * from Cards Set SubDB = OpenDatabase(App.Path & "\satsubs.mdb&quot
Set rs = SubDB.OpenRecordset(SQL)
Loop

NOTE: my syntax may be off, it's been a while since I've used regular VB.

Best of luck
~Leah

 

[vb5prgrmr]

How are you checking to see how many record you have retruned? If you are using the recordcount property then you should execute a movelast, movefirst to get the correct recordcount.

Side note: DAO when it cannot determine how many records are affected by a query will return 1 to let you know that there are records, while RDO will retrun -1.

So in short ...
[tt]
If daoRs.RecordCount <> 0 And daoRs.Eof = False And daoRs.Bof = False Then
daoRs.MoveLast
daoRs.MoveFirst
MsgBox daoRs.RecordCount
End If

daoRs.Close
[/tt]

Good Luck

 

[johnwm]

You don't need to loop for the SQL statement, but you do need to loop to read your recordset;

SQL = "Select * from Cards Where EEPROMCAMID = '" & CAMID & "'"
Set SubDB = OpenDatabase(App.Path & "\satsubs.mdb")
Set rs = SubDB.OpenRecordset(SQL)
Do While not rs.EOF
List1.Additem rs.Fields(1) ' or whatever
rs.MoveNext
Loop

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'