can passthrough queries use ADO connection (1 Viewer)

I believe the answer is no, but just wanted to check. Or perhaps there is another way?

I have a requirement to link a local access table to a recordset returned by a sql server stored procedure (all communication with sql server is via stored procedures). My thinking was to return this via a passthrough query, then in another query I can join the two tables together.

This works if I use an ODBC connection, however the client requires the use of SQLOLEDB when communicating with SQL Server - users do not have the relevant ODBC drivers installed on their machines and it is unlikely I can persuade the client otherwise.

Documentation on passthrough queries all refer to using ODBC (and the property is called 'ODBC Connect Str' which is why I think it is not possible - I've tried using the ADO connection string but get an 'error with connection string' message.

I believe QueryDefs are a DAO object so I cannot assign a ADO recordset to one so far as I can see

At the moment I am returning the ADO recordset then looping through it to populate a local table and then joining the local table to that - it is the 'standard' way of doing it.

Anyone have any thoughts?

A random thought.

Excel can populate a table on a sheet with a recordset via an OLE connection. Access can link an Excel sheet.

I have never actually tried to link Access to an OLE table in Excel but if there was a problem, Excel could be automated to convert the table to a range.

Certainly not elegant but could be easily addressed if implemented as a Class.

Would not bet on the performance being good but you never know til you try.

Total shot in the dark, have you tried using the sqloledb connection string to connect in ADO ?
This implies a pretty old version of SQL server?

@Galaxiom - interesting thought, I will investigate.

@Minty - that is the ADO connection string I am using at the moment - and yes SQL Server is 2008, and does not work in a passthrough query