Calling Sql Server SP with multiple parameters

[jsdba]

When i execute the following as a pass-through query it works

exec dbo.sp_MyStoredProc @Parameter1 = 1, @Parameter2 = 3

However i cant get this to execute in vba

Dim recordsAffected As Long
Set conn = New ADODB.Connection
conn.ConnectionString = "MyConnectionString"
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.sp_MyStoredProc"
cmd.Parameters.refresh
cmd(1) = 1
cmd(2) = 3
cmd.Execute recordsAffected

What am i missing from my VBA code? The store proc is an update statement and I know that it works.

How would i know if my connection string is the problem?

 

[Minty]

Why not just execute the pass through and modify it in VBA with your parameters? That's How I have normally done it.

I'm not sure the parameters can be modified in the way you are describing with the ADO connection - this seems to suggest the same;
https://pulseinfomatics.com/microsoft-access-ado-passing-parameters-sql-server/

 

[jsdba]

I'm not sure the parameters can be modified in the way you are describing with the ADO connection - this seems to suggest the same;
https://pulseinfomatics.com/microsoft-access-ado-passing-parameters-sql-server/

I've also tried the code below instead of cmd(1) with no luck

cmd.Parameters.Item("@Parameter1").Value = 1
cmd.Parameters.Item("@Parameter2").Value = 3
cmd.Execute

Also how do i do this?

Why not just execute the pass through and modify it in VBA with your parameters? That's How I have normally done it.

 

[Minty]

Just adjust the query def, something like (assuming you where taking the values from two controls on a form for instance);

    Dim db               As DAO.Database
    Dim rs               As DAO.Recordset
    Dim qdfpt            As QueryDef


    Set db = CurrentDb
    Set qdfpt = db.QueryDefs("[COLOR="Red"]passYourPassQuery[/COLOR]")
    qdfpt.SQL = "exec dbo.sp_MyStoredProc @Parameter1 = " & Me.Value1 & " , @Parameter2 = " & Me.Value2
    
    db.Close

    DoCmd.OpenQuery "passPogi", acViewNormal, acReadOnly

You don't have to name the parameters if you know the order they are presented in so you could just effectively write

exec dbo.sp_MyStoredProc 1, 3

 

[jsdba]

Just adjust the query def, something like (assuming you where taking the values from two controls on a form for instance);

    Dim db               As DAO.Database
    Dim rs               As DAO.Recordset
    Dim qdfpt            As QueryDef


    Set db = CurrentDb
  [B]  Set qdfpt = db.QueryDefs("[COLOR="Red"]passYourPassQuery[/COLOR]")[/B]
    qdfpt.SQL = "exec dbo.sp_MyStoredProc @Parameter1 = " & Me.Value1 & " , @Parameter2 = " & Me.Value2
    
    db.Close

    [B]DoCmd.OpenQuery "passPogi", acViewNormal, acReadOnly[/B]

I have little experience using QueryDefs, what should i put in "passYourPassQuery"?
Also is "passPogi" the pass-through query? If yes what is the sql in the query?

 

[jsdba]

Got it to work using QueryDefs. however i still don't understand why adodb command execute wont work. See link below, i tested this in my own application and it works for SELECTstore procs but not UPDATE

http://docs.microsoft.com/en-us/sql/ado/guide/data/calling-a-stored-procedure-with-a-command?view=sql-server-2017

 

[sonic8]

What am i missing from my VBA code?

You are missing:

conn.Open

If your connection string is incorrect you'll get an error on that line.
​

 

[jsdba]

You are missing:

conn.Open

If your connection string is incorrect you'll get an error on that line.
​

Thank you. This have been driving me crazy. Now it works