Two ways to execute a Stored procedure in VBA, Whi

Question

Background: Work on frontend Ms-Access 2010 and backend SQL server 2008 Managment Studio

For executing stored procedures I have been using a pretty lengthy process as seen here: in VBA

    Set Conn = New ADODB.connection
    Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;....."
    Conn.Open

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = Conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "upGetTestIdForAnalyte"

    cmd.Parameters.Append cmd.CreateParameter("@WOID", adVarChar, adParamInput, 60, MySampleName)
    cmd.Parameters.Append cmd.CreateParameter("@Analyte", adVarChar, adParamInput, 60, MyAnalyte)
    cmd.Parameters.Append cmd.CreateParameter("@SampleID", adVarChar, adParamInput, 60, MyConcentration

    cmd.Execute
    Conn.Close

Someone told me there was a better way to execute a stored procedure and the correct way would be something like this: in VBA

strsql = "Exec upGetTestIdForAnalyte(WOID, Analyte, SampleID)"
test = ExecuteNonQuery(strsql)

But I got a lot of errors for this process and I looked up ExecuteNonQuery and it said it was only for VB (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx). One of the advantages of this process would be its conciseness and the fact that it connects automatically to the current database. I would have to change the connection string in my current sp process because it is set to link to a test database not the actual one. Another advantage would be that it returns a value when executed. I would like my current process to do that because I want to somehow verify it ran and other stored procedures I need to make, need to return the number of records affected.

My questions are: Is the second method a legitimate process and is that the correct syntax? Also is there any difference in what each process accomplishes? Also is there a way for the first process to return a value when executed? Thank you for your time.

UPDATE: This is my stored procedure I'm currently working on. My sp sees if a testID exists or not, I will continue with the program after calling the sp if variable ThisTestId >0 else I will raise an error testid not found

CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @Test var(20) output
AS

  SELECT TestID = t1.TestID
  FROM tblWOSampleTest t1
  JOIN tblTest t2
  ON t1.TestID=t2.TestID; 
  WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte

GO

Answer 1

I'll stay with first process and add an output parameter like this:

ccmd.parameters.Append ccmd.CreateParameter("OutParam", adInteger, adParamOuput, , NULL)   ' output parameter

You need to add this parameter in stored procedure as well like this:

@OutParam int OUTPUT

EDIT Added OUT parameter, changed to integer in VBA code. See how @@ROWCOUNT work

CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @RecordsAfected int OUT
AS

SELECT TestID = t1.TestID
FROM tblWOSampleTest t1
JOIN tblTest t2
ON t1.TestID=t2.TestID; 
WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte
set @recordsAfected = @@ROWCOUNT
GO

Answer 2

The examples posted here have way too much code.

The original question is the poster has seen some examples where only one or two lines of code is required.

In fact this code works and passes a parameter to a store procedure.

With CurrentDb.QueryDefs("MyPass")
  .SQL = "exec MySproc" & ProducutID
  .Execute
End If

The above is only two lines of code.

The advantages of this approach?

Note how we did not have to mess (or pass) with a connection string.

Note how we did not have to declare any connection object in code.

Note how we did not have to store or have the user name or password anywhere in the code.

Note how we don’t have to create a query def object in code either.

In fact the whole thing works without declaring ANY variables.

And the above could have been written on two lines of code, but I decided to post a massive 4 lines of code for increased readability.

The advantages of this setup are many, but the main reason is such an approach is MOST simple and easy to maintain.

The other posted solutions here serve only to force one to write many lines of code – this simply increases development costs for their employers.

Answer 3

From Microsoft help site:

   Function ExecuteSPT (sqltext As String, connectstring As String)

   ' Purpose: Run a temporary pass-through query.
   ' Accepts: sqltext: SQL string to run.
   '          connectstring: Connection string, which must be at least
   '          "ODBC;".
   ' Returns: nothing.

   Dim mydb As Database, myq As QueryDef
   Set mydb = DBEngine.Workspaces(0).Databases(0)

   ' Create a temporary QueryDef object that is not saved.
   Set myq = mydb.CreateQueryDef("")

   ' Set the ReturnsRecords property to False in order to use the
   ' Execute method.
   myq.returnsrecords = False

   myq.connect = connectstring
   myq.sql = sqltext

   myq.Execute
   myq.Close

   End Function

It can be easily altered to return as DAO.Recordset. DAO is still the "native" data access in MS Access.