Categories: sql-server, excel, vba, excel-vba

run time error 1004 general odbc error refresh backgroundquery false

2 answers

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

All answers to this question, which has the identifier 27172202

The best answer:

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

Thanks Matt

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" 

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type com.google.firebase.iid.zzav is referenced as an interface from com.google.firebase.messaging.zzd”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data