从VBA中的访问模块传递参数时调用存储过程 [英] Calling Stored Procedure while passing parameters from Access Module in VBA

问题描述

我正在使用Microsoft SQL Server 2008后端在Access 2010中工作.我有一个存储过程，将新值(由参数提供)插入表中.分配给参数的值是从存储在文件夹中的文件中获得的. Windows文件系统用于扫描特定的文件夹以列出其中的文件.对于每个扫描的文件，将调用存储过程，并将FileName和QueueId(不带扩展名的文件名)以及其他值用作调用的存储过程的参数.该存储过程用于为表的每个文件创建新记录.

I am working in Access 2010 with a Microsoft SQL Server 2008 backend. I have a stored procedure that inserts new values(supplied by the parameters) into a table. The values assigned to the parameters are obtained from files stored in a folder. The Windows File System is used to scan a particular folder to make a list of the files in it. For each scanned file the stored procedure is called and the FileName and QueueId (Filename without extension) along with other values are used as parameters for the stored procedure called. The stored procedure is used to create new records for each file for a table.

Public Function CreateInstrumentInterfaceLogRecords(BatchID As Long, InstrumentName As            String) As Boolean
On Error GoTo HandleError

Dim objFSO As FileSystemObject
Dim AllFiles As Object
Dim objFolder As Object
Dim objFile As Object
Dim FileExt As String
Dim strSQL As String

CreateInstrumentInterfaceLogRecords = False
strSQL = "SELECT FileExt FROM tlkpInstrument"
FileExt = ExecuteScalar(strSQL)


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(NewPath) 

'NewPath is a public variable that holds the path to the folder'

Set AllFiles = objFolder.Files
For Each objFile In AllFiles

FileName = objFile.FileName
QuenueId = Replace(FileName, FileExt, "")

'call procedure to pass values'

Next objFile

ExitProc:
Exit Function
HandleError:
MsgBox Err.Number & " " & Err.Description & " in CreateInstrumentInterfaceLogRecords"
GoTo ExitProc

End Function

，存储过程为:

CREATE PROCEDURE upInsertToInstrumentInterfaceLog @BatchID nvarchar(60),@InstrumentName nvarchar(60) ,@FIleName nvarchar(60), @QueueId nvarchar(60) 
AS
INSERT INTO tblInstrumentInterfaceLog (BatchID,InstrumentName,FileName,QueueID,DateOfBatch,Folder)
VALUES (@BatchID, @InstrumentName,@FileName, @QueueId,getdate(),'New');
GO

所有示例并没有真正为我创建连接和调用过程的坚实思路.我得到的一些建议是研究ExecuteNonquery的工作原理，因此我一直在尝试查找与此相关的示例.以下是我找到的示例模板之一

All the examples haven't really given me a solid idea of to create the connection and call the procedure. Some advice I have been given is to study how ExecuteNonquery works so I have been trying to find examples related to that. The following is one of the example templates I've found

Dim conn As ADODB.Connection 
Dim cmd As ADODB.Command 

Set conn = New ADODB.Connection 
conn.ConnectionString = "your connection String here" 
conn.Open 

Set cmd = New ADODB.Command 
cmd.ActiveConnection = conn 
cmd.CommandType = adCmdStoredProc 
cmd.CommandText = "put stored procedure name here" 

cmd.Execute 
conn.Close 

Set conn = Nothing 
Set cmd = Nothing 

我不太确定我应该从这个示例中学到什么以及如何合并传递的值.即使我访问过 http://www.connectionstrings.com/，我仍然对如何制作感到困惑他们.任何帮助将不胜感激

I am not really sure what I should take from this example and how to incorporate passing values. Also even though I have visited http://www.connectionstrings.com/ I am still confused on how to make them. Any help would be greatly appreciated

推荐答案

Dim conn As ADODB.Connection 
Dim cmd As ADODB.Command 

Set conn = New ADODB.Connection 
conn.ConnectionString = "your connection String here" 
conn.Open 

Set cmd = New ADODB.Command 
cmd.ActiveConnection = conn 
cmd.CommandType = adCmdStoredProc 
cmd.CommandText = "upInsertToInstrumentInterfaceLog" 

cmd.parameters.Append cmd.CreateParameter("@BatchID", adVarChar, adParamInput, 60, "value for BatchID")   
cmd.parameters.Append cmd.CreateParameter("@InstrumentName", adVarChar, adParamInput, 60, "value for InstrumentName")   
'...

cmd.Execute 
conn.Close 

这篇关于从VBA中的访问模块传递参数时调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！