Excel to Access VBA - Syntax Error, Data not returned - help

[ramm_66]

Dear Gurus

Please help on the above error, I have attached my file as well.

I am trying toget data from a table in access database. All my parameters are in excel VBA and excel cells.

In the above code when I type exact matching parameter thequery works but when I put "*"toselect all from a column it isnot returning any values. Can you please help me on this.

So that If I can give parameter to Area like"IT "and parameter for place will be '*' to select Area as IT and places includes All.

Many thanks for your help in Advance.


Error Area -

[COLOR=#141414][FONT=Consolas]SQL = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"SELECT * FROM "[/FONT][/COLOR][COLOR=#141414][FONT=Consolas] & strTable & [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]" WHERE Area = '"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas] & Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Sheet1"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]).Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"A1"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) & [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"' AND Place ="[/FONT][/COLOR][COLOR=#141414][FONT=Consolas] & Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Sheet1"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]).Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"A2"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) & [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]""[/FONT][/COLOR]

Actual Code -

Option Explicit

Sub CreateAndRunQuery()
    
   Sheets("New Query").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    'Declaring the necessary variables.
    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strTable    As String
    Dim SQL         As String
    Dim i           As Integer
                
    'Disable screen flickering.
    Application.ScreenUpdating = False
    
    'Specify the file path of the accdb file. You can also use the full path of the file like:
     AccessFile = "S:\ITCM Project Libraries\Projects\10136\Other Project Documents\Applications Estate\Ram Mente\DAD Backup\Copy of CT & N Database_14102015.accdb"
    
    'Set the name of the table you want to retrieve the data.
    strTable = "Test_1610"
    
    On Error Resume Next
    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")
    'Check if the object was created.
    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Open the connection.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile
    
    'Create the SQL statement to retrieve the data from table.
    
      'SQL = "SELECT * FROM " & strTable & " WHERE Area = '" & Sheets("Sheet1").Range("A1") & "' AND Place='" & "Like" & " " & Sheets("Sheet1").Range("A2") & "'"
      'SQL = "SELECT * FROM " & strTable & " WHERE Area = '" & Sheets("Sheet1").Range("A1") & "' AND Place ='" & Sheets("Sheet1").Range("B1") & "'OR Place ='" & Sheets("Sheet1").Range("C1") & "'"
        SQL = "SELECT * FROM " & strTable & " WHERE Area = '" & Sheets("Sheet1").Range("A1") & "' AND Place =" & Sheets("Sheet1").Range("A2") & ""
        
    On Error Resume Next
    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.Recordset")
    'Check if the object was created.
    If Err.Number <> 0 Then
        'Error! Release the objects and exit.
        Set rs = Nothing
        Set con = Nothing
        'Display an error message to the user.
        MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
        Exit Sub
    End If
    On Error GoTo 0
         
    'Set thee cursor location.
    rs.CursorLocation = 3 'adUseClient on early  binding
    rs.CursorType = 1 'adOpenKeyset on early  binding
    
    'Open the recordset.
    rs.Open SQL, con
    
    'Check if the recordet is empty.
    If rs.EOF And rs.BOF Then
        'Close the recordet and the connection.
        rs.Close
        con.Close
        'Release the objects.
        Set rs = Nothing
        Set con = Nothing
        'Enable the screen.
        Application.ScreenUpdating = True
        'In case of an empty recordset display an error.
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If
    
    'Copy the recordset headers.
    For i = 0 To rs.Fields.Count - 1
        Sheets("New Query").Cells(1, i + 1) = rs.Fields(i).Name
    Next i
    
    'Write the query values in the sheet.
    Sheets("New Query").Range("A2").CopyFromRecordset rs
    
    'Close the recordet and the connection.
    rs.Close
    con.Close
    
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    
    'Adjust the columns' width.
    Sheets("New Query").Columns("A:E").AutoFit
    
    'Enable the screen.
    Application.ScreenUpdating = True


    'Inform the user that the macro was executed successfully.
    MsgBox "The customers were successfully retrieved from the '" & strTable & "' table!", vbInformation, "Done"


End Sub

 

[RoryA]

You need to use Like and not = if you want wildcards, and you should use % rather than *.

 

[ramm_66]

RoryA

thanks lot for getting back to me, I have tried % but nothing is working for me, can you please modify my above single line code if possible.

 

[ramm_66]

Rory thanks a lot.

It just worked I change the code to

SQL = "SELECT * FROM " & strTable & " WHERE Area = '" & Sheets("Sheet1").Range("A1") & "' AND Place Like '" & Sheets("Sheet1").Range("A2") & "'"