Runtime error 1004 general odbc error

It shows up that this error is so generic that many kind of of the existing options deal with various problems.

You watching: Runtime error 1004 general odbc error

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

Here is an overview of the code:

cn = "ODBC;Driver=SQL Server Native Client 10.0;Server=serverName;Database=dbName;Trusted_Connection=yes;"sql = "select height 10 * from tableName"Dim S As WorksheetSet S = ActiveWorkbook.Sheets("Medical")With S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1")) .CommandText = sql .Refresh BackgroundQuery:=False End WithThis code executes perfectly in Excel 2010, yet errors out in Excel 2007.

Do I should adjust the link string framework for 2007, or is there an additional worry at work?


excel vba odbc runtime
Share
Improve this question
Follow
edited May 6 "13 at 22:06
*

Undo♦
25.2k3737 gold badges102102 silver badges124124 bronze badges
asked Apr 22 "13 at 16:03
*

lancelance
5111 gold badge11 silver badge66 bronze badges
5
Add a comment |

5 Answers 5


Active Oldest Votes
3
Solution:

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

I adjusted my connection string to be:

cn = "ODBC;Driver=SQL Server;Server=serverName;Database=dbName;Trusted_Connection=yes;"and it functioned choose a cinjury.

Thanks to those that responded.

See more: How To Get Firefox Out Of Fullscreen, How Do I Back Out Of Full Screen Viewing


Share
Improve this answer
Follow
answered Apr 23 "13 at 1:44
*

lancelance
5111 gold badge11 silver badge66 bronze badges
Add a comment |
0
Change your building .Refresh BackgroundQuery:=False to be .Refresh. Nothing has readjusted from 2007 to 2010 in regards to this so if changing the residential or commercial property does not resolve the concern tright here is something else going on such as component of the workbook is corrupt.


Share
Improve this answer
Follow
edited Apr 22 "13 at 17:53
answered Apr 22 "13 at 16:17
*

AxGryndrAxGryndr
2,06211 gold badge1818 silver badges4343 bronze badges
2
Add a comment |
0
I had actually the exact same worry once calling stored procedure from the macro. After wasting a totality day in browsing internet for the solution, I finally discovered a solution.

I used SET NOCOUNT ON;

It worked!


Share
Improve this answer
Follow
answered Jul 2 "15 at 13:09
*

pkkpkk
1
1
Add a comment |
0
Unable to include 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.

See more: Dual Monitors Icons Keep Moving To Secondary Monitor In Windows 10

produce proc yourSprocascollection nocount on;start --Contents of sprocend
Share
Improve this answer
Follow
answered May 26 "17 at 14:24
JordanJordan
1
2
Add a comment |
0
I had actually this specific error. The debug would certainly take me to the .refresh line as well. I functioned every which way I could think of to settle that line. Nothing functioned.

The "General ODBC Error" verbiage had me reasoning...I checked out Run >> Microsoft ODBC Administrator >> Schosen my DataSource >> Clicked the Connumber switch >> Clicked Test Connection

That reverted an error that my password had actually expired and the database was set to read only. My Windows User password has not expired yet it was within the home window of as soon as I "should" adjust my password...I was making use of a work-related machine and also have actually mandatory user account password alters eextremely 60-90 days or so via a 30-somepoint day grace duration. Didn"t even know that.

Not saying this is the fix for you, however I was acquiring the precise exact same error and had actually tried eextremely little trick and also modify this webwebsite, and also the Net, had to sell with no luck. I logged out, recollection my Windows User Account password, logged back in, opened the excel file, hit the Refresh Button macro...and also it worked.