SQL Syntax Error? - Background Query= False

[bs0d]

I'm using Excel (2010) to get data from a SQL database. I setup the sheet to allow user input before executing the query. But even when the query is hard-coded with static values I'm getting the following error:

Runtime Error '1004' - SQL Syntax Error

And it highlights this line in VBA:

.Refresh BackgroundQuery = False

If I comment out the line, the query doesn't execute. Anyone deal with this issue before? I'm sure it's something simple. Here's my query:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=xxxx;Description=xxxxx;UID=me;APP=Microsoft Office 2010;WSID=DHVXBC1;DATABASE=database;Trusted_Connection=Yes" _
        , Destination:=Range("$B$9")).QueryTable
        .CommandText = Array( _
        "SELECT dbo.AC_PROPERTY.LEASE, dbo.AC_PROPERTY.MTR, dbo.AC_PROPERTY.SOMETHING, " _
        , _
        "avg(dbo.AC_DAILY.DATA), Avg(dbo.AC_DAILY.DATA2), avg(dbo.AC_DAILY.DATA3), Avg(dbo.AC_DAILY.DATA4), avg(dbo.AC_DAILY.DATA5), Avg(dbo.AC_DAILY.DATA6), dbo.AC_PROPERTY.LEASENO" _
        , _
        "FROM dbo.AC_DAILY INNER JOIN dbo.AC_PROPERTY ON dbo.AC_DAILY.PROPNUM = dbo.AC_PROPERTY.PROPNUM" _
        , _
        "WHERE (((dbo.AC_DAILY.D_DATE)>='1/1/2011' And (dbo.AC_DAILY.D_DATE)<'2/1/2011')) AND dbo.AC_PROPERTY.STATE = 'KS' AND dbo.AC_PROPERTY.CAT = '1'" _
        , _
        "GROUP BY dbo.AC_PROPERTY.LEASE, dbo.AC_PROPERTY.MTR, dbo.AC_PROPERTY.SOMETHING, dbo.AC_PROPERTY.LEASENO" _
        , _
        "HAVING avg(dbo.AC_DAILY.DATA) <= '50'" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Query"
        .Refresh BackgroundQuery:=False
    End With

 

[Andrew Poulsom]

Remove the colon.

 

[bs0d]

Wow, that came across in the macro record, good catch!

Now i've got some syntax errors to correct i guess. It's saying error near '.' and near 'dbo' ...

 

[bs0d]

Remove the colon.

I'm running into a problem with executing code before the query can return any results. I found a link where you can move the cursor to the end of the results (http://bytes.com/topic/access/answers/624404-how-do-you-make-vba-wait-query-finish like this:

' Open Recordset object with SQL statement. 
   Set rstOrders = dbsNorthwind.OpenRecordset(strSQL) 
  
   'This forces the query to finsh and moves the curson to the last record 
   retOrders.MoveLast 
  
   'Now I can continue execution of my code

Considering my above query, how do i set the reference to my query? so that I can use "moveLast" ?

 

[Andrew Poulsom]

I thought you had set BackgroundQuery to False. Doesn't that pause the macro until the Refresh is finished?

 

[bs0d]

Does it? It must not be working; any ideas how to test to confirm? Because I'm also having a problem where the query is showing the column headings for all items in my GROUP BY clause (eventhough I tell it not to display column headings); but if I refresh the query, then they disappear.

So I tried to add a refresh to then end of the query code,

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

and it gives an error:

object variable or with block variable not set.

But If I execute this independently after I can confirm the query has returned data, it works just fine, even wit the BackgroundQuery:= (that's how the macro records it) Hmmm.

 

[chuckchuckit]

About 10 lines from the bottom you have:

.BackgroundQuery = True

Change it to False and that should fix it. Even though you have the 2nd line from the bottom set to False, change the other one to False also.

When set to True it will not wait for data to return from the query to start processing the rest of the code.

 

[bs0d]

I think it's working the way I would expect now, thanks for the help guys.