On the fly Search Query from Excel querying Access

[frozbie]

Hi

I've written an application in MS Excel that uses Excel forms to enter and display information that is stored in an MS Access database.

I am developing it to allow users to search for key words in details fields.

I have a problem with my code where I am creating a SQL string on the fly bringing in text the user has entered to search on. The SQL does not bring back any records when run on code. However, if I copy the created SQL and paste into an Access query and run in Access, it does return records.

If anyone has come across this before I would appreciate directing me to links with a solution. I may end up changing the way the query is created to allow records to be returned and if so will post the solution.

The code I am using is below:

Private Sub cmdSearchDetails_Click()

    Dim con As ADODB.Connection '// requires reference to Microsoft ActiveX Data Objects 2.8 library
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim strDB As String
    Dim strSQL As String
    strDB = "Data Source=" & strDataSource
    
    Set con = New ADODB.Connection
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open ConnectionString:=strDB
    End With
    
'++ following query works as a union query searching on flag
'SELECT tbl_CallsAndProjects.PK_CallsAndProjects, tbl_CallsAndProjects.Call_Project_Detail
'FROM tbl_CallsAndProjects
'WHERE tbl_CallsAndProjects.Call_Project_Detail Like "*flag*"
'Union
'SELECT tbl_CallsAndProjects.PK_CallsAndProjects, tbl_TimeLog.ActivityNotes
'FROM tbl_CallsAndProjects INNER JOIN tbl_TimeLog ON
'tbl_CallsAndProjects.PK_CallsAndProjects = tbl_TimeLog.FK_CallsAndProjects
'WHERE  tbl_TimeLog.ActivityNotes Like "*flag*";
    
strSQL = "SELECT cp.PK_CallsAndProjects, cp.Call_Project_Detail " _
& "FROM tbl_CallsAndProjects as cp " _
& "WHERE cp.Call_Project_Detail Like '*" & txtSearchString.Value & "*' " _
& "Union " _
& "SELECT cp.PK_CallsAndProjects, t.ActivityNotes " _
& "FROM tbl_CallsAndProjects as cp INNER JOIN tbl_TimeLog as t ON " _
& "cp.PK_CallsAndProjects = t.FK_CallsAndProjects " _
& "WHERE  t.ActivityNotes Like '*" & txtSearchString.Value & "*' "
    
    Set cmd = New ADODB.Command
    
    With cmd
        .ActiveConnection = con
        .CommandText = strSQL
        .CommandType = adCmdText
    End With
    
    Set rst = cmd.Execute()
    
    
    
    '++ code failing at this point as recordset being returned has no records
    '++ weird thing is that pasting SQL into access query and running DOES
    '++ return records!!!!!!! WHY?
    If Not rst.EOF Then
        rst.MoveFirst
        frmReOpenCall.lstClosed.Column() = rst.GetRows
    End If
    
    
    rst.Close
    con.Close
End Sub

Thanks

Mark
PS, on a train as I write this, free INternet access, how cool is that...

 

[pbaldy]

Why is this a poll about SQL Server? In any case, ADO likes % as a wildcard instead of *.

That is cool having internet on a train.

 

[frozbie]

Microsoft fix

Hi,

Just found an interesting link on Microsoft's site. It may identify the issue:

http://support.microsoft.com/kb/311313

I will need to check as my version of the driver is probably older.

Let you know.

Mark

 

[frozbie]

Sorted

Paul,

Thank you!!!!!

Changing the wild card to % solved the problem.

The poll was a mistake, I thought it was a poll on this post. Too late to take it off so I just put in the first thing that came to me...

Regards

Mark

Code I am now using is shown below:

Private Sub cmdSearchDetails_Click()

    Dim con As ADODB.Connection '// requires reference to Microsoft ActiveX Data Objects 2.8 library
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim strDB As String
    Dim strSQL As String
    strDB = "Data Source=" & strDataSource '// Where string has been set as constant at beginning of module to show path to database
    
    Set con = New ADODB.Connection
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open ConnectionString:=strDB
    End With
    
'++ following query works as a union query searching on flag
'SELECT tbl_CallsAndProjects.PK_CallsAndProjects, tbl_CallsAndProjects.Call_Project_Detail
'FROM tbl_CallsAndProjects
'WHERE tbl_CallsAndProjects.Call_Project_Detail Like "*flag*"
'Union
'SELECT tbl_CallsAndProjects.PK_CallsAndProjects, tbl_TimeLog.ActivityNotes
'FROM tbl_CallsAndProjects INNER JOIN tbl_TimeLog ON
'tbl_CallsAndProjects.PK_CallsAndProjects = tbl_TimeLog.FK_CallsAndProjects
'WHERE  tbl_TimeLog.ActivityNotes Like "*flag*";
    
strSQL = "SELECT cp.PK_CallsAndProjects, cp.Call_Project_Detail " _
& "FROM tbl_CallsAndProjects as cp " _
& "WHERE cp.Call_Project_Detail Like '%" & txtSearchString.Value & "%' " _
& "Union " _
& "SELECT cp.PK_CallsAndProjects, t.ActivityNotes " _
& "FROM tbl_CallsAndProjects as cp INNER JOIN tbl_TimeLog as t ON " _
& "cp.PK_CallsAndProjects = t.FK_CallsAndProjects " _
& "WHERE  t.ActivityNotes Like '%" & txtSearchString.Value & "%' "
    
    Set cmd = New ADODB.Command
    
    With cmd
        .ActiveConnection = con
        .CommandText = strSQL
        .CommandType = adCmdText
        .Prepared = False
    End With
    
    '++ possible this is the problem?
    'Set rst = cmd.Execute()
    
    '== altered the setting of rst object based on comments in link:
    'http://www.experts-exchange.com/Databases/MS_Access/Q_22030962.html
    '== did not cause code to work by itself but after changing wildcard
    '== to % caused sub to work, have just left it in
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    
    rst.Open cmd
    
    '++ code was failing at this point as recordset being returned had no records
    If Not rst.EOF Then
        rst.MoveFirst
        frmReOpenCall.lstClosed.Column() = rst.GetRows
    Else
        MsgBox (txtSearchString.Value & " is not found in call details")
    End If
    
    
    rst.Close
    con.Close
End Sub

 

[pbaldy]

No problem; have a nice train ride!