title: Calling Stored Procedure while passing parameters from Access Module in VBA
body: <p>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.  </p>
  
  <pre><code>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 &amp; " " &amp; Err.Description &amp; " in CreateInstrumentInterfaceLogRecords"
  GoTo ExitProc
  
  End Function
  </code></pre>
  
  <p>and the stored procedure is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>I am not really sure what I should take from this example and how to incorporate passing values.  Also even though I have visited <a href="http://www.connectionstrings.com/" rel="nofollow">http://www.connectionstrings.com/</a> I am still confused on how to make them.  Any help would be greatly appreciated</p>