Filtering records retrieved from SQL server

[NigelBishop]

Hi I'm hoping one of you lovely people will be able to assist.
I am migrating an Access backend to SQL server and have created a stored procedure for retrieving records.

[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]USE[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [IPTTracking]
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]GO
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000]/****** Object: StoredProcedure [Issues].[sp_Select_tabled] Script Date: 04/10/2019 10:40:52 ******/
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]SET[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]ANSI_NULLS[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]ON
GO
SET[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]QUOTED_IDENTIFIER[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]ON
GO
ALTER[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]PROCEDURE[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [Issues][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][sp_Select_tabled][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]@ref [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Nvarchar[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]20[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]AS
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]BEGIN
declare[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] @sql [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Nvarchar[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]4000[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]begin[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]transaction
[INDENT]set[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] @sql [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]N'select i.ID
, i.Reference
, i.AssignedOwner
from issue.T_IPTTrackerIssues as I
where dc.IPTGroup = @ref'
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]exec[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#800000][FONT=Consolas][SIZE=2][COLOR=#800000][FONT=Consolas][SIZE=2][COLOR=#800000]sp_executesql[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]@sql[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]N'@Ref Nvarchar(20)'[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]@ref
[/INDENT][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]END
[COLOR=black]

[/COLOR]
I've entered the code below into the On Load event of my continuous form and I'm able to return the correct rows into the form based on the value entered into the string.

[/COLOR]
[INDENT]Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB.1;Initial Catalog=IPTTracking;Data Source=PHE-3J021434H;Integrated Security=SSPI;"
 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
rs.CursorLocation = adUseClient
rs.Open "EXEC issues.sp_Select_tabled '[B]QC[/B]'", conn
Set Me.Recordset = rs
[/INDENT]

So far so good.

I need to pass a variable into the EXEC string and have altered the code as below and once the code works I'll change the variable to refer to a dropdown field

[/COLOR]
[INDENT][COLOR=black]Dim conn As ADODB.Connection
[COLOR=red][B]Dim strArea As String[/B][/COLOR]
Set conn = New ADODB.Connection
[/COLOR][COLOR=red][B]strArea = "QC"[/B][/COLOR]
[COLOR=black]conn.Open "Provider=SQLOLEDB.1;Initial Catalog=IPTTracking;Data Source=PHE-3J021434H;Integrated Security=SSPI;"[/COLOR]
[COLOR=#000000][/COLOR] 
[COLOR=black]Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset[/COLOR]
[COLOR=#000000][/COLOR] 
[COLOR=black]rs.CursorLocation = adUseClient[/COLOR]
[COLOR=black]rs.Open "EXEC issues.sp_Select_tabled [COLOR=red][B]strArea[/B][/COLOR]", conn[/COLOR]
[COLOR=black]Set Me.Recordset = rs[/COLOR]
[/INDENT]

Unfortunately when I open the form this change causes no records to be returned, I'm guessing it is probably a syntax error in the
rs.Open "EXEC issues.sp_Select_tabled strArea", conn string but no matter what I try I'm unable to get it to work.
Any help would be gratefully received.

I know that I could probably use a passthrough query to achieve the same goal however the long term plan is to go for a web based interface at some point so I'd prefer to take this approach.

Thank you in advance
[/SIZE][/FONT][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

 

[Minty]

You need to concatenate your variable into the Exec statement ;

rs.Open "EXEC issues.sp_Select_tabled '" & strArea & "'", conn

If you built the exec string into a variable you would have seen it ;

sExec = "EXEC issues.sp_Select_tabled '" & strArea & "'"
Debug.Print sExec

rs.Open sExec , conn

 

[Galaxiom]

Using sp_executesql really isn't the right direction to be heading with your stored procedure.

 

[NigelBishop]

instead of sp_executesql what would you recommend?, as you might have guessed i'm new to this game

 

[NigelBishop]

A bit cheeky I know how do I make the change from
strArea = "QA"
to
strArea = [Forms]![F_IPTTrackerIssues1]!

• ?

 

[Minty]

Actually, in addition to Galaxioms statement, I'm unsure why you would be using a transaction for a simple select?

USE [IPTTracking]
GO
/****** Object: StoredProcedure [Issues].[sp_Select_tabled] Script Date: 04/10/2019 10:40:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Issues].[sp_Select_tabled]
@ref Nvarchar(20)
AS

SET NOCOUNT ON

BEGIN

SELECT i.ID
, i.Reference
, i.AssignedOwner
from issue.T_IPTTrackerIssues as I
where dc.IPTGroup = @ref

END

 

[Minty]

And to answer your second question, assuming you are on the form you are running this from;

Dim conn As ADODB.Connection
Dim strArea As String
Dim sExec as String

Set conn = New ADODB.Connection

[COLOR="Red"]strArea = Me.List[/COLOR]

conn.Open "Provider=SQLOLEDB.1;Initial Catalog=IPTTracking;Data Source=PHE-3J021434H;Integrated Security=SSPI;"
 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
rs.CursorLocation = adUseClient
sExec = "EXEC issues.sp_Select_tabled '" & strArea & "'"
Debug.Print sExec

rs.Open sExec , conn
Set Me.Recordset = rs

 

[NigelBishop]

Thank you all for your help, it all makes perfect sense now.