使用存储的过程作为MS Access窗体Recordsource [英] Using a Stored Procedure as a MS Access form Recordsource

问题描述

现在，我有一个数据库，一个SQL Server后端和一个MS Access前端.在一种形式上，我的用户正在经历缓慢的加载时间才能显示它.该表单实质上是查看用户身份的(基于他们的登录ID，这部分有效)，查看其访问权限(只读，v.更新等；这也有效)，然后拉出允许的项目根据他们的访问权限进行查看(这是我认为运行缓慢的部分).

Right now, I have a database, with a SQL Server backend, and a MS Access frontend. On one form, my users are experiencing slow load times for it to display. The form essentially looks up to see who the user is (based on their login ID, this part works), sees their access rights (readonly, v. update, etc.; this also works), and then pulls the projects they are allowed to see based on their access rights (this is the part I believe is running slow).

要使此表单的加载速度更快，我觉得将最后一部分(将允许他们看到的项目拉到该部分的部分)移至SSMS存储过程应该使表单的加载速度更快.

To make this form load faster, I feel that moving the last part, the part that pulls the projects they are allowed to see, to an SSMS Stored Procedure should make the form faster to load.

我用SSMS编写了存储过程，并且在"Form Load"事件上有调用存储过程的代码.我面临的问题是使存储过程的结果成为表单的Recordsource.我已经尝试过Me.RecordSource，但这似乎不起作用.下面是调用存储过程的代码的副本:

I have the stored procedure written, in SSMS, and I have the code on the 'Form Load' event that calls the stored procedure. The issue I am facing is having the results of the stored procedure become the Recordsource of the form. I've tried Me.RecordSource, but that doesn't seem to be working. Below is a copy of the code that calls the Stored Procedure:

这与具有调用SP的代码不同，因为我已经有了调用它的代码，但是，我需要将SP的结果作为MS Access表单的记录源.

任何帮助或想法将不胜感激！ (例如，我考虑过尝试使用临时表作为记录源...)

Any help, or ideas will be appreciated! (e.g. I thought of trying to use a temp table to serve as the recordsource...)

Dim rs1 As ADODB.Recordset

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

cn.ConnectionString = "DRIVER=SQL Server;SERVER=SERVERNAME;APP=Microsoft     Office XP;WSID=MYCOMPUTER;DATABASE=dbname;Trusted_Connection=Yes;"

cn.Open


Set cmd = New ADODB.Command
Set rs1 = New ADODB.Recordset
With cmd
    .ActiveConnection = cn
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.ProcProjectSelection"
    Set prm = .CreateParameter("@xID", adVarChar, adParamInput, 10, Me.txtNetworkID)
    .Parameters.Append prm
        End With

如果我输入: 设置Me.RecordSource = rs1 在结尾为"之前，我收到一条错误消息，内容为无效使用财产"

If I put: Set Me.RecordSource = rs1 Before the "end with", I get an Error Message saying "Invalid Use of Property"

推荐答案

解决方案1:

将存储过程返回的Recordset分配给Form.Recordset对象

Assign the Recordset returned from stored procedure to Form.Recordset Object

在代码末尾添加此行

Set Me.Recordset = cmd.Execute

解决方案2:

您可以基于ODBC数据源创建传递查询，并将其用作记录源

You can create pass-through query based on ODBC data source, and use it as record source

然后，在表单的属性表中，设置属性:

Then, in the property sheet of the form , set the properties:

record source = your_pass_through_query
recordset type = dynamic

我的建议使用解决方案2来避免针对ado和运行时错误进行许多设置

My advice use solution 2 to avoid many setting for ado and runtime errors

示例:使用northwind和存储过程

Example: Pass through using northwind , and stored procedure

使用查询结果集的字段名称将表单绑定到控件中

Bind your controls in the form with the field name of result set of the query

Private Sub Form_Open(Cancel As Integer)
 test
 End Sub

 Sub test()
 Dim qdf As DAO.QueryDef, rst As DAO.Recordset
 Set qdf = CurrentDb.CreateQueryDef("")
 qdf.Connect = "ODBC;Driver=SQL Server;Server=xxxx;database=northwind;Trusted_Connection=Yes;"

' stored procedure with paramete
 qdf.SQL = "exec [CustOrderHist] 'ALFKI'"

 qdf.ReturnsRecords = True
 Set rst = qdf.OpenRecordset

 Set Me.Recordset = rst

 End Sub

这篇关于使用存储的过程作为MS Access窗体Recordsource的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！