How can I execute an action query with ADO?

[Ben Mann]

Hi,

I have successfully been using recordsets based on MS-Access SELECT queries
for some time now. However, I am having problems running DELETE and INSERT
queries using ADO. Does anyone have sample VB code that shows how to do
this?

TIA,

Ben Mann
etc2000 Ltd.
b...@etc2000.co.uk

[Dave Carr]

Dim cm As ADODB.Command

Set cm = New ADODB.Command

cm.ActiveConnection = gcnApp '<a reference to a connection>
cm.CommandType = adCmdText
cm.CommandText = "update Tbl set Fld = 'TEST'"

cm.Execute

There are other ways (if you are using a data environment) but I prefer
this.
HTH,
-Dave
dcarr<at>gr<dot>com

Ben Mann wrote in message
<917372554.22239.0...@news.demon.co.uk>...

[Ben Mann]

Thanks.

What do I set 'CommandType' to if I want to execute a stored query (Not an
SQL string)?

Also, I'd be interested to know how to do this with a DataEnvironment if
possible.

TIA.

Ben Mann
etc2000 Ltd.
b...@etc2000.co.uk

Dave Carr wrote in message <78kvsj$mpl$1...@mercury.cargill.com>...

[Dave Carr]

I haven't done this with a data environment, but it is my understanding
that you can store stored procedures and other things as part of the
environment. Here's a quote from another htread that may get you
headed in the right direction:

The easiest way is to add a data environment to your project and it
will help step you through setting up a connection. Right click in the
project window, select Add..., select Data Environment. When the
Data Environment window appears, there are buttons for adding
commands and inserting SP's.

-Dave
Greenbrier & Russel, Inc.

dcarr<at>gr<dot>com

Ben Mann wrote in message
<917435033.29269.0...@news.demon.co.uk>...

[Shawn Brock]

> What do I set 'CommandType' to if I want to execute a stored query (Not
an
> SQL string)?
>
> Also, I'd be interested to know how to do this with a DataEnvironment if
> possible.
>

You should be able to use the Open method of the recordset object and
specify the procedure as the Source. adCmdStoredProc should be specified
as the option parameter I believe.
--
Shawn Brock
sdb...@ssallc.com

[ASP Kid]

I'm using the DataEnvironment and I never been able to execute an action
query stored in Access. I use the following turnaround: I cut and paste the
SQL action query into my command object. You can execute it this way:

DataEnvironment1.MyActionQuery

where

MyActionQuery is the name of your command.

Ben Mann a écrit dans le message

<917372554.22239.0...@news.demon.co.uk>...
>Hi,
>
>I have successfully been using recordsets based on MS-Access SELECT
queries
>for some time now. However, I am having problems running DELETE and INSERT
>queries using ADO. Does anyone have sample VB code that shows how to do
>this?
>
>TIA,
>

[Aaron Light]

SQL = "DELETE FROM myTable WHERE custID = 12"

connADO.Execute(SQL)

Ben Mann wrote in message

[Eric Swartz]

Well, I've never used a delete query before... but I know your "where" statement
is incorrect. You need to have appostrophies around the 12. Example:

SQL = "Delete from myTable where custID = '12'"

Eric

[Stefan Zschocke]

Eric Swartz wrote in message <36BF14E4...@caeransohoff.com>...

>Well, I've never used a delete query before... but I know your "where"
statement
>is incorrect. You need to have appostrophies around the 12. Example:
>
>SQL = "Delete from myTable where custID = '12'"
>

Sorry, but this is nonsense. If custId is numeric, then you don't need the
quotes. Only if it is a string, which I doubt is in this case.
Stefan

[Ben Mann]

Thanks,

The Action queries I'm trying to use are stored as queries in MS-Access.
I can't run the SQL from VB because I need to maintain the query's in
Access.
ADO doesn't seem to like these queries because they don't return recordsets.

Can anyone tell me how to do this?

TIA,

Ben Mann
etc2000 Ltd.
b...@etc2000.co.uk

Aaron Light wrote in message ...

[Stefan Zschocke]

Try something like
Dim c As New ADODB.Connection
c.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\temp\yourdatabase.mdb"
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = c
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "YourQuery"
cmd.Execute
Stefan

Ben Mann wrote in message

<918556870.26376.0...@news.demon.co.uk>...

[Scott Swigart]

That seems logical, unfortunately it doesn't work. To the best of my
knowledge (and I spent a couple of days trying to solve this problem)
there's no way to execute an action query that's stored in Access
using ADO.

I came up with a pretty horrible work around, but it does the job.
Essentially, I open the database schema, find the query by name,
retrieve the definition for the query (which is the SQL statement) and
execute that:

Dim StoredProcs as Recordset
Dim cn as Connection
Dim cmd as Command

Private Sub CallStoredProc(procName As String)
'
' Load in the names of all the action queries
'
If StoredProcs Is Nothing Then
Set StoredProcs = cn.OpenSchema(adSchemaProcedures)
Do Until StoredProcs.EOF
Debug.Print StoredProcs("procedure_name")
StoredProcs.MoveNext
Loop
End If
Dim sql As String
'
' Find the desired query by name
'
StoredProcs.MoveFirst
StoredProcs.Find "procedure_name = '" & procName & "'"
If StoredProcs.EOF Then
'
' Query wasn't found
'
Err.Raise errCantFindStoredProc, "CallStoredProc", "The stored
procedure '" & procName & "' does not exist."
End If
'
' Get the SQL for the query
'
sql = StoredProcs("PROCEDURE_DEFINITION")
'
' Execute the query
'
Debug.Print "Calling " & procName
cmd.execute sql
End Sub

[Ben Mann]

As you said, it's a horrible work around, but it does solve my immediate
problem.

I'm disappointed that there's no easy way to do this with ADO. This isn't
the only problem I've had with ADO and I'd seriously recommend using DAO for
Jet development at present.

Thanks for your help.

Ben Mann

Scott Swigart wrote in message <36c0d830...@news.teleport.com>...

[Jason Catlett]

Sorry I'm coming in here late. Some of the first messages in this thread
have expired.
What version of ADO are you using? I am successfully using VB6,ADO 2.1 and
Access 97 to execute select, update, append, and delete queries all
predefined in Access.

Are you by any chance trying to use parameterized queries? If so I had a
lot of trouble getting those to work myself. Either way, please post or
email me your code that's not working as well as relevant database info.
I'd be more than happy to try and help you get this working without hacking
it.

Jason Catlett
First Edge

The following code works for me.

Dim cmd As adodb.Command
Dim rs As adodb.Recordset
Dim pm As adodb.Parameter
Dim lngRecordsAffected As Long

Set cmd +AD0- New adodb.Command

Select Case Action
Case actInitialize
Case actLoad
cmd.CommandText +AD0- +ACI-VCompany+ACI-
cmd.CommandType +AD0- adCmdStoredProc
Set pm +AD0- cmd.CreateParameter(Name:+AD0AIg-CompKey+ACI-, Type:+AD0-adBSTR,
Value:+AD0-Company.CompKey)
cmd.Parameters.Append pm
Set pm +AD0- cmd.CreateParameter(Name:+AD0AIg-PolicyPeriodKey+ACI-,
Type:+AD0-adNumeric, Value:+AD0-Company.PolPeriodKey)
cmd.Parameters.Append pm

Set cmd.ActiveConnection +AD0- GetConnection()
Set rs +AD0- cmd.Execute
If Not (rs.BOF And rs.EOF) Then
CompanyRStoUDT rs, Company 'Private function to transfer to UDT
ActOnCompany +AD0- True
Else
ActOnCompany +AD0- False
End If

rs.Close
cmd.ActiveConnection.Close
Set rs +AD0- Nothing

Case actSave
cmd.CommandText +AD0- +ACI-UCompany+ACI-
cmd.CommandType +AD0- adCmdStoredProc
UCompanyParameters cmd, Company
Set cmd.ActiveConnection +AD0- GetConnection()
cmd.Execute lngRecordsAffected

If lngRecordsAffected +AD4- 0 Then
ActOnCompany +AD0- True
Else
ActOnCompany +AD0- False
End If

cmd.ActiveConnection.Close

Case actDelete
Case Else
End Select 'Action

Set cmd +AD0- Nothing

End Function

Private Function ConnectString() As String
ConnectString +AD0- +ACI-Provider+AD0-Microsoft.Jet.OLEDB.4.0+ADs-Data
Source+AD0-'C:+AFw-program files+AFw-company manager+AFw-jhtest.mdb'+ADsAIg-

End Function

Private Function UserID() As String
UserID +AD0- +ACI-Admin+ACI-
End Function

Private Function PassWord() As String
PassWord +AD0- +ACIAIg-
End Function

Private Function GetConnection() As adodb.Connection
Dim lngRecordsAffected As Long

Set GetConnection +AD0- New adodb.Connection
GetConnection.ConnectionString +AD0- ConnectString()
GetConnection.Properties(+ACI-User ID+ACI-) +AD0- UserID()
GetConnection.Properties(+ACI-PassWord+ACI-) +AD0- PassWord()
GetConnection.Open

End Function

Private Function UCompanyParameters(cmd As adodb.Command, udt As udtCompany)
Dim pm As adodb.Parameter

Set pm +AD0- cmd.CreateParameter(Name:+AD0AIg-CompKey+ACI-, Type:+AD0-adBSTR,
Value:+AD0-udt.CompKey)
cmd.Parameters.Append pm
Set pm +AD0- cmd.CreateParameter(Name:+AD0AIg-PolicyPeriodKey+ACI-, Type:+AD0-adNumeric,
Value:+AD0-udt.PolPeriodKey)
cmd.Parameters.Append pm
'+ADw-SNIP+AD4-
'forty other paramaters added the same way
End function

[Aaron Light]

12 is an integer moron therefore needs NO QUOTES!

Eric Swartz wrote in message <36BF14E4...@caeransohoff.com>...
>Well, I've never used a delete query before... but I know your "where"
statement
>is incorrect. You need to have appostrophies around the 12. Example:
>
>SQL = "Delete from myTable where custID = '12'"
>

>Eric

>
>
>Aaron Light wrote:
>
>> SQL = "DELETE FROM myTable WHERE custID = 12"
>>
>> connADO.Execute(SQL)
>>

>> Ben Mann wrote in message

>> <917372554.22239.0...@news.demon.co.uk>...
>> >Hi,
>> >
>> >I have successfully been using recordsets based on MS-Access SELECT
>> queries
>> >for some time now. However, I am having problems running DELETE and
INSERT

>> >queries using ADO. Does anyone have sample VB code that shows how to do