Coding of a query using QueryTable

[j_Southern]

I am trying to download data from Sage Line100 using vba. If I set about this using MSQuery I can down load the data I want but I have not been able to code this.
My best attempt so far is

Sub DownLoad()
      SQLStr = "SELECT SELECT SALES_LEDGER.ACCOUNT_NUMBER, SALES_TRANSACTIONS.SALES_CONTROL_VALUE "
      SQLStr = SQLStr & "FROM ACCOUNTING_SYSTEM.SALES_LEDGER SALES_LEDGER, ACCOUNTING_SYSTEM.SALES_TRANSACTIONS SALES_TRANSACTIONS "
      SQLStr = SQLStr & "WHERE SALES_LEDGER.THIS_RECORD = SALES_TRANSACTIONS.PARENT_RECORD "
      SQLStr = SQLStr & "AND ((SALES_TRANSACTIONS.TRANSACTION_DATE>={d '2017-01-01'}))"
   
    NewSQL "DownLoad", SQLStr
      
End Sub
Sub NewSQL(ShName, SQLStr)
Sheets(ShName).Visible = True
Const SAGECONN1 As String = "ODBC;DSN=SAGE LINE 100;UID=test;;"
Dim qtSage As QueryTable
Dim rngDest As Range
'Dim strSQL As String

       Set rngDest = Sheets(ShName).Range("A1")
        Set qtSage = Worksheets(ShName).QueryTables.Add(SAGECONN1, rngDest, strSQL)
          qtSage.RefreshStyle = xlOverwiteCells
          Sheets(ShName).Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False ********
       
End Sub

This ends with the error message "Object variable or With block not set" at the line marked********
I have not been able to resolve this and would welcome some help
John

[Norie]

What happens if you remove that's line?

[CK76]

Try below (replace the line marked with ********)

qtSage.Refresh BackgroundQuery:=False

[j_Southern]

Norie andCK76
Thanks for your interest. If I remove the asterisked line then nothing happens and no data is returned.
If I replace the line as suggested by CK76 then I get the error message "Incomplete Data Source"
John

[j_Southern]

I have changed the code to

Sub NewSQL(ShName, SQLStr)
Sheets(ShName).Visible = True
'Const SAGECONN1 As String = "ODBC;DSN=SAGE LINE 100;UID=test;;"
Const SAGECONN1 As String = "ODBC;DSN=SageLine100;UID = test;;"
Dim qtSage As QueryTable
Dim rngDest As Range
'Dim strSQL As String

       Set rngDest = Sheets(ShName).Range("A1")
        Set qtSage = Worksheets(ShName).QueryTables.Add(SAGECONN1, rngDest, SQLStr)
          qtSage.RefreshStyle = xlOverwriteCells
          qtSage.Refresh BackgroundQuery:=False
End Sub

This includes the correction of 2 typos.(SQLStr and xloverwrite) and now leads to the error message "Authorisation Failure"
I have often used this kind of code to get data from Sage Line 100,and cannot see why its not working now.
John

[CK76]

From the error, it's likely user permission level. Never dealt with Sage Line 100, so can't say for sure, but it's usually combination of User permission table setting and INI setting.

[j_Southern]

I am going to have to admit that I have discovered that the cause of my problem was another typo in SQLStr. (SELECT SELECT)
Correcting that allowed the expected data to download.
Many apologies for wasting everyone's time. I have marked this thread as solved. The next one is about the coding of parameters and I have checked that I have not made the same crass mistake.
John

[Norie]

John

I think we should have seen that too.