I am using the following VBA code to essentially paste the SQL Query into a worksheet. When using the commented out query it works fine but for whatever reason adding the Right Join from the other SQLite table in the same database creates issues.
The macro running this returns the error ODBC Driver does not support the requested properties and highlights the last line. I can't find why the sql string is not working since the same works within sqlite itself. Any ideas or something I overlooked would be huge!
Dim conn As Object, rst As Object
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
ReportDate = Range("B1")
' OPEN CONNECTION
conn.Open "DRIVER=SQLite3 ODBC Driver;Database= ** omitted for security ** ;"
'strSQL = "SELECT IPDM_Number, Fund_Name, Manager, Rating," & _
' " Commitment, Asset_Value, Sub_Asset_Class, Asset_Class" & _
' " FROM Illiquid_Table WHERE Report_Date = '" & ReportDate & "' ORDER BY Rating"
strSQL = "SELECT IPDM_Number, Fund_Name, Manager, Rating," & _
" Commitment, Asset_Value, Sub_Asset_Class, Asset_Class" & _
" FROM Illiquid_Table RIGHT JOIN Rating_Code ON Rating_Code.Rating = Illiquid_Table.Rating" & _
" WHERE Report_Date = '" & ReportDate & "' ORDER BY Rating_Code.Code desc;"
' OPEN RECORDSET
rst.Open strSQL, conn, 1, 1
