access 2013将记录集分配给表单时收到错误消息“您输入的对象不是有效的记录集属性”
access 2013将记录集分配给表单时收到错误消息“您输入的对象不是有效的记录集属性”
提问于 2016-05-04 04:00:39
我正在使用Access 2013升级一个带有sql 2012后端的旧应用程序。我有几个带参数的存储过程，我需要调用它们并将它们分配给窗体和报表。
我遇到的问题是，每次我尝试使用表单的open事件和以下代码Private Sub Form_Open(Cancel As Integer)将返回的记录集赋给表单时，都会得到错误"7965“
Dim cmd1 As ADODB.Command
Dim recs1 As New ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim prm3 As ADODB.Parameter
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "DRIVER={SQL Server Native Client 11.0};SERVER=192.168.0.12;DATABASE=SavingsPlusCorp;Trusted_Connection=yes;"
cnn.Open cnn.ConnectionString
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = cnn
cmd1.CommandText = "dbo.iNVENSOLDSp"
cmd1.CommandType = adCmdStoredProc
Set prm1 = cmd1.CreateParameter("@branchid", adInteger, adParamInput, 2)
cmd1.Parameters.Append prm1
Set prm2 = cmd1.CreateParameter(" @Beginning_Date", adDate, adParamInput)
cmd1.Parameters.Append prm2
Set prm3 = cmd1.CreateParameter(" @Ending_Date", adDate, adParamInput)
cmd1.Parameters.Append prm3
Set prm4 = cmd1.CreateParameter("@vENDORID", adInteger, adParamInput, 2)
cmd1.Parameters.Append prm4
Set prm5 = cmd1.CreateParameter("@catID", adInteger, adParamInput, 2)
cmd1.Parameters.Append prm5
prm1.Value = Form_ReportGenerator.Branches
prm2.Value = Form_ReportGenerator.Begin_Date
prm3.Value = Form_ReportGenerator.Ending_Date
prm4.Value = Form_ReportGenerator.Vendors
prm5.Value = Form_ReportGenerator.Category
Set recs1 = CreateObject("ADOdB.recordset")
recs1.CursorType = adOpenKeyset
recs1.CursorLocation = adUseClient
'Set recs1 = cmd1.Execute
'recs1.Open
Set Me.Recordset = cmd1.Execute 我也试过
set me.Recordset= recs1有了相同的结果，请帮助解决方案中的线程被识别为可能是几乎相同的我的尝试，事实上，我用它来构建我的尝试，我使用的命令。执行并将其分配给我的窗体
Set me.RecordSet = cmd1.execute就是这一行返回的错误是我用来连接的提供程序的问题我不明白为什么它不能工作请帮助
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-05-04 16:12:37
您需要调用ADO.Recordset对象的Open方法。
为了演示这一点，首先在SQL Server中创建一个简单的存储过程。
USE AdventureWorks2016CTP3;
GO
CREATE PROCEDURE dbo.up_TestPerson
AS
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person;然后使用下面的代码创建一个Microsoft Access窗体。
Private Sub Form_Open(Cancel As Integer)
Dim cnn As New ADODB.Connection
cnn.ConnectionString = "DRIVER={SQL Server Native Client 11.0};SERVER=V-SQL16-R;DATABASE=AdventureWorks2016CTP3;Trusted_Connection=yes;"
cnn.Open
Dim cmd1 As New ADODB.Command
Set cmd1.ActiveConnection = cnn
cmd1.CommandText = "dbo.up_TestPerson"
cmd1.CommandType = adCmdStoredProc
Dim rst As New ADODB.Recordset
rst.Open cmd1, , adOpenKeyset, adLockPessimistic
Set Me.Recordset = rst
End Sub打开表单，它将显示数据。
要演示参数的使用，请创建一个如下所示的存储过程。(与第一个存储过程相同，只是它有一个参数和一个WHERE子句。)
CREATE PROCEDURE dbo.up_TestPerson2
(
@PersonType nchar(2)
)
AS
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE PersonType = @PersonType;使用此代码创建另一个Access窗体。(除了我们在其中创建和配置参数的块之外，与第一个表单相同。)
Private Sub Form_Open(Cancel As Integer)
Dim cnn As New ADODB.Connection
cnn.ConnectionString = "DRIVER={SQL Server Native Client 11.0};SERVER=V-SQL16-R;DATABASE=AdventureWorks2016CTP3;Trusted_Connection=yes;"
cnn.Open
Dim cmd1 As New ADODB.Command
Set cmd1.ActiveConnection = cnn
cmd1.CommandText = "dbo.up_TestPerson2"
cmd1.CommandType = adCmdStoredProc
' This is new
Dim prm1 As ADODB.Parameter
Set prm1 = cmd1.CreateParameter("@PersonType", adWChar, adParamInput, 2)
cmd1.Parameters.Append prm1
prm1.Value = "EM"
Dim rst As New ADODB.Recordset
rst.Open cmd1, , adOpenKeyset, adLockPessimistic
Set Me.Recordset = rst
End Sub打开表单，它将显示与参数匹配的记录。
Stack Overflow用户
发布于 2019-03-12 07:49:02
我有这个问题，花了几个小时寻找它...结果就在我的眼皮底下:一个运行得很好的函数，从Mysql支持的Access组合框中重新填充，在我出于几乎不相关的原因将adUseClient改为adUseServer后，突然给出了这条消息。以下是代码(感谢作者: Christian Coppes)
Public Function fnADOComboboxSetRS(cmb As String, strSQL As String, strCallingForm As String, Optional StrCnnstring As String)
On Error GoTo fnADOComboboxSetRS_Error
Dim sourceDB As New clsAdoDBHelper
Dim RS1 As New ADODB.Recordset
If Len(StrCnnstring & vbNullString) = 0 Then
sourceDB.Connect CnString
Else
sourceDB.Connect StrCnnstring
End If
Set RS1 = sourceDB.OpenRecordset(strSQL, adUseClient)
Set Application.Forms(strCallingForm).Controls(cmb).Recordset = RS1
fnADOComboboxSetRS_Exit:
If Not RS1 Is Nothing Then
If RS1.State = adStateOpen Then RS1.Close
Set RS1 = Nothing
End If
Exit Function
fnADOComboboxSetRS_Error:
Select Case Err
Case Else
'fnErr "modODBC->fnADOComboboxSetRS", True
Resume fnADOComboboxSetRS_Exit
End Select
End Function页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37013322
复制相关文章
相似问题