ADO Me.RecordSet invalid recordset property

I have a form which is not updatable and based on an ado recordset (see code)
Previously, the commented section was not commented, and the with cmd section wasn't there. It worked fine.
Recently, Il replaced it with the cmd section and it doesn't work any more.
On the insruction Set Me.Recordset = rst, I get  an error message "7965 The object you entered is not a valid recordset property"
What is wrong?

In the code, I have also listed the SQL Server (2008) stored procedure being used in the with cmd section. The field is NORecherche is unique.
Private Sub Form_Open(Cancel As Integer)
    Dim SQL As String
    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset
    If cnUtil.State <> adStateOpen Then
        cnUtil.Open sDemelerChaine(sConnUtil)
    End If
On Error GoTo Terminer
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenKeyset
'    rst.Open SQL, cnUtil, adOpenKeyset
'    SQL = "SELECT     dbo.tbRechercheExperts.ID, dbo.tbRechercheExperts.NoRecherche, dbo.tbRechercheExperts.Initiales,dbo.tbRechercheExperts.NbBons, dbo.tbRechercheExperts.DateReponse,dbo.tbRechercheExperts.Statut, " & _
'                      " dbo.tbRechercheExperts.DateDemande, CASE WHEN FC.NoFiche IS NULL THEN H.Prénom + ' ' + H.Nom ELSE FC.PrénHomme + ' ' + FC.NomHomme END AS Homme," & _
'                      " CASE WHEN FC.NoFiche IS NULL THEN F.Prénom + ' ' + F.Nom ELSE FC.PrénFemme + ' ' + FC.NomFemme END AS Femme, CASE WHEN FC.NoFiche IS NULL" & _
'                      " THEN TypeRecherche ELSE 3 END AS Type" & _
'        " FROM         dbo.tbRechercheExperts  LEFT OUTER JOIN" & _
'                      " [ma-arbres" & sSourceBD & "].dbo.tbPersonnes AS H ON dbo.tbRechercheExperts.NoHomme = H.NoPersonne LEFT OUTER JOIN" & _
'                      " [ma-arbres" & sSourceBD & "].dbo.tbPersonnes AS F ON dbo.tbRechercheExperts.NoFemme = F.NoPersonne LEFT OUTER JOIN" & _
'                      " [mesaieux" & sSourceBD & "].dbo.tbSiteFiches AS FC ON dbo.tbRechercheExperts.NoFiche = FC.NoFiche" & _
'        " WHERE     (dbo.tbRechercheExperts.ID = '" & Me.OpenArgs & "')"
    With cmd
        .ActiveConnection = cnUtil
        .CommandType = adCmdStoredProc
        .CommandText = "spRecherchesServClientele"
        .Parameters.Append cmd.CreateParameter("@ID", adVarChar, adParamInput, 20, Me.OpenArgs)
        Set rst = .Execute
    End With
    
    
    
    Set Me.Recordset = rst
    Exit Sub
Terminer:
    Cancel = True

End Sub




USE [ma-admin-01]
GO
/****** Object:  StoredProcedure [dbo].[spRecherchesServClientele]    Script Date: 10/06/2011 12:15:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spRecherchesServClientele] 
	-- Add the parameters for the stored procedure here
	@ID varchar(20) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT     dbo.tbRechercheExperts.ID, dbo.tbRechercheExperts.NoRecherche, dbo.tbRechercheExperts.Initiales,dbo.tbRechercheExperts.NbBons, 
	dbo.tbRechercheExperts.DateReponse,dbo.tbRechercheExperts.Statut,
	dbo.tbRechercheExperts.DateDemande, 
	CASE WHEN tbRechercheExperts.NoFiche IS NULL THEN 
	 CASE WHEN TypeRecherche IN (0, 1) THEN H.Prénom + ' ' + H.Nom 
	 ELSE CASE E.Sexe WHEN 1 THEN E.Prénom + ' ' + E.Nom ELSE '' END
	 END
	ELSE 
	 CASE tbRechercheExperts.TypeRecherche WHEN 1 THEN FC.PrénHomme + ' ' + FC.NomHomme 
	 WHEN 2 THEN CASE B.Sexe WHEN 1 THEN B.PrénEnfant + ' ' + B.NomEnfant ELSE '' END
	 WHEN 3 THEN CASE S.Sexe WHEN 1 THEN S.PrénDéfunt + ' ' + S.NomDéfunt ELSE '' END
	 END
	END AS Homme,
	CASE WHEN tbRechercheExperts.NoFiche IS NULL THEN 
	 CASE WHEN TypeRecherche IN (0, 1) THEN F.Prénom + ' ' + F.Nom 
	 ELSE CASE E.Sexe WHEN 0 THEN E.Prénom + ' ' + E.Nom ELSE '' END
	 END
	ELSE 
	 CASE tbRechercheExperts.TypeRecherche WHEN 1 THEN FC.PrénFemme + ' ' + FC.NomFemme  
	 WHEN 2 THEN CASE B.Sexe WHEN 0 THEN B.PrénEnfant + ' ' + B.NomEnfant ELSE '' END
	 WHEN 3 THEN CASE S.Sexe WHEN 0 THEN S.PrénDéfunt + ' ' + S.NomDéfunt ELSE '' END
	 END 
	END AS Femme, 
	CASE WHEN tbRechercheExperts.NoFiche IS NULL THEN CASE WHEN TypeRecherche BETWEEN 1 AND 3 THEN  1 ELSE 0 END ELSE 2 END AS TypeRecherche,
	CASE WHEN TypeRecherche IN (0, 1) THEN  'M' WHEN TypeRecherche IN (2, 4) THEN  'B' ELSE 'S' END AS TypeActe
	FROM dbo.tbRechercheExperts  
	LEFT OUTER JOIN [ma-arbres-01].dbo.tbPersonnes AS H ON dbo.tbRechercheExperts.NoHomme = H.NoPersonne 
	LEFT OUTER JOIN [ma-arbres-01].dbo.tbPersonnes AS F ON dbo.tbRechercheExperts.NoFemme = F.NoPersonne 
	LEFT OUTER JOIN [ma-arbres-01].dbo.tbPersonnes AS E ON dbo.tbRechercheExperts.NoEnfant = E.NoPersonne 
	LEFT OUTER JOIN [mesaieux-01].dbo.tbSiteFiches AS FC ON dbo.tbRechercheExperts.NoFiche = FC.NoFiche
	LEFT OUTER JOIN [ma-bs-01].dbo.tbSiteBaptemes AS B ON dbo.tbRechercheExperts.NoFiche = B.NoActe
	LEFT OUTER JOIN [ma-bs-01].dbo.tbSiteSepultures AS S ON dbo.tbRechercheExperts.NoFiche = S.NoActe
	WHERE     (dbo.tbRechercheExperts.ID = @ID)END

Open in new window

GiteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PreeceCommented:
I think the problem might be that you are using a single quote as a comment marker.  This works in VB and VB.NET, but not sql.  Use this instead:

/*
commented code
goes here
*/


Preece
PreeceCommented:
Or you can use two dashes for a single line comment:

-- single line comment in sql
GiteAuthor Commented:
I executed the stocked procedure as is in SQL Server and it worked fine. The execute instruction works fine.
The problem is with the set Me.recordset statement
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PreeceCommented:
My bad.  Your example has a mix of sql and a vb function.  Still looking...

Preece
Leigh PurvisDatabase DeveloperCommented:
Have you tested the recordset after opening it?  Have you examined the recordset before attempting to assign it to make sure it was successfully opened and can be accessed and navigated?
Since the only change is the recordset you've opened, you can safely look there as you start to debugging. ;-)
There is an initial concept here.
rst.CursorType = adOpenKeyset

Open in new window

You might as well actually request the more accurate
rst.CursorType = adOpenStatic

Open in new window

As this is what you'd get with a client side cursor anyway.  However, then consider how you're opening the recordset.
Set rst = .Execute

Open in new window

The Execute method normally returns a forward only recordset, although the client side cursor forces this Static again.  But always a read only one.
If you open your recordset more like:
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    rst.LockType = adLockReadOnly
    With cmd
        Set .ActiveConnection = cnUtil
        .CommandType = adCmdStoredProc
        .CommandText = "spRecherchesServClientele"
        .Parameters.Append cmd.CreateParameter("@ID", adVarChar, adParamInput, 20, Me.OpenArgs)
    End With
    rst.Open cmd
    Set Me.Recordset = rst

Open in new window

Do you get the same results?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GiteAuthor Commented:
Great. The problem was with the .execute!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.