Run MS Sproc with 4 parameters

[cheekybuddha]

Just to check: you did replace the server address with the correct details from hovering over a linked table?

 

[Tupacmoche]

.Execute dbFailOnError is the line that caused the error and
I did update the connection string.

 

[Galaxiom]

You are Executing on CreateQueryDef instead of the QueryDef itself.

You would need give it a Name property so it saves so you can then Execute it.

 

[cheekybuddha]

You are Executing on CreateQueryDef instead of the QueryDef itself.

You would need give it a Name property so it saves so you can then Execute it.

Not strictly true. CreateQueryDef returns a QueryDef object on which you can .Execute. If you don't give it a name (ie name it with an empty string) it exists only for the life of the object and isn't appended to the QueryDefs collection.

The problem with the code I posted in post #18 is that I forgot to pass the (empty) query name, assuming that the optional parameter would default to an empty string. (It's a long time since I played with any of this code! )

So, Tupacmoche, try adjusting to:

Private Sub btnCS_Click()
  Dim strSQL As String
  Dim strCn As String
  strCn = "ODBC;Description=XYZ;DRIVER=SQL Server;SERVER= XYZ;Trusted_Connection=Yes;APP=2007 Microsoft Office system;"
  strSQL = "usp_EgatePledgeSchedule " & Me.txt_strHM & ", " & Me.txt_strHMP & ", '" & _
              Format(Me.txt_strSD, "yyyy-mm-dd") & "', '" & Me.txt_strFreq & "', " & Me.txt_strGID & ";"   ' <-- Note I removed quotes around GID - it's declared as INT in the SP
  With CurrentDb.CreateQueryDef(vbNullString)    ' <-- pass empty string as query name
    .Connect = strCn
    .SQL = strSQL
    .ReturnsRecords = False
    .Execute dbFailOnError
  End With
End Sub

If you can get this to work, then we can look at creating something a bit more reusable.


hth,

d

 

[Tupacmoche]

Super everything is now working!

 

[cheekybuddha]

Super everything is now working!

That's great!

If you plan to have more SP's which you wish to run then it might be an idea to create a stored Pass-Through query. It can already contain the connection string, and you can create a function to change its SQL and execute it, passing the SQL/SP command as a parameter.



d