Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 20th, 2008, 10:21 PM
Registered User
 
Join Date: Jan 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO Create Procedure problem

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.

 
Old January 22nd, 2008, 12:56 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 22nd, 2008, 07:59 PM
Registered User
 
Join Date: Jan 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


 
Old January 23rd, 2008, 09:37 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i create a new ado database ? jerryham VB.NET 8 October 13th, 2008 09:21 PM
ado and oracle procedure problem plz help zrtv Pro VB Databases 9 February 28th, 2008 01:00 PM
Create Store Procedure ?? kumiko SQL Language 7 January 4th, 2008 02:11 AM
Problem In Parameterized ADO.NET Stored Procedure yoord ADO.NET 3 June 19th, 2004 05:09 AM
create procedure in chap 4 ghfinn BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 June 15th, 2004 08:09 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.