Hi, guys: I'm new here.
I got a problem with using Create Procedure in ADO.
here is my code:

Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection

Conn.Execute "CREATE PROCEDURE XX(fld1 Char) AS " & _
                "UPDATE mytbl SET fld1 = -99"
Conn.Execute "EXECUTE XX '[my field]'"

this program ends up with error: Operation must use
updateable query.

but if I run the following below w/o procedure, it works
Conn.Execute "UPDATE mytbl set [my field] = -99"

I don't have any idea why this?
I'm wondering who knows what's going on here and
help me.
any help appreciated, thanks a lot.

What DBMS are you using for this? Is this Access/Access, or Access/SQL?

Why do you need to create a stored procedure at runtime rather than just running the query? Usually you create the stored procedure using the query designer in ADPs, and then execute the stored procedure as needed instead of a query.


mmcdonal

Look it up at: http://wrox.books24x7.com

Hi, mmcdonal, Thanks for your response.
I'm using ACCESS VBA 2003. I created this update
procedure so that I can call it with different parameters.
Since I need to call this update procedure many
times, that's why I create one at runtime.
I am just wondering CREATE PROCEDURE supports
UPDATE query. Probably not. because when I use stored
procedure with select, delete query, it works fine.

anyway, thanks a lot.

Access does not support Stored Procedures. So if your data tables are in an Access database, then this will not work. If your tables are in a SQL Server database, then this could work.

If you want to reuse this code, you can always put the update in a function in a module, and then call the function from your various events. That would look something like:

Public iField As Integer

Function Updatemytbl(iField As Integer)
Dim rs As ADODB.Recordset
Dim sSQL As String

sSQL = "UPDATE mytbl SET fld1 = " & iField

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

End Function

Then when you call it, do this, for example on a button's On Click event:

iField = Me.cboFieldCombo

Updatemytbl(iField)

This would give you some of the function you are looking for in reusable code.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com