cheekybuddha
AWF VIP
- Local time
- Today, 12:52
- Joined
- Jul 21, 2014
- Messages
- 2,274
Just to check: you did replace the server address with the correct details from hovering over a linked table?
Run MS Sproc with 4 parameters (1 Viewer)
- Thread starter Tupacmoche
- Start date
Tupacmoche
Registered User.
- Local time
- Today, 07:52
- Joined
- Apr 28, 2008
- Messages
- 291
.Execute dbFailOnError is the line that caused the error and
I did update the connection string.
I did update the connection string.
cheekybuddha
AWF VIP
- Local time
- Today, 12:52
- Joined
- Jul 21, 2014
- Messages
- 2,274
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:
Code:
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 SubIf you can get this to work, then we can look at creating something a bit more reusable.
hth,
d
Tupacmoche
Registered User.
- Local time
- Today, 07:52
- Joined
- Apr 28, 2008
- Messages
- 291
Super everything is now working!
cheekybuddha
AWF VIP
- Local time
- Today, 12:52
- Joined
- Jul 21, 2014
- Messages
- 2,274
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
