Is it possible to pass a parameter from MS Access to a SQL Server stored procedure and display the o-CodePudding

Users in MS Access enter values in a field that is passed as a parameter to a stored procedure in a SQL Server database. The stored procedure will return a result set with two columns. I would like to display that result set in a datasheet form. The code below almost does that, but will only display the final row of the result set. I confirmed that the stored procedure is being passed to the db correctly and that the test values I'm using should return 4 rows. The code in comments are a few of the many things that I have tried that didn't work.

Private Sub btnBulkLink_Click()

    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset
               
    With cmd
        .CommandType = adCmdStoredProc
        .ActiveConnection = "Driver=SQL Server Native Client 11.0;Server=xxxxxxxxxxxxxxxxx;Database=xxxxx;Trusted_Connection=Yes"
        .CommandText = "dbo.usp_FindResolutionsLinkedToServices"
        .CommandTimeout = 180
        .NamedParameters = True
    End With
           
    cmd.Parameters.Append cmd.CreateParameter("@PprsRefINP", adLongVarChar, adParamInput, Len(txtBulkLink.Value), txtBulkLink.Value)
    Set rst = cmd.Execute
    
    DoCmd.OpenForm FormName:="ServiceResolutionLink", View:=acFormDS, DataMode:=acFormReadOnly, WindowMode:=acHidden
'   Set Forms!ServiceResolutionLink.Recordset = rst

    With rst
    Do While Not .EOF
        Forms("ServiceResolutionLink").txtPprsRef = rst!PprsRef
        Forms("ServiceResolutionLink").txtSubmissionId = rst!SubmissionId
    .MoveNext
    Loop
    End With

'   Forms("ServiceResolutionLink").txtPprsRef = rst!PprsRef
'   Forms("ServiceResolutionLink").txtSubmissionId = rst!SubmissionId
'   Forms("ServiceResolutionLink").Visible = True

    DoCmd.OpenForm FormName:="ServiceResolutionLink", View:=acFormDS
   
    rst.Close
    
End Sub

CodePudding user response：

Ok, we assume you setup a pass-though query. (and it set with returns records = true).

And if your datasheet is a REAL sub form (setup as datasheet - not a table).

Then this code will work:

Private Sub Command2_Click()

  With CurrentDb.QueryDefs("qryPassR")
      .SQL = "exec dbo.GetHotels2 @City = '" & "Banff" & "'"
  End With
  
  Me.MyDataSheet.Form.RecordSource = "qryPassR"
        
End Sub

And if you are using a real datasheet (form that does not exist), then you can have the form set to

Query.PassR

The trick (or challenge) then becomes to have the form NOT load until such time you setup the query with the correct stored procedure call.

CodePudding user response：

I was in the midst of testing suggestions from others when my boss figured this out. Here is our final code:

Private Sub Form_Load()
    Dim rst As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim strPPRSRef As String
    Dim cn As New ADODB.Connection
    
    With cn
        .Provider = "sqloledb"
        cn.Properties("Data Source").Value = "[server]"
        cn.Properties("Initial Catalog").Value = "[db]"
        cn.CursorLocation = adUseClient
'       Windows authentication.
        cn.Properties("Integrated Security").Value = "SSPI"
'       .CursorLocation = adUseClient
    End With

    cn.Open
               
    With cmd
        .CommandType = adCmdStoredProc
        .ActiveConnection = cn
        .CommandText = "dbo.usp_FindResolutionsLinkedToServices"
        .CommandTimeout = 180
        .NamedParameters = True
    End With
    
    strPPRSRef = Forms("Resolution_Tracker").txtBulkLink.Value
    
    cmd.Parameters.Append cmd.CreateParameter("@PprsRefINP", adLongVarChar, adParamInput, Len(strPPRSRef), strPPRSRef)
   
    With rst
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
    End With
           
    Set rst = cmd.Execute
    Set Me.Recordset = rst
    
    rst.Close
    Set rst = Nothing
    
    cn.Close
    Set cn = Nothing
    
End Sub