Excel 2007 - 1004 Run-time error Refresh Query Table

2
votes

It appears that this error is so generic that many of the existing solutions address different issues.

In my case I have created a macro that works in Excel 2010 and does not work in Excel 2007.

Here is a summary of the code:

cn = "ODBC;Driver={SQL Server Native Client 10.0};Server=serverName;Database=dbName;Trusted_Connection=yes;"
sql = "select top 10 * from tableName"

Dim S As Worksheet
Set S = ActiveWorkbook.Sheets("Medical")

With S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1"))

        .CommandText = sql
        .Refresh BackgroundQuery:=False

     End With

This code executes perfectly in Excel 2010, but errors out in Excel 2007.

Do I need to change the connection string structure for 2007, or is there another issue at work?

5
excelvbaodbcruntime
Error 1004 is generic error but do you have the actual error text? Should say something like Runtime error 1004: followed by more description - that will help narrow down the problem. - AxGryndr
Run-time error '1004': General ODBC Error - lance
Is there a query in range B1 of the Medical sheet? If you right click in the cell you should see the refresh option. - AxGryndr
It does in 2010, but not in 2007. - lance
Instead of .Commandtext can you try changing S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1")) to S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1"), Sql:=sql)? - AxGryndr

5 Answers

3
votes

Solution:

All machines I was using with Excel 2010 have "SQL Server Native Client 10.0" as a possible driver for ODBC Data Sources. The machines with Excel 2007 only have "SQL Server".

I changed my connection string to be:

cn = "ODBC;Driver=SQL Server;Server=serverName;Database=dbName;Trusted_Connection=yes;"

and it worked like a charm.

Thanks to those that responded.

0
votes

Change your property .Refresh BackgroundQuery:=False to be .Refresh. Nothing has changed from 2007 to 2010 in regards to this so if changing the property does not fix the issue there is something else going on such as part of the workbook is corrupt.

0
votes

I had the same issue when calling stored procedure from the macro. After wasting a whole day in searching internet for the solution, I finally found a solution.

I used SET NOCOUNT ON;

It worked!

0
votes

Unable to add a comment so this is to address the question posed above.

This answer saved me a lot of time.

MSFT documentation on this: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql

Below is an implementation.

create proc yourSproc

as

set nocount on;

begin

    --Contents of sproc

end
0
votes

I had this exact error. The debug would take me to the .refresh line as well. I worked every which way I could think of to fix that line. Nothing worked.

The "General ODBC Error" verbiage had me thinking...I went to Run >> Microsoft ODBC Administrator >> Selected my DataSource >> Clicked the Configure button >> Clicked Test Connection

That returned an error that my password had expired and the database was set to read only. My Windows User password has not expired but it was within the window of when I "should" change my password...I was using a work machine and have mandatory user account password changes every 60-90 days or so with a 30-something day grace period. Didn't even know that.

Not saying this is the fix for you, but I was getting the exact same error and had tried every little trick and edit this website, and the Internet, had to offer with no luck. I logged out, reset my Windows User Account password, logged back in, opened the excel file, hit the Refresh Button macro...and it worked.

Anyway, maybe this will help someone...