[VBA] SQL to get data from access to excel

[skorpionkz]

Hi All,

I am building VBA tool and need to incorporate report into it. Data is recorded in Access database.

User will pick status of project and project code list to see in the Report.

I build SQL which works in Access query, but fails in VBA

sample query

SELECT *  FROM Key_table WHERE (Status = 'Submitted') AND (LookUp_Key LIKE 'PRJ0001*' OR LookUp_Key LIKE 'PRJ0002*')

Any ideas how to force it to work in VBA?

Private Sub LoadKeyTable()
    Dim myConnection            As New ADODB.Connection
    
    Dim v_Item                  As Variant
    Dim str_SQL                 As String
    Dim str_List                As String
    Dim arr_List                As Variant
            
    str_List = uf_Main.txt_ReportList
    If Not str_List = Empty Then
        arr_List = Split(str_List, ",")
        
        str_List = Empty
        For Each v_Item In arr_List
            If Not v_Item = Empty Then
                If str_List = Empty Then
                    str_List = "LookUp_Key LIKE '" & v_Item & "*'"
                Else
                    str_List = str_List & " OR LookUp_Key LIKE '" & v_Item & "*'"
                End If
            End If
        Next v_Item
    End If
       
    str_SQL = "SELECT *  FROM " & DB.KEYTABLE
    
    str_SQL = str_SQL & " WHERE (Status = '" & PBL.LoadList & "')"
    
    If Not str_List = Empty Then str_SQL = str_SQL & " AND (" & str_List & ")"
    
    Set myConnection = ConnectTo_IPSDB
    Set PBL.RecordSet = myConnection.Execute(str_SQL)
      
    RecordSetToClass DB.KEYTABLE


    myConnection.Close
                
    Set PBL.RecordSet = Nothing
    Set myConnection = Nothing


End Sub

Thank you,

 

[Fazza]

hello, Andrzej. Please try a % instead of * wildcard. regards, Fazza

 

[skorpionkz]

Thank you,
Simple solution It is weird one though... MS Access SQL work with * rather than %. And if you ran query within Access * works just fine...

Any way thank you. This did the trick.

 

[jmacleary]

Hi scorpionkz. I agree its curious, but you will find many varieties of SQL implemented in the world, with different syntax for things like wildcards, date formats, etc. It's a real pain for me at times because I work with access SQL, msquery sql, mySQL and others as well.