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.