将访问表单绑定到存储过程的结果

I am trying to return the results of a stored procedure to a form. I have managed to iterate thru the results using an ADO recordset, but cannot bind the results to the form..

我试图将存储过程的结果返回到表单。我已设法使用ADO记录集迭代结果,但无法将结果绑定到表单..

Here is the VBA code:

这是VBA代码:

Private Sub RetrieveSiteInformation()

  Dim cmd As New ADODB.Command
  Dim cnn As New ADODB.Connection
  Dim rs As ADODB.Recordset, f As ADODB.Field

  With cnn
    .Provider = "SQLOLEDB"
    .ConnectionString = 
        "data source=UKFCSVR;initial catalog=ACACB;Trusted_Connection=Yes"
    .Open
  End With

  Dim param1  As ADODB.Parameter
  If Nz(txtSiteID_Search.Value, vbNullString) <> vbNullString Then
    Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
    param1.Value = txtSiteID_Search.Value
    cmd.Parameters.Append param1
  End If

  With cmd
   .ActiveConnection = cnn
   .CommandText = "spSiteInformation_Retrieve"
   .CommandType = adCmdStoredProc
    **' THIS FAILS**
    Me.Recordset = .Execute
    **' THIS LOOP WORKS FINE**
    '    Set rs = .Execute
    '    rs.MoveFirst
    '    For Each f In rs.Fields
    '      Debug.Print f.Name
    '    Next
    '    With rs
    '      Do While Not .EOF
    '        Debug.Print ![CompanyName] & " " & ![Postcode]
    '        .MoveNext
    '      Loop
    '    End With
  End With
  cnn.Close
End Sub

3 个解决方案

#1

Okay, I have tested this example. It includes changes to suit my set-up which I have left in, rather than guessing at your set-up. Most of this is taken from http://support.microsoft.com/kb/281998/EN-US/

好的,我测试了这个例子。它包括适合我的设置的更改,而不是猜测您的设置。其中大部分来自http://support.microsoft.com/kb/281998/EN-US/

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1  As New ADODB.Parameter

    With cn
        .Provider = "Microsoft.Access.OLEDB.10.0"
        .Properties("Data Provider").Value = "SQLOLEDB"
        .Properties("Data Source").Value = "Server"
        .Properties("Integrated Security").Value = "SSPI"
        .Properties("Initial Catalog").Value = "Test"
        .Open
    End With

    txtSiteID_Search = 1

    If Nz(txtSiteID_Search, vbNullString) <> vbNullString Then
        Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
        param1.Value = txtSiteID_Search
        cmd.Parameters.Append param1
    End If

    With cmd
        .ActiveConnection = cn
        .CommandText = "spSiteInformation_Retrieve"
        .CommandType = adCmdStoredProc
        Set Me.Recordset = .Execute
    End With

#2

Forget ADO. Create a passthru query in Access, with property ReturnsRecords = True.
Bind your form to that passthru query.
Using VBA, change the .SQL property of that QueryDef object, then open the form. You're done.

忘了ADO。在Access中创建一个passthru查询,其属性为ReturnsRecords = True。将表单绑定到该passthru查询。使用VBA,更改QueryDef对象的.SQL属性,然后打开表单。你完成了。

Set qry = CurrentDb.QueryDefs("myQryDef")
qry.SQL = "exec spMyStoredProc " & "'argument1'"

#3

You need to use Set whenever you assign an object reference in VBA.

每当在VBA中分配对象引用时,都需要使用Set。

Change Me.Recordset = .Execute to Set Me.Recordset = .Execute.

更改Me.Recordset = .Execute设置Me.Recordset = .Execute。

Also, you probably need to open it with a supported cursor type. I don't think there's a way to change the cursor type if you use the Execute method on the Command object. You'll have to create the Recordset separately.

此外,您可能需要使用支持的游标类型打开它。如果在Command对象上使用Execute方法,我认为没有办法更改游标类型。您必须单独创建Recordset。

Set rs = New ADODB.Recordset
rs.Open cmd, , adOpenKeyset
Set Me.Recordset = rs

#1