External Connections In Excel

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.

JulieSenior Analyst/ProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:

Have you tried directly connecting to SQL Server from Excel instead of going through Access?
Jeffrey CoachmanMIS LiasonCommented:
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

JulieSenior Analyst/ProgrammerAuthor Commented:
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.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NorieAnalyst Assistant Commented:
What are you using Excel for?
JulieSenior Analyst/ProgrammerAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
Access can create Graphs and Pivot tables as well.

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

NorieAnalyst Assistant Commented:
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...
JulieSenior Analyst/ProgrammerAuthor Commented:
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.


Jeffrey CoachmanMIS LiasonCommented:
JulieSenior Analyst/ProgrammerAuthor Commented:

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.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JulieSenior Analyst/ProgrammerAuthor Commented:
Solution resolved the issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.