Calls to stored procedure not firing (1 Viewer)

[liddlem]

Hi All
further to https://www.access-programmers.co.uk/forums/showthread.php?t=294892

I am have now converted the queries to stored procedures (in the back-end SQL server) as follows.
I have a SYSTEM DSN connection using the 'ODBC Driver 13 for SQL Server'
But they are not firing for some reason?
P.S. It seems that the ID_Subject is not being parsed through to the SP. How do I check that?

Quick summary of what should happen.
On my form, the user creates generic comments for student report results.
When they click an 'update' button, the relevant text in the comment field should be updated as per the SP.
NOTE : The comment field is RTF so i have had to consider that some text might have html tags.

The code under the button . . .

Private Sub Btn_Update_Comments_Click()
' These are calls to a MODULE - see below
    Call Stp_Sname(Me.Get_Subject)
    Me.Refresh
    Call Stp_Fname(Me.Subj_ID)
    Me.Refresh
    Call Stp_HIS_001(Me.Get_Subject)
    Me.Refresh
    Call Stp_Him_001(Me.Subj_ID)
    Me.Refresh
END SUB

SP_Calls Module . . .

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Integer

IdValueToProcess = MySubjID

Set qdf = CurrentDb.CreateQueryDef("")
qdf.ReturnsRecords = True
qdf.Connect = "ODBC;DSN=Demo;Trusted_Connection=Yes;"
qdf.SQL = "EXEC dbo.Stp_FIX_RPT_Sname " & IdValueToProcess

qdf.Close
Set qdf = Nothing
End Sub

Sub Stp_Fname(MySubjID As Integer)
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Integer

IdValueToProcess = MySubjID

Set qdf = CurrentDb.CreateQueryDef("")
qdf.ReturnsRecords = True
qdf.Connect = "ODBC;DSN=Demo;Trusted_Connection=Yes;"
qdf.SQL = "EXEC dbo.Stp_FIX_RPT_Fname " & IdValueToProcess

qdf.Close
Set qdf = Nothing
End Sub

Sub Stp_HIS_001(MySubjID As Integer)
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Integer

IdValueToProcess = MySubjID

Set qdf = CurrentDb.CreateQueryDef("")
qdf.ReturnsRecords = True
qdf.Connect = "ODBC;DSN=Demo;Trusted_Connection=Yes;"
qdf.SQL = "EXEC dbo_Stp_FIX_RPT_Comments_His_001 " & IdValueToProcess

qdf.Close
Set qdf = Nothing
End Sub

SP's created on the server

USE DEMO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 17/07/2017
-- Description:	Used to replace text in the FLX Academic Report Comment
-- =============================================
CREATE PROCEDURE Stp_FIX_FLX_RPT_Comments_Sname
	-- Add the parameters for the stored procedure here
	@ID_Subj int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE R
	SET R.Comment = REPLACE(rtrim(Comment), 'Sname', rtrim(S.Name_First))
	from FLX_RPT_Results R
	INNER JOIN STU_STUDENT S
	ON S.ID_Student = R.ID_Student
	WHERE R.ID_FLX_RPT_Subject = @ID_Subj
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 17/07/2017
-- Description:	Used to replace text in the FLX Academic Report Comment
-- =============================================
CREATE PROCEDURE Stp_FIX_FLX_RPT_Comments_Fname
	-- Add the parameters for the stored procedure here
	@ID_Subj int = 0
AS
BEGIN
--DECLARE @ID_Subj int = 11
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE R 
	SET R.Comment = REPLACE(rtrim(Comment), 'Fname', rtrim(S.Name_First) +' ' + rtrim(S.Name_Surname))
	from FLX_RPT_Results R
	INNER JOIN STU_STUDENT S
	ON S.ID_Student = R.ID_Student
	WHERE R.ID_FLX_RPT_Subject = @ID_Subj
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 17/07/2017
-- Description:	Used to replace text in the FLX Academic Report Comment
-- =============================================
CREATE PROCEDURE Stp_FIX_FLX_RPT_Comments_His_001 
	-- Add the parameters for the stored procedure here
	@ID_Subj int = 0
AS
BEGIN
--DECLARE @ID_Subj int = 11
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- His / Her
	UPDATE R 
	SET R.Comment = REPLACE(rtrim(Comment), 'his', 'her')
	from FLX_RPT_Results R
	INNER JOIN STU_STUDENT S
	ON S.ID_Student = R.ID_Student
	where S.Pers_Gender = 'Female' and 	R.ID_FLX_RPT_Subject = @ID_Subj

END
GO

-- =============================================
-- Author:
-- Create date: 17/07/2017
-- Description:	Used to replace text in the FLX Academic Report Comment
-- =============================================
CREATE PROCEDURE Stp_FIX_FLX_RPT_Comments_Him_001
	-- Add the parameters for the stored procedure here
	@ID_Subj int = 0
AS
BEGIN
--DECLARE @ID_Subj int = 11
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	UPDATE R 
	SET R.Comment = REPLACE(rtrim(Comment), ' him ', ' her ')
	from FLX_RPT_Results R
	INNER JOIN STU_STUDENT S
	ON S.ID_Student = R.ID_Student
	where S.Pers_Gender = 'Female' and 	R.ID_FLX_RPT_Subject = @ID_Subj
END
GO

-- =============================================
-- Author:
-- Create date: 17/07/2017
-- Description:	Used to replace text in the FLX Academic Report Comment
-- =============================================
CREATE PROCEDURE Stp_FIX_FLX_RPT_Comments_Him_002 
	-- Add the parameters for the stored procedure here
	@ID_Subj int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE R 
	SET R.Comment = REPLACE(rtrim(Comment), '>him<', '>her<')
	from FLX_RPT_Results R
	INNER JOIN STU_STUDENT S
	ON S.ID_Student = R.ID_Student
	where S.Pers_Gender = 'Female' and R.ID_FLX_RPT_Subject = @ID_Subj
END
GO

 

[Galaxiom]

You have not executed the querydef after setting its SQL.

qdf.Execute

I don't think you need the records retuned since it is an action.

I have only done this kind of thing by modifying an existing PassThrough query, so I'm not sure if you need more properties to be set too. (I used it to modify the parameters in the SQL for returning data from a Table Valued Function so it can be easily joined to other server tables using local queries.)

Typically, to just run a procedure on the server, I would instead use an ADOB Connection and Command with Parameters.

 

[Minty]

The pass through normally needs the variable in the EXEC statement for some reason, so this line should read;

qdf.SQL = "EXEC dbo.Stp_FIX_RPT_Fname  @ID_Subj = " & IdValueToProcess

Ditto with the others.

 

[Galaxiom]

The pass through normally needs the variable in the EXEC statement for some reason, so this line should read;

qdf.SQL = "EXEC dbo.Stp_FIX_RPT_Fname  @ID_Subj = " & IdValueToProcess

They do already have EXEC.

BTW, Named Parameters should be enabled for the procedure with the syntax you have used. liidlam's call syntax with the positonal parameter is fine.

 

[Minty]

Hmmm interesting - I'm sure I had issues without the parameter being included, and I haven't had to declare them up front - I'll revisit.

 

[liddlem]

Hi Glaxiom
I put in a qdf.Execute statement as suggested.
I still cannot get it working.

I am wondering of there is something wrong with my ODBC connection?
It used to be able to connect to the database using the 'SQL Server' driver, but my system crashed (like TOTAL wipeout) the other day while I was testing my code above.

I have spend the last 3 days uninstalling and reinstalling my system.
Now - the only driver that is working for me is "ODBC Driver 13 for SQL Server"

Another possible issue is that I'm using 64-bit SQL Server, but 32-bit Access.
And I have only set up 32-bit ODBC connection at this time.
Perhaps I should try creating the 64-bit connection too?

 

[Galaxiom]

If there were something wrong with the connection or the server login didn't have execute permissions on the procedure I would expect an error message.

Try this to show any errors.

qdf.Execute dbFailOnError

Are you sure the procedure has not succeeded?
Look at the RecordsAffected Property of the qdf after it runs.

Can you execute a simple query using the connection?

Did you try Minty's syntax?

 

[Minty]

Another possible issue is that I'm using 64-bit SQL Server, but 32-bit Access.
And I have only set up 32-bit ODBC connection at this time.

Just as a pointer, this is our setup, so that shouldn't be an issue.

 

[isladogs]

I have not exactly the same issue but almost similar to this back-end and front end of Access database.

I have heard that splitting access database is quiet helpful while multiple users are working on the same database at the same time and also resolves database performance. So can anyone tell what how these procedure of spiriting database to be performed.

I don’t have much knowledge on this…..i anyone have then please share……!

Oh the wonders of auto spellcheck.... I've often wanted to spirit my database when things go wrong.

Splitting a database isn't 'quite helpful' in multiuser environments. Its ESSENTIAL to avoid corruption.

Lots of info on this online. For example
http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html

 

[Minty]

I have just had time to check and using this ODBC connection string;

ODBC;DRIVER=SQL Server;SERVER=OurSQLServer;;DATABASE=MyDatabase;UseTrustedConnection=True

This Pass through syntax does not work, I get the following error - ODBC - Call failed incorrect syntax near '='. (#102)

exec dbo.CallTimes_Breakdown = 41656

Whereas this does work;

exec dbo.CallTimes_Breakdown @SvcID = 41656

Using SQL Server 2008, Access 2010. ODBC is stored as a DSN (Machine Data Source) .

 

[Galaxiom]

This Pass through syntax does not work, I get the following error - ODBC - Call failed incorrect syntax near '='. (#102)

exec dbo.CallTimes_Breakdown = 41656

Of course that won't work. Try without the equals sign.

Whereas this does work;
Code:
exec dbo.CallTimes_Breakdown @SvcID = 41656

The issue with passing named parameters like you have is that the names are ignored unless the Stored Procedure is configured for named parameters. Without that setting enabled they are still passed in the order they are presented regardless of the names you give them.

Obviously it won't matter with one parameter and it won't even matter if the parameters are listed in the same order as they are defined in the procedure. But it can lead to very confusing results if the order is not matched.

 

[Minty]

Of course that won't work. Try without the equals sign.

Doh - Sometimes my level of thickness amazes me...

Edit - And obviously yes without the equals sign it does work.
I see your point about the named parameters as well. By default I have always put the parameters in in the correct order, so I've not noticed the problem with naming them...