Run SP from Access 2000 vba

• Paul Sp

  SSCrazy

  Points: 2314

  More actions

  November 5, 2004 at 9:01 am

  #65230

  Hi

  I'm sure this is easy, but it is new to me. 

  I want to pass a variable to a stored procedure from Access.  The sp is something like:

  CREATE PROCEDURE ProcedureName (@Variable varchar(20))

  AS

  INSERT INTO Table

    SELECT Field1, Field2

    FROM Table2

    WHERE Field1 = @Variable

   

  In Access, I want the content of a field to provide the value of the variable executed on SQL Server 2000.  I have the connection string and I can execute the sp fine.  I just don't know the syntax to pass the variable.

   

  Can anybody please help

  Paul

• pacopico

  SSC Enthusiast

  Points: 188

  More actions

  November 5, 2004 at 10:16 am

  #529124

  hi paul,

  try this code

  dim sql as string

      sql = "{call ProcedureName(?)}"

      Set qdf = SQLLink.CreateQueryDef("", sql)

      With qdf

          .Parameters("@Variable") = TxtVariable

          .Execute

          .Close

      End With

  where Procedure name is the stored procedure and TxtVariable is the value to pass and SQLLink is connecting database to SQL. qdf is query defenition

  paco

• sqlSherwin

  SSCrazy

  Points: 2884

  More actions

  November 5, 2004 at 2:05 pm

  #529166

  Here is another option using ADO

  Dim cnn AS ADODB.Connection

  Dim cmd as ADODB.Command

  cnn.ConnectionString =""

  cnn.open

  with cmd

       .ActiveConnection = cnn

       .CommandType = adCmdStoredProc

       .CommandText = "ProcedureName"

       'Issue a refresh to get the named parameters

       .Parameters.Refresh

       .Parameters("@Variable") = txtVariable

       .Execute

  End With

• Paul Sp

  SSCrazy

  Points: 2314

  More actions

  November 8, 2004 at 4:13 am

  #529223

  Thanks to both of you. 

  Excellent solutions.  I found the ADO worked best for me for this particular problem and can now do what I needed to do.

  This is much appreciated.

  Many thanks again,

  Paul