can passthrough queries use ADO connection

[CJ_London]

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?

 

[Galaxiom]

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.

 

[Minty]

Total shot in the dark, have you tried using the sqloledb connection string to connect in ADO ?

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;
Integrated Security=SSPI;

This implies a pretty old version of SQL server?

 

[CJ_London]

@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