SQL Server Management Studio + MS Excel/Access Files?

[mc84excel]

I'm interested in using SQL Server Management Studio to run queries on an Excel or Access file

I tried googling but most of the results confuse this with similar-but-different concepts (see bottom of this post). The few remaining results didn't provide enough information for me to get this to work.

Is there any forum user who could kindly inform me how I can do this?


To avoid any confusion, I would like to stress that this thread is NOT about:

1. importing an Excel or Access file into SQL Server Management Studio
2. connecting to a database from VBA using ADO

I know how to do the above two and they are not what I am looking for.

[Kyle123]

The only ways I can think of is through using a linked server that acts as a proxy https://msdn.microsoft.com/en-GB/library/ms188279.aspx or, on a more ad-hoc basis using something like opendatasource

[mc84excel]

Good to hear from you again, Kyle

This sounds promising. Thank you +1

I will leave this thread open while I look into this. If I run into any major problems, I will update the thread.

[mc84excel]

I have been told by the friend I asked to do the setup that using a linked server isn't possible unless both Office & SQL Studio are 64 bit? (I installed my Excel as 32 bit so I could keep using MZTools!)

Does anyone know if this is so and if yes, is there any workaround for it?

I had a go myself and I managed to get a connection to a test excel workbook but none of the sheets showed up under Tables???

[Kyle123]

Did you try running a query even though you couldn't see the tables listed?

[mc84excel]

Did you try running a query even though you couldn't see the tables listed?

I confess I didn't think of trying something like that.

(Trys it). No good. It gives an error message: " Invalid object name 'tablenameexample'. "

I overlooked mentioning before that the connected workbook doesn't appear under Databases in SQL Studio. It appears under 'Server Objects' -> 'Linked Servers' -> TEST (TEST is the name of the connection I gave to the excel workbook).

If I right click the linked server 'TEST' and choose 'Test Connection' I get "The test connection to the linked server succeeded."

If I right click Tables (under ServerObjects>LinkedServers>TEST>Catalogs>default>Tables) and choose Refresh, I get an error message:

Failed to retrieve data for this request (Microsoft.SqlServer.Management.Sdk.Sfc)

Additional information:
L>An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
L> Cannot obtain the required interface("IID_IDBSchemaRowset") from OLE DB provider "ADsDSOObject" for linked server "TEST". (Microsoft SQL Server, Error: 7301)