使用VBA调用存储过程 [英] Calling stored procedure using VBA

问题描述

我正在使用Microsoft SQL Server 2008后端在Access 2010用户前端中工作.

I am working in Access 2010 user front-end with a Microsoft SQL Server 2008 back-end.

Access中的表都链接到SQL Server数据库.

The tables in Access are all linked to the SQL server database.

我有一个存储过程，可以将新值(由参数提供)插入表中.

I have a stored procedure that inserts new values (supplied by the parameters) into a table.

我之前曾问过类似的问题，并且得到了很好的答案

I asked a similar question previously and got a good answer Calling Stored Procedure while passing parameters from Access Module in VBA

我不知道如何找到建立连接字符串所需的信息(例如:我不知道提供商/服务器名称/服务器地址).

I do not know how to find the information required for making a connection string (ex: I don't know the provider/server name/server address).

我在这里发现一个问题，指出如果您已经有一个指向SQL Server数据库的Access链接表，则可以将其.Connect字符串与DAO.QueryDef对象一起使用来执行存储过程"-用于访问SQL Server存储过程的访问的连接字符串

I found a question on here that stated "If you already have an Access linked table pointing to the SQL Server database then you can simply use its .Connect string with a DAO.QueryDef object to execute the Stored Procedure" - Connection string for Access to call SQL Server stored procedure

我尝试实现此代码.为了传递参数，我尝试使用前面的示例.

I tried to implement this code. To pass parameters, I tried using a previous example.

我得到了错误

通话失败

在Set rst = qdf.OpenRecordset(dbOpenSnapshot)行中的

(更不用说我传递的参数代码可能还很遥远).

at the line Set rst = qdf.OpenRecordset(dbOpenSnapshot) (not to mention my passing parameters code is probably way off).

Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = CurrentDb.TableDefs("tblInstrumentInterfaceLog").Connect
qdf.sql = "EXEC dbo.upInsertToInstrumentInterfaceLog"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

qdf.Parameters.Append qdf.CreateParameter("@BatchID", adVarChar, adParamInput, 60, BatchID)
qdf.Parameters.Append qdf.CreateParameter("@InstrumentName", adVarChar, adParamInput, 60, InstrumentName)
qdf.Parameters.Append qdf.CreateParameter("@FileName", adVarChar, adParamInput, 60, FileName)
qdf.Parameters.Append qdf.CreateParameter("@QueueId", adVarChar, adParamInput, 60, QuenueId)

rst.Close
Set rst = Nothing
Set qdf = Nothing

谁能告诉我我的代码可能出什么问题了，为什么我会收到此错误?

Could anyone tell me what could be wrong with my code and why I am getting this error?

推荐答案

维多利亚，

您可以使用ADO运行存储过程，如下所示...

You can run a stored procedure using ADO, like below...

Set mobjConn = New ADODB.Connection
mobjConn.Open "your connection string"
Set mobjCmd = New ADODB.Command
With mobjCmd
    .ActiveConnection = mobjConn 
    .CommandText = "your stored procedure"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 0
    .Parameters.Append .CreateParameter("your parameter name", adInteger, adParamInput, , your parameter value)
    ' repeat as many times as you have parameters

    .Execute
End With

要获取连接字符串，可以使用线路

To get your connection string, you can use the line

Debug.Print CurrentDb.TableDefs("tblInstrumentInterfaceLog").Connect

在立即窗口中，它应该显示一个可以使用的连接字符串.

in the Immediate Window and that should show you a connection string which you can use.

您可以尝试一下，如果有任何问题，请告诉我.

Would you try that and let me know if you have any problems.

灰

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