VBA Pass Thru to SQL query

[HCB]

I am new to VBA programming and I would like to run a pass-thru query (to SQL Server). I have the following code developed that compiles and executes without error, but I am not sure it does anything (at least not that I can see.

I run the code using a Macro without error, but also without result.

Public Function ExecuteSQL()
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

sSQL = "SELECT Top 10 dbo.tbl_pm_active.key_patient_2 FROM dbo.tbl_pm_active"

Set cn = New ADODB.Connection
cn.Open "DSN=DW_Vista" 'from ODBC setup

Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic

End Function

--
I run the same query in Access as a pass thru query and receive the ten records as planned:

This task is my starting point as I eventually want to use a local table as a filter for the individual records (Attached excel file).

Any suggestions would be appreciated.

 

[mdlueck]

This task is my starting point as I eventually want to use a local table as a filter for the individual records (Attached excel file).

Any suggestions would be appreciated.

Then you will need to use DAO.QueryDef's for your query and not ADO objects if you want the query results to end up in an Access table.

You will need to nest two DAO.QueryDef objects - one inside the other.

The inner one is set to Pass-Through mode and sends the query to SQL Server.

The outer wrapper selects everything from the inner DAO.QueryDef and maps the records to an Access table.

I have written this solution up here:
"Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them"
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

 

[HCB]

thanks for your suggestions. I will need to study this as I need to learn a lot more.

 

[mdlueck]

You are most welcome, HCB.