ADODB Recordset Query Problem.

[kashif.special2005]

Hi,

I have a table in ms-access like below

Associate_Name​

Region​

Domain​

Role​

Steve
East
DB
BOA
Jhonson
East
DB
BIO
Josh
East
DB
EBOA
Derek
East
DC
BOA
Haroon
East
DC
EBOA
Aman
East
DC
BIO


I want to take Associate_Name in recordset, and I am using below code

Sub Fetching_Data()

Dim rs As ADODB.Recordset
Dim str_sql As String

str_sql = "select [Associate_Name] from Associates where ([Role] like '*BOA*' Or [Role] Like '*EBOA*')" & _
"And [Domain]='" & Me.cmbdomian & "'" & "And [Region]='" & Me.cmbregion & "'" & "group by [Associate_Name] order by [Associate_Name];"

Set rs = New ADODB.Recordset

With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open str_sql
End With

If Not rs.EOF Then
rs.MoveLast
Debug.Print rs.RecordCount
End If

It is not giving me any error message but no record showing in recordset, but when I am manually running str_sal query it is giving me result like below

Associate_Name​

Josh
Steve


"select [Associate_Name] from Associates where ([Role] like '*BOA*' Or [Role] Like '*EBOA*')" & _
"And [Domain]='" & Me.cmbdomian & "'" & "And [Region]='" & Me.cmbregion & "'" & "group by [Associate_Name] order by [Associate_Name];"

Will show

select [Associate_Name] from Associates where ([Role] like '*BOA*' Or [Role] Like '*EBOA*')And [Domain]='DB' And [Region]='East' group by [Associate_Name] order by [Associate_Name];
Please help me to solve this problem.

Thanks
Kashif

 

[mdlueck]

As you are attempting to use ADO type objects, please allow me to suggest a working example you may refer to / learn from:

"Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables"
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

And please wrap your code in code tags... makes posts much easier to read.

 

[kashif.special2005]

Hi,

Thanks for reply

just an fyi....

That when I am removing Like criteria from the sql query it is working fine

Not Working

"select [Associate_Name] from Associates where ([Role] like '*BOA*' Or [Role] Like '*EBOA*')" & _
"And [Domain]='" & Me.cmbdomian & "'" & "And [Region]='" & Me.cmbregion & "'" & "group by [Associate_Name] order by [Associate_Name];"

Working

"SELECT Associates.Associate_Name FROM Associates WHERE Associates.[Region]='East' AND Associates.[Domain]='DB' ORDER BY Associates.[Associate_Name];"

I am not abale to understant that why it is not working with like operator

Please help.

Thanks
Kashif

 

[kashif.special2005]

Sorry forget to load the data

Associate_Name Region Domain Role
Steve East DB BOA
Jhonson East DB BIO
Josh East DB EBOA
Derek East DC BOA
Haroon East DC EBOA
Aman East DC BIO

Thank
Kashif

 

[mdlueck]

I am not abale to understant that why it is not working with like operator

Against an Access table, I have the following query correctly returning records using this LIKE syntax:

  'Define a query to look for FE DB temp tables
  strSQL = "SELECT M.[Name] " & _
           "FROM MSysObjects AS M " & _
           "WHERE M.[Name] LIKE 'tmptblqry_[COLOR=Red][B]%[/B][/COLOR]' " & _
           "AND M.[Type] = 1;"

I believe I have indicated the silver bullet difference.

Note: I believe DAO objects use a * character for the wild card whereas ADO objects use the % character.

 

[kashif.special2005]

Thank you so much mdlueck,
Now it is working fine

"SELECT Associates.Associate_Name FROM Associates WHERE Associates.[Region]='East' AND (Associates.[Role] Like '%BOA%' Or Associates.[Role] Like '%EBOA%') AND Associates.[Domain]='DB' ORDER BY Associates.[Associate_Name];"

Thanks
Kashif

 

[mdlueck]

I am glad the trouble is now resolved. You are most welcome.