executing a stored query

[darre1]

not having a good day today

i'm trying to execute a stored query (update query) in a access 2000 database with the following code.

Set comUpdate = New Command
    comUpdate.CommandType = adCmdStoredProc
    comUpdate.CommandText = "qryUPDWeeklyDataFile"
    comUpdate.Parameters.Append comUpdate.CreateParameter("parPublisher", adBSTR, _
        adParamInput, , com.Parameters("parPublisher"))
    comUpdate.Parameters.Append comUpdate.CreateParameter("parWeekYear", adBSTR, _
        adParamInput, , com.Parameters("parWeekYear"))
    comUpdate.ActiveConnection = cnTemp
    comUpdate.Execute
    Set comUpdate = Nothing

i've stepped through it and it goes straight through the code, no problems, no errors etc, but when i look at the records in the datdabase using access, nothing has changed.

NOTE: I had ran the query manually using access and specified the exact same parameter values (checked them using the immediate window) and it worked perfectly.

i'm lost!

[darre1]

nobody got a clue?

[darre1]

sokay i've sorted it now.

just used a nice long messy SQL statement instead

[djengiz]

Have you defined the parameters in your stored procedure in Access?

[djengiz]

try this

Dim objCon As ADODB.Connection
Set objCon = New ADODB.Connection
objCon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\bla.mdb;"
 
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cmd = CreateObject("ADODB.Command")
With cmd
    .CommandType = adCmdUnknown 'access does not know Store Procedure
    .CommandText = "your_procedure"
    .Parameters.Append .CreateParameter("param_1", adDouble, adParamInput, , <value of param_1>)
    .Parameters.Append .CreateParameter("param_2", adDouble, adParamInput, , <value of param_1>)
    .ActiveConnection = objCon
    Set rs = .Execute
End With
rs.Close
Set rs = Nothing
Set cmd = Nothing
objCon.Close
Set objCon = Nothing

[darre1]

i didn't know this bit :

".CommandType = adCmdUnknown 'access does not know Store Procedure
"

but i've been messing about with it for hours now, and i got it working just by using SQL so i'm gonna leave it for now

[djengiz]

Sorry I didnt see your post earlier

[darre1]

that's ok, your way sounds like your on the right track, i didnt know that bit anyway