CreateQueryDef, OpenRecordSet Only returns 1 Record

[crimsntyd]

Hi,
I'm trying to populate a flexgrid in my VB 6.0 application with values from a query of an Access table:

sql = sql & "SELECT * "
sql = sql & "FROM SongInfo "
sql = sql & "WHERE " & Sec1 & Sec2 & Sec3

'Populate flexgrid
Set db = OpenDatabase(Appl)
Set qu = db.CreateQueryDef("", sql)
Set rs1 = qu.OpenRecordset

Sec1, Sec2 and Sec3 are strings that the user creates by populating fields on the search form. In this example, there's nothing in Sec2 or Sec3, so, sql ends up looking like this:

SELECT * FROM SongInfo WHERE SongTitle Like '*amazing*'

The problem is I'm only getting 1 record. If I run the EXACT SAME QUERY in MS Access, I get multiple records, so it's working fine there. I've tried all the different options (dbOpenDynaSet, dbOpenTable, etc.) without any success. I know the flexgrid is working OK, because I've checked the recordcount before getting to the flexgrid code. Any ideas?

VB 6.0
MS Access 2003

 

[petermeachem]

Ah you need to use % not * for the wildcard.

 

[crimsntyd]

OK, I tried that. It gives me NO records. Remember, if I type the query in SQL view in query designer in Access, I get 7 records, as I should. It's something to do with the VB app, I think, that for some reason is just giving me ONE. Any other ideas? I'm really stumped on this one.

 

[petermeachem]

You use * in Access but % for ADO.
What single song title do you get back?

 

[crimsntyd]

Hmm...I'm using DAO, not ADO, could that have something to do with it?

I get the song in my database titled "Amazing". That's the whole title. Just FYI, when I search on '*rhythm*', I should get two records but I only get "Rhythm of the World". The other song is "Rhythm of Heaven". So, the like statement SEEMS to be working, but for the life of me I can't figure out why I'm only getting one record.

Thanks for your help.

 

[SBerthold]

1. Are you sure it is only returning one record, or is it just the RecordCount property which you are inspecting?
The RecordCount property will probabably only return "1" with the Querydef, but should return an accurate count if you first move to the last record then back to the first.
This however shouldn't matter if you are looping through the records or using a bound control.
Is the SongTitle field a Text field, or a Memo field?

2. In a LIKE clause under DAO, you can also use the % wildcard with the ALIKE operator, which then makes it easier to change when later converting to ADO.

 

[crimsntyd]

I forgot that you have to do the MoveFirst then MoveLast then back to the first for the RecordCount to work. Thanks.

 

[SBerthold]

you are welcome.

The first MoveFirst is not needed. Just MoveLast and then MoveFirst.