将访问表单绑定到存储过程的结果 [英] Bind Access form to the results from a Stored Procedure
本文介绍了将访问表单绑定到存储过程的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将存储过程的结果返回到表单.我已经设法使用ADO记录集遍历结果,但是无法将结果绑定到表单.
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..
这是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
推荐答案
好的,我已经测试了这个示例.它包括适合我剩下的设置的更改,而不是猜测您的设置.其中大部分内容取自 http://support.microsoft.com/kb/281998/ZH-CN/
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/
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
这篇关于将访问表单绑定到存储过程的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文