sql – 在VBA中从Access模块??转达参数时挪用存储进程

我在Access 2010中行使Microsoft SQL Server 2008后端.我有一个存储进程将新值(由参数提供)插入表中.分派给参数的值是从存储在文件夹中的文件中获取的. Windows文件体系用于扫描特定文件夹以天生个中的文件列表.对付每个扫描的文件,挪用存储进程,并将FileName和QueueId(没有扩展名的文件名)以及其他值用作挪用的存储进程的参数.存储进程用于为表的每个文件建设新记录.

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怎样事变,以是我一向试图找到与此相干的例子.以下是我发明的示例模板之一

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/,我如故对怎样建造它们感想狐疑.任何辅佐将不胜谢谢

办理要领

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","value for InstrumentName")   
'...

cmd.Execute 
conn.Close

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!