Access Recordsets

[ParaTrooper]

Access Recordsets

Hi,

I am having trouble with an access recordset. It is querying a simple table of students, who belong to particular groups (SGROUPS).
The query works fine when without the wildcard * chararcter and the LIKE clause. It returns nothing when they are in there when there is a whole load of data that it should select.

Dim studentRS As ADODB.Recordset
Set studentRS = New ADODB.Recordset
studentRS.CursorLocation = adUseClient

Dim students As String

students = "SELECT LASTNAME, FIRSTNAME, SGROUP FROM [Student detail] WHERE [Student detail].SGROUP LIKE '" & excelSGROUP & "' " '//excelSGROUP contains the group. eg D1129*


studentRS.Open students, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If studentRS.EOF Or studentRS.BOF Then
'do nothing, empty RS
MsgBox "empty rs"
Else
'loop thru studentRS
Do Until studentRS.EOF

MsgBox studentRS!LASTNAME


Thanks for your help,
ParaTrooper

 

[LittleSmudge]

I suspect that your LIKE statement is the wrong way round for the wild card.

Post examples of the contents of SGroup

and confirm that excelSGroup is a string variable containing "D1129*"


G LS

 

[TrojanRabbit]

Sounds like you may have had it in there before, but I noticed that * is not in the code you posted (... excelSGroup & "*'&quot. Your code looks fine to me. May not make a difference, but you can try
Set rs = CurrentDb.OpenRecordset(students)

 

[LittleSmudge]

Or rather DON'T try
Set rs = CurrentDb.OpenRecordset(students)
because that is DAO code and you're using ADO



G LS

 

[TrojanRabbit]

Actually, it may be the studentRS command. Are you using Access 97 or 2000? I've never used 97, so I don't know its syntax. In 2000, you need to set the recordset object using the database's OpenRecordset function. Since studentRS is empty, there's nothing to open from it. Opening it from the database gives you something to look at.

 

[LittleSmudge]

I really don't get what you're saying there Trojan

ParaTrooper is clearly using ADO in:-

Dim studentRS As ADODB.Recordset
Set studentRS = New ADODB.Recordset

This HEAVILY implies A2k or A2k2

Yet you put
In 2000, you need to set the recordset object using the database's OpenRecordset function.

But the OpenRecordset function in

Set rs = CurrentDb.OpenRecordset(students)

is a DAO construct.
Whilst I know that it is possible to run DAO in A2k it is unusual. Most people running DAO are using it because they are stuck in A97.

Are you really saying that you are using A2k and using DAO - "by choice" ?


G LS