run time error 1004 general odbc error refresh backgroundquery false

0
votes

I have a working VBA code which runs as:

wsEnd.Select

Range("A:AQ").Delete

    strSQL = "Select *
    strSQL = strSQL & " FROM [XXX].[ABCCustomer] As A"
    strSQL = strSQL & " Left join"
    strSQL = strSQL & " (Select * "
    strSQL = strSQL & " From [XXX]..[ABCCustomer]"
    strSQL = strSQL & " where LineageId = '123' ) B"
    strSQL = strSQL & " on a.product = b.product and a.[StartDate] = b.[StartDate]"
    strSQL = strSQL & " where (a.EndDate <> b.EndDate)"
    strSQL = strSQL & " and a.NewEndDate is NULL AND B.NewEndDate IS NULL"
    strSQL = strSQL & " and a.Id = '456"
    strSQL = strSQL & " order by b.ProductType"

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DRIVER=SQL Server;SERVER=XXX\SQL01;UID=;Trusted_Connection=Yes;APP=2007 Microsoft  Office system;WSID=XXX;DATA" _
    ), Array("BASE=master")), Destination:=Range("$A$1")).QueryTable
    .CommandText = strSQL
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_from_XXX_C"
    .Refresh BackgroundQuery:=False

End with,

I have two other scripts starting after the End With but inside the same sub all using the same VBA just different SQL, which all work perfectly fine.

Then I have this very annoying forth, which is causing my a real headache, that goes as follows:

    strSQL = "Select *
    strSQL = strSQL & " FROM [XXX].[ABCCustomer] As A"
    strSQL = strSQL & " Left join"
    strSQL = strSQL & " (Select * "
    strSQL = strSQL & " From [XXX]..[ABCCustomer]"
    strSQL = strSQL & " where Id = '123' ) B"
    strSQL = strSQL & " on a.product = b.product and a.[StartDate] = b.[StartDate]"
    strSQL = strSQL & " where (a.EndDate = b.EndDate)"
    strSQL = strSQL & " and a.NewEndDate is Not NULL AND B.NewEndDate not NULL"
    strSQL = strSQL & " and a.Id = '456"
    strSQL = strSQL & " order by b.Product"

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DRIVER=SQL Server;SERVER=XXX\SQL01;UID=;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=XXX;DATA" _
    ), Array("BASE=master")), Destination:=Range("$A$1")).QueryTable
    .CommandText = strSQL
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_from_XXX_D"
    .Refresh BackgroundQuery:=False

End With End Sub

When running the code, the first three come back fine but the forth says "run time error 1004 general odbc error"

and stops the code at backgroundquery=false.

I have lifted the SQL code into SQL and it works perfectly fine there and have even tried running it on a seperate excel document and that doesnt give any joy.

The VBA is copy and pasted and only the list object table name is changed i.e. from C to D

I have tried to change backgroundquery:=false to background:=refresh, this works but I get a message saying "run time error 1004 This operation cannot be done because the data is refreshing in the background.

Please help!

Thanks Matt

2
sql-servervbaexcel
why are you resetting the variable value each line? – peege
Hi Are you refering to each strSQL = strSQL &? If so nothing more than to keep it easy to read through for colleaques etc. Other than that no other reason. – Matthew Hopkins
Just remarque: you are missing closing quotation mark after "Select * which makes the rest of code wrongly formated. – Radek
Hi Radek. You are absolutely correct, well spotted ☺. However I have changed various parts of the example provided to something slightly more generic as the select statement goes on a while. Please take my word that the unaltered code has the " at the end. I have literally copied and pasted the sql bit into sql and it works perfectly fine. I feel the error is not the sql but something else. Any further ideas are more than welcome – Matthew Hopkins

2 Answers

1
votes

The error went away after a computer reset. Really sorry, but thanks for all those who responded.

Thanks Matt

0
votes

This isn't an answer to the question. But it is relevant, since the reason for writing it like this was to make it easier to read.

Original Code: This does a nice job of lining up the characters to read the statement, but is redundant by defining the value of strSQL for every line item. 

strSQL = "Select *
strSQL = strSQL & " FROM [XXX].[ABCCustomer] As A"
strSQL = strSQL & " Left join"
strSQL = strSQL & " (Select * "
strSQL = strSQL & " From [XXX]..[ABCCustomer]"
strSQL = strSQL & " where LineageId = '123' ) B"
strSQL = strSQL & " on a.product = b.product and a.[StartDate] = b.[StartDate]"
strSQL = strSQL & " where (a.EndDate <> b.EndDate)"
strSQL = strSQL & " and a.NewEndDate is NULL AND B.NewEndDate IS NULL"
strSQL = strSQL & " and a.Id = '456"
strSQL = strSQL & " order by b.ProductType"

Modified: Aside from the color formatting getting lost in translation. This tells anyone reading it that the variable is getting set once and eliminates redundant characters to have to scan over.

strSQL = "Select * " & _
         "FROM [XXX].[ABCCustomer] As A " & _
         "Left join " & _
         "(Select * " & _
         "From [XXX]..[ABCCustomer] " & _
         "where LineageId = '123' ) B " & _
         "on a.product = b.product and a.[StartDate] = b.[StartDate] " & _
         "where (a.EndDate <> b.EndDate) " & _
         "and a.NewEndDate is NULL AND B.NewEndDate IS NULL " & _
         "and a.Id = '456 " & _
         "order by b.ProductType"