Solved

External Connections In Excel

Posted on 2012-03-22
11
3,997 Views
Last Modified: 2012-03-31
Hi, hope somebody can help.

Let me explain the steps I've taken so far :

I have linked two SQL Server 2000 tables into an Access 2010 database using ODBC; I can open them and see the content

Great, the ODBC is working OK, Windows authentication is accepted and SQL Server recognizes the fact I have permission to Select from the tables.  OK, next steps ...

Create an Access query using the two tables; I can open it and see the content

Nothing wrong with the query.  Next ...

In Excel 2010, from the Data tab, choose "From Access", select my database, select my new query from the list provided, click OK and hey presto .... I get an error ...

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 hsn't been moved or reorganized, then try the operation again.

Now, I wouldn't mind if this happened every time, but it doesn't.  I can link a table one day and attempt to refresh the data another day (well, actually, I might try a couple of later) and get the error.  It's infuriating.  I've tried all of the following ...

Created a User DSN using the SQL 2000 driver
Created a User DSN using the SQL 2008 Native driver
Created System DSN's for both drivers
Created File DSN's for both drivers
Created 32bit DSN's in all the flavours and all the colours using SysWow64
Tried using a SQL account instead of network authentication
Even tried re-creating the above but linking to a SQL 2008 server

All of the above show inconsistent results.  It does work, it doesn't work. Yes, no.  Can, can't.  It's driving me potty.  I have a domain account which has Domain Administrator privileges.  This is all from my own PC ... can't wait to try this on another users' PC!

It's either something really basic I'm missing or some other factor that is having an impact on this scenario.

We are intending to port our databases to SQL Server 2008 later this year, but at the moment, I need to try and get this working using SQL Server 2000.

I would also point out that my colleague has Office 32 bit installed on his Windows 7 Pro 64bit PC and he doesn't have any problems whatsoever.

Any guidance would be greatly appreciated.

Thanks in advance.

Julie
0
Comment
Question by:AHJ2008
  • 5
  • 3
  • 3
11 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37752942
Julie

Have you tried directly connecting to SQL Server from Excel instead of going through Access?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37752970
^
Yeah, ...and from the other end,...What's the need for Excel?

So what we both are saying is :
The more "Applications" you have involved, the more issues like this keep cropping up...

So instead of: SQL-->Access-->Excel
You could have just : SQL-->Excel
Or: SQL-->Access

JeffCoachman
0
 

Author Comment

by:AHJ2008
ID: 37753021
That was quick!

Yes, I can connect directly to the SQL Server from Excel.  The only reason I am using Access is because the initial query was raised by our corporate department, actually during training, when they ported one of their MDB's to ACCDB.  At least this way, we have control over what tables they have access to, but give them the freedom to create their own queries.

Thanks
0
 
LVL 33

Expert Comment

by:Norie
ID: 37753053
What are you using Excel for?
0
 

Author Comment

by:AHJ2008
ID: 37753558
It's the pretty stuff that Excel does with tables, charts, graphs etc, and in this particular scenario, Pivot Tables.  It's a senior management thing.  

With linked tables, the theory was that it should be a one-click refresh of data rather than an export from Access first.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37755638
Access can create Graphs and Pivot tables as well.

I mean, ...if it's not working in Excel, ...then how "pretty" is it?
;-)

Jeff
0
 
LVL 33

Expert Comment

by:Norie
ID: 37755644
Why not 'pull' the data from Access into Excel using ADO/DAO and use it to create the charts/pivot tables etc?

Or just 'pull' the data from SQL Server using ADO and use it to...
0
 

Author Comment

by:AHJ2008
ID: 37756369
boag2000: agreed, it can be done in Access, but everyone has Excel, not Access; "super users" have the ability to create the queries, then anyone can use the results to create their pretty stuff in whatever format/color they see fit for their senior management.  They can do it by importing the data, but the facility is supposed to be there to link to it.

imnorie: don't mean to be thick, but what exactly do you mean by using ADO/DAO?  I don't want to write macros or VB code.

Thanks again.

Regards

Julie
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37757141
OK
0
 

Accepted Solution

by:
AHJ2008 earned 0 total points
ID: 37770259
Resolved.

Instead of using the "From Access" ribbon button, I used "From Other Sources" and invoked the Data Connection wizard.  From there, I chose ODBC DSN, picked MS Access Database and selected the Access database, ensuring I didn't link to a specific table.

Now, I can use the "Existing Connection" button in any workbook/sheet and choose any query/table in the database.

Thanks for all the comments and suggestions.

Regards

Julie
0
 

Author Closing Comment

by:AHJ2008
ID: 37790552
Solution resolved the issue.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
microsoft access - xml 10 29
Slow SQL query 12 26
3rd level dependant list 4 33
SQL Query for Periods 3 0
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn how the fundamental information of how to create a table.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now