Solved

Excel Connnection to SQL through ODBC

Posted on 2014-07-24
6
350 Views
Last Modified: 2014-07-25
Hello:

My new Windows 8 laptop will not let me attach files.  When I tried, it closed Internet Explorer.  Unfortunately, then, I will just have to explain the issue as best as I can.  

I created an Excel spreadsheet which pulls in data from SQL.  So, I have both an Excel spreadsheet and a .odc file.  

The customer wanted the SQL query revised.  So, I revised it.  When I run the query in SQL, it executes and displays data perfectly.

But, when I paste the query into the Excel spreadsheet, replacing the original query, I get the following error upon refreshing Excel:

The query did not run, or the database table could not be opened.  Check the database server or contact your database administrator.  Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.

Any ideas?

Thanks!  Much appreciated!

TBSupport
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40218502
> When I run the query in SQL, it executes and displays data perfectly.

What is "in SQL", exactly? SSMS? Excel Query Designer? something else?
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40219319
SSMS
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40219396
Try in query designer.

The easiest is probably to delete the query from excel, and then recreate it from scratch.

In my own queries, I make a trick to make excel independent from ODBC element After the workbook is ready, I open it with hex editor, find odbc description, and specify it there as DSN-less connecton (http://www.connectionstrings.com/microsoft-sql-server-odbc-driver/). After that, I can send the workbook to someone else and it will work.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 10

Accepted Solution

by:
FamousMortimer earned 500 total points
ID: 40219432
I think by 'in SQL' he means Microsoft Query.

Can you try recreating the connection in the workbook and the ODBC connection?  Maybe that will point you to the problem.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40220064
I couldn't get it to work.  I'll just move on to something else, as usual.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40220067
So, this question can go to history, I'll give props to somebody.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question