Solved

External Connections In Excel

Posted on 2012-03-22
11
4,113 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

932 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

12 Experts available now in Live!

Get 1:1 Help Now