Displaying a result set from a stored procedure with multiple entries

[clukem]

I have a query (below), which lists the phones connected to particular desk.




Private Sub DeskSearch_Click()
Dim db As Database
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

Set cnn = New ADODB.Connection

cnn.ConnectionString = "Driver={SQL Server};Server=LAPTOP-A35N8041;Database=PhoneDB;Trusted_Connection=yes"

cnn.Open

Set rs = New ADODB.Recordset
Set rs = cnn.Execute("Exec dbo.DeskSearch '" & [Location] & "'")


Prior to moving the tables to an SQL server, I could do an SQL query to return the data. I have a stored procedure on the server called DeskSearch



When the result is run the output is like





I've got the code as far as being executed, but not sure how to display it similar to above. Has anyone any ideas



Regards


Chris

[NoellaG]

Have you already tried to use an ADODB.command object for this?

[pbaldy]

You could use a pass through query and make it the source of a form/report for viewing. I feel like I've heard you can set a report's source to your recordset, but not sure off the top of my head.

[Welshgasman]

Like what?
I do not see anything?

You cannot paste pictures on this site, but need to attach them.

[CJ_London]

I feel like I've heard you can set a report's source to your recordset

You can't with an ado recordset, only a dao recordset

[pbaldy]

You can't with an ado recordset, only a dao recordset

Ah, thanks for the clarification. I'd use a pass through query, so haven't tried this.

[CJ_London]

To clarify that was referring to ADO and reports. You can use ADO to populate forms (which can be datasheet forms), combo's or listboxes. But be aware that with forms, sorting and filtering using the access built in functionality will fail - you need to build your own.

Not tried using a passthrough query for reports

[clukem]

Attachments added of code and output. I've seen a comment that an access pass through query cannot take parameters

[clukem]

This is the response when using pass through

[Minty]

You Can't pass the form reference to SQL server it has no clue what it is.
You need to pass the actual value into the pass through .

    Dim db As DAO.Database
    Dim qDef As QueryDef
    Dim StrSQl as string

    StrSQL = "SELECT YourFields FROM Phones WHERE Location = '" & Forms!DeskSearch!Location & "'"


    Set db = CurrentDb()
    
    Set qDef = db.QueryDefs("Your_Saved_PT_Query")
    qDef.Connect = db.TableDefs("A_SQL_LinkedTable").Connect
    qDef.SQL = StrSQL 
    qDef.ReturnsRecords = True
    qDef.Close 


    Set qDef = Nothing
    Set db = Nothing

You can now use Your_Saved_PT_Query as the recordsource.

However, unless your table has millions of rows, simply link to it and create a local query to do the filtering.
This isn't a efficient use of a pass through, it's only one table filtered.

[pbaldy]

Minty has already addressed your error, but I should have clarified that I'd have started with linked tables and an Access query too. My rule of thumb is to use Access queries until performance dictates otherwise.

[clukem]

You Can't pass the form reference to SQL server it has no clue what it is.
You need to pass the actual value into the pass through .

    Dim db As DAO.Database
    Dim qDef As QueryDef
    Dim StrSQl as string

    StrSQL = "SELECT YourFields FROM Phones WHERE Location = '" & Forms!DeskSearch!Location & "'"


    Set db = CurrentDb()
    
    Set qDef = db.QueryDefs("Your_Saved_PT_Query")
    qDef.Connect = db.TableDefs("A_SQL_LinkedTable").Connect
    qDef.SQL = StrSQL 
    qDef.ReturnsRecords = True
    qDef.Close 


    Set qDef = Nothing
    Set db = Nothing

You can now use Your_Saved_PT_Query as the recordsource.

However, unless your table has millions of rows, simply link to it and create a local query to do the filtering.
This isn't a efficient use of a pass through, it's only one table filtered.

I originally had just an access database with all the data in there. Then I was asked to keep the data on a SQL server. So, linked the tables using migration assistant. I was just trying to get as much as possible in stored procedures on the SQL server. Where people can't easily reach. Wil have to take a look at QDEF at some point

[pbaldy]

I use a function that "fixes" the SQL of pass through queries so I only need the QueryDef code in one place. I got it from a book but I think this is the same code:

From Access to SQL Server – SQLServerCentral Forums


The PassThroughFixup function about half way down. On SQL Server I started using table-valued functions as much as stored procedures, since they can be used in a FROM clause.