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

问题描述

背景：我在Access 2010用户前端与Microsoft SQL Server 2008的后端工作。在访问的表都连接到SQL Server数据库。我有一个存储过程中插入新值（由参数提供）到表中。我经历过无数的例子，但我不明白，足以知道我是否真的接近得到它还是我完全关闭。我问过类似的问题previously并取得了良好的anwser，但我有关于连接字符串的问题。这是问题/ anwser <一href="http://stackoverflow.com/questions/24248870/calling-stored-procedure-while-passing-parameters-from-access-module-in-vba">Calling存储过程的同时，从接入模块的VBA传递参数

Background: I am working in Access 2010 user frontend with a Microsoft SQL Server 2008 backend. 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 have been through numerous examples but I don't understand enough to know whether I am really close to getting it or I am completely off. I asked a similar question previously and got a good anwser but i have issues concerning the connection string. Here is question/anwser Calling Stored Procedure while passing parameters from Access Module in VBA

我不知道，不知道如何找到需要建立连接字符串中的信息（例如：我不知道供应商/服务器名称/服务器地址）。我发现这里一个问题，指出：如果你已经有了链接表指向SQL Server数据库，那么你可以简单地使用它.Connect字符串与DAO.QueryDef对象执行存储过程中的访问 - <一个href="http://stackoverflow.com/questions/18664139/connection-string-for-access-to-call-sql-server-stored-procedure">Connection串访问调用SQL Server存储过程我想实现这个code，但我不确定如何传递参数，所以我尝试使用previous例子。但我得到的错误呼叫失败的行设置RST = qdf.OpenRecordset（dbOpenSnapshot）何况我传递的参数code是可能的路要走。

I am unaware and 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). 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 but I was unsure on how to pass parameters, so I tried using a previous example. But i got the error "call failed" 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  

谁能告诉我这可能是错我的code和为什么我收到这个错误？谢谢！

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

推荐答案

维多利亚，

您可以运行使用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 = Me.Connection
    .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屋！