Hi,

I am using ADO to retrieve records from an MS ACCESS DB in Excel. All my queries work fine but I am having problems with subqueries. My subqueries work fine in ACCESS but when I execute them via ADO I get the following error message:'"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect".

I hope someone can help me with the correct syntax. Alternatively I was thinking of creating a view in ACCESS but that seems not possible.

Help would much appreciated!


PHP Code: 
Sub retrieve_loan_details_test()

Dim Provider As String
Dim Source As String
Dim sConn As String
Dim sSql As String
Dim rep_per As Date
Dim LB, UB As Long

Dim cnPubs As ADODB.Connection
Dim rsPubs As ADODB.Recordset
Set cnPubs = New ADODB.Connection
Set rsPubs = New ADODB.Recordset

Provider = Range("Provider")
Source = Range("Source")
rep_per = Range("rep_per")
LB = "12"
UB = "18"

sConn = "PROVIDER=" & Provider & ";"
sConn = sConn & "DATA SOURCE=" & Source & ";Persist Security Info=False;"
cnPubs.Open sConn

sSql = "select c.bank, a.appID, a.month, b.min, sum(a.Mdeopn)"
sSql = sSql & " from Monthly_balances a, (SELECT appID, min(prepdate) as min FROM Prep_input where fullpartial = 'Full' group by appID) b, Loan_details c"
sSql = sSql & " where a.appID = b.appID"
sSql = sSql & " and a.appID = c.appID"
sSql = sSql & " and a.rep_per = #" & rep_per & "#"
sSql = sSql & " and a.month between " & LB & " and " & UB & ""
'sSql = sSql & " and DateDiff("m", #" & rep_per & "#", b.min) > 6"
sSql = sSql & " Group by c.bank, a.appID, a.month,b.min"


With rsPubs
    .ActiveConnection = cnPubs
    .Open sSql
    Range("tst").CopyFromRecordset rsPubs
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub