ADO Command Object calling stored procedure

[Jen123]

Hi,

I'm trying to call a stored procedure from AccessXP, that has a input parameter. The stored procedure has one input parameter @EventRef. This is what I have so far but the error message is Automation Error Unspecified Error -2147467259. Any ideas?

Dim EventRef As Integer
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Set conn = New ADODB.Connection
conn.Open ConnectionString
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdStoredProc
.CommandText = "MailEvent"
.Parameters.Append cmd.CreateParameter("EventID", adNumeric)
.Parameters("EventID&quot.Value = EventRef
.ActiveConnection = conn
End With
Set rs = cmd.Execute

 

[BanditWk]

Hi Jen...

Well, I'm not sure where the issue may lie, but you don't have any Error Trapping in your code that will help you alleviate the guess work. Try this:

100 On Error GoTo PROC_ERR

Dim EventRef As Integer
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

110 Set conn = New ADODB.Connection
120 conn.Open ConnectionString
130 Set rs = New ADODB.Recordset
140 Set cmd = New ADODB.Command
150 With cmd
160 .CommandType = adCmdStoredProc
170 .CommandText = "MailEvent"
180 .Parameters.Append cmd.CreateParameter("EventID",adNumeric)
190 .Parameters("EventID&quot.Value = EventRef
200 .ActiveConnection = conn
210 End With
220 Set rs = cmd.Execute

PROC_EXIT:
230 Exit Function

PROC_ERR:
240 msgbox Err.Description & Chr$(13) & Chr$(10) & "Error in Line " & Erl()
250 Resume PROC_EXIT


What this will do is tell you exactly WHERE the error is (which will alleviate the guesswork) and will give you the error message.

HTH

Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"No need to send gifts, just send a smile."

 

[Jen123]

ok, cheers. I'll put it in and come back to you.

 

[Jen123]

Hi Roy,

Well, for me the error message doesn't help to a great extent. It was this:

Method 'Execut of Object'_command' failed -2147467259. It falls over in the line set rs = cmd.execute.

Thanks for previous help, but any other ideas?

Jen

 

[Jen123]

Roy,

Cracked it.

Dim EventRef As Integer
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
conn.Open ConnectionString
Set rs = New ADODB.Recordset
rs.Open "MailEvent ( ' " & EventRef & " ')", conn, adOpenKeyset, adLockOptimistic, adCmdStoredProc

Cheers