Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

External Connections In Excel

Posted on 2012-03-22
11
Medium Priority
?
5,065 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
[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
  • 5
  • 3
  • 3
11 Comments
 
LVL 34

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
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 34

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
 
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 34

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

705 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