Automate response to Run-time error 3146 ODBC--call failed

OK, so far I've uninstalled & re installed Office-2010 3 to 4 times, done hours of research for 3 days with no success. I started getting this error either from Vbscript or Excel VBA, performing the same command that has worked for months. I am not sure what I may have changed, I don't recall changing anything that could cause it but I nailed it down to the ReadOnly:=True parameter. I think something may be wrong with the registry... but re-installing should fix it right? It didn't...

Anyways, very simple command that I recorded on Excel 2010. This command WORKS FINE and the file opens:

Sub Macro1()      Workbooks.Open Filename:="C:\temp\file_9928_131101.xlsx" End Sub 

But when I add the ReadOnly:=True parameter, it does NOT WORK:

Sub Macro1()      Workbooks.Open Filename:="C:\temp\file_9928_131101.xlsx", ReadOnly:=True End Sub 

This is the returned error Run-time error '1004' Method 'Open' of object 'Workbooks' failed:

When I click Debug, the error is at the only line of code.

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

I may have a workaround as a temporary fix, but this is going to cause problems if I can't get it fixed for good.

I have a query in MS Access that works just fine when it's run on its own, but when I run it using the CurrentDb.Execute command, it gives the error "Run-time error '3146': ODBC--call failed."

I also checked if it fails if it's run from the Immediate window, and it does fail then, too.

The query calls from three different tables plus one local table that was designed by the VBA script. The VBA script works if one particular table is removed.

I know this is very generic, but why would the query work everywhere except if it's called from VBA?

UPDATE: The original question was poorly worded, and I apologize for that. It was written on-the-fly and I was frazzled by the problem I was having.

Here, I think, is the problem in a nutshell: If VBA calls a query that pulls from too many tables (3 or more) on my company's server, it gives the "call failed" error. If the query is just double-clicked, it runs fine.

Now that I'm thinking about it, could it be a server-side issue?

The workaround that I've put in place is multiple queries that create local tables, then running the original query pointing to the local tables instead of the server tables. It works, but is a very time-consuming process, both in writing the code and running the code. For this specific project that I'm doing right now, it'll work, but it would really be good to be able to get this working properly.