title: Calling stored procedure using VBA
body: <p>I am working in Access 2010 user front-end with a Microsoft SQL Server 2008 back-end.</p>
  
  <p>The tables in Access are all linked to the SQL server database.</p>
  
  <p>I have a stored procedure that inserts new values (supplied by the parameters) into a table.</p>
  
  <p>I asked a similar question previously and got a good answer <a href="https://stackoverflow.com/questions/24248870/calling-stored-procedure-while-passing-parameters-from-access-module-in-vba">Calling Stored Procedure while passing parameters from Access Module in VBA</a>  </p>
  
  <p>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).</p>
  
  <p>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 <code>.Connect</code> string with a DAO.QueryDef object to execute the Stored Procedure" - <a href="https://stackoverflow.com/questions/18664139/connection-string-for-access-to-call-sql-server-stored-procedure">Connection string for Access to call SQL Server stored procedure</a></p>
  
  <p>I tried to implement this code. To pass parameters, I tried using a previous example.</p>
  
  <p>I got the error</p>
  
  <blockquote>
    <p>call failed</p>
  </blockquote>
  
  <p>at the line <code>Set rst = qdf.OpenRecordset(dbOpenSnapshot)</code> (not to mention my passing parameters code is probably way off). </p>
  
  <pre><code>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></pre>
  
  <p>Could anyone tell me what could be wrong with my code and why I am getting this error? </p>