Trouble running an access query in excel vba

[Legal Tender]

So we use a number of access databases to store transaction data. Recently I got asked to run a query against 36 databases, the query was not in the databases so it had to be written and I just assume that I would do what I always do which is to write the query in access, test it to make sure it works then write a small excel macro to loop through all of the databases and get my data. I have done this numerous times so I kind of know what to expect in terms of converting the query from access into excel which for me is mainly to wrap things that were in quotes in access in double quotes for excel, so I did that and got the following debug message:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

This is the sql of the query that runs in access without issue

SELECT [All Interface].Entity, [All Interface].[Intfc Date], [All Interface].[HAR (Hospital Account Record)], [All Interface].[Nat Account], [All Interface].[Source of Fund], [All Interface].[Service Date], Sum([All Interface]![Credit Amount]-[All Interface]![Debit Amount]) AS Amount
FROM [SOF to FC] INNER JOIN ([InPatient OutPatient] INNER JOIN [All Interface] ON [InPatient OutPatient].Account = [All Interface].[Nat Account]) ON [SOF to FC].SOF = [All Interface].[Source of Fund]
WHERE ((([All Interface].[Nat Account])>"40000" And ([All Interface].[Nat Account])<"49999") AND (([All Interface].[Service Date])<#1/1/2024#))
GROUP BY [All Interface].Entity, [All Interface].[Intfc Date], [All Interface].[HAR (Hospital Account Record)], [All Interface].[Nat Account], [All Interface].[Source of Fund], [All Interface].[Service Date];

And then this would be the way it mapped it to a variable for vba in excel:

sQRY = "SELECT [All Interface].Entity, [All Interface].[Intfc Date], [All Interface].[HAR (Hospital Account Record)], [All Interface].[Nat Account], [All Interface].[Source of Fund], [All Interface].[Service Date], Sum([All Interface]![Credit Amount]-[All Interface]![Debit Amount]) AS Amount" & _
"FROM [SOF to FC] INNER JOIN ([InPatient OutPatient] INNER JOIN [All Interface] ON [InPatient OutPatient].Account = [All Interface].[Nat Account]) ON [SOF to FC].SOF = [All Interface].[Source of Fund]" & _
"WHERE ((([All Interface].[Nat Account]) > ""40000"" And ([All Interface].[Nat Account]) < ""49999"") And (([All Interface].[Service Date]) < #1/1/2024#))" & _
"GROUP BY [All Interface].Entity, [All Interface].[Intfc Date], [All Interface].[HAR (Hospital Account Record)], [All Interface].[Nat Account], [All Interface].[Source of Fund], [All Interface].[Service Date];"

Here is the entirety of the vba code I was trying to run:

Sub Query_Dbs()
Dim cnn As ADODB.Connection ' connection
Dim rs As ADODB.Recordset ' record
Dim sQRY As String ' SQL statement
Dim strFilePath As String 'pathway to the database
Dim sPath As String ' pathway to the workbook
Dim i As Integer
For i = 2 To 37
    Sheets("Sheet" & i).Select
    sPath = Sheet1.Cells(i, 1).Value
    strFilePath = Sheet1.Cells(i, 1).Value & Sheet1.Cells(i, 2).Value
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Provider = "Microsoft.Ace.OLEDB.12.0"
    cnn.Properties("Data Source") = strFilePath
    cnn.Open

    sQRY = "SELECT [All Interface].Entity, [All Interface].[Intfc Date], [All Interface].[HAR (Hospital Account Record)], [All Interface].[Nat Account], [All Interface].[Source of Fund], [All Interface].[Service Date], Sum([All Interface]![Credit Amount]-[All Interface]![Debit Amount]) AS Amount" & _
"FROM [SOF to FC] INNER JOIN ([InPatient OutPatient] INNER JOIN [All Interface] ON [InPatient OutPatient].Account = [All Interface].[Nat Account]) ON [SOF to FC].SOF = [All Interface].[Source of Fund]" & _
"WHERE ((([All Interface].[Nat Account]) > ""40000"" And ([All Interface].[Nat Account]) < ""49999"") And (([All Interface].[Service Date]) < #1/1/2024#))" & _
"GROUP BY [All Interface].Entity, [All Interface].[Intfc Date], [All Interface].[HAR (Hospital Account Record)], [All Interface].[Nat Account], [All Interface].[Source of Fund], [All Interface].[Service Date];"
   
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    Application.ScreenUpdating = False
    ActiveSheet.Range("A6").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
Next i
End Sub

I then use another macro to combine the data from sheet2 to sheet37 into a single tab of data in case anyone was wondering.

I ended up manually opening all 36 databases, creating a new query, pasting in the sql, running the query and copying and pasting the results manually into excel which took about an hour and a half. I'd really like to know how to avoid that next time if I run into this again.

I am pretty good in excel but not so great at access, if someone could help me figure out why the query would not run in excel and how to change it, I would appreciate it. Thanks.

 

[RoryA]

You appear to be missing some spaces at the end of your lines so words are being concatenated together like "Amount" at the end of the first line and "FROM" at the start of the second.

 

[Legal Tender]

Yeah that solved it for me, thanks. I thought it might be something trivial but I didn't think it was that stupid of me. Thanks again, its an honor to have my question answered by you, I peruse the boards a lot and see you answering stuff all the time. Thanks for making this community great.