How to run Access action query in code using ADO ? 7

[surotkin]

Hi,
could anyone tell me how to run Access action (append) query in code using ADO ?
I don't want to use a SQL-string , since this SQL-string is too long (it involves many field to be updated).
Thank you.
Surotkin

 

[vbajock]

How far along with ADO are you? Do you know how to establish a connection, open a table, etc ?

 

[surotkin]

Hi vbajock,
yes, I know how to establish a connection, open a table, etc
I am converting my DAO-application to ADO.
Surotkin

 

[vbajock]

ok, then you would create two records sets, lets call one rsSource that has the data, and rsTarget that gets the data.

use the .Addnew method like so:

Do

RsTarget.addnew
RsTarget!SomeField=RsSource!SomeFieldJustLikeIT
RsTarget!SomeField1=RsSource!SomeFieldJustLikeIT1
'etc
.Update
RsSource.movenext

Loop until RsSource.eof
If your insert query had criteria, use IF...Then... END IF
to qualify your records.

 

[surotkin]

Hi vbajock,
it is good to see you after new year!
Thank you for your answer, however I know how to append records using code. My question is - is it possible to run my old action (append) query in code using ADO? I have my old append query and I want to run it within code.
Surotkin

 

[cmmrfrds]

The query can be accessed through the View Collection. You will need a reference to the ADOX library. Here is an example that uses the Open method of the recordset, but the Execute method could be used as well.

Dim cn As New adodb.Connection, sql1 As String
Dim rs As adodb.Recordset, connString As String, bdate As String
Set rs = New adodb.Recordset

''- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim v As View
Dim vn As View
''For Each v In cg.Views
''Debug.Print "views = "; v.Name
'' If v.Name = "query1" Then
'' Set vn = v
'' End If
''Next
Set vn = cg.Views("query1&quot

rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly

 

[vbajock]

If you are trying to do something like

rn.execute "qrySomeQuery", I don't think that is possible in ADO. Everything I have seen shows a SQL string has to be passed to the execute method.

Why not just use the docmd.openquery "qrySomeQuery" to execute your action query?

 

[cmmrfrds]

cn.Execute vn.Name

I believe as long as the variable vn.name resolves to a valid sql statement this will work.

 

[vbajock]

cmmrfrds, I tried your method and it only returns non-action queries as members of the Views collection.

 

[cmmrfrds]

Thank you vbajock. I have only used it for Select queries. Normally, I don't store actions sql queries, but create them dynamically.

 

[surotkin]

Hi vbajock,
unfortunatelly you can not use docmd.openquery "qrySomeQuery" with parameters. But I need to pass parameters with my action query.
Anyway, thank you and cmmrfrds for trying to help me.
If you have any other ideas let me know using this thread.
Best regards.
Surotkin

 

[oharab]

This is an example of why you shouldn't change your code to ADO from DAO unless you've a reason to.
That said,

http://msdn.microsoft.com/library/d...s/vbcon98/html/vbconperformingactionquery.asp

and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdamth02_4.asp

should help you do what you want to do. There's even a full example at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaexamples_vb01_4.asp

Let us know how you get on.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[vbajock]

When I've had a problem like that, I create a single record table, store the parameter values into it when they are collected, and then use dlookup("[SomeParameterField],"myParamterTable&quot in the criteria column of the query, or I use joins on the parameter table.
Then one can use the openquery method to achieve the same results as passing parameters to a stored procedure. For some reason Access's ADO implementation doesn't support querydefs as stored procedure objects, which is the root of the problem.

 

[cmmrfrds]

vbajock, although I did't test it out, I believe the action queries (procedures) reside in the Procedures Collection. This could be applied similiar to the way the View is applied with the Open or Execute methods. Iterate through the procedures collection and you will probably find the action queries.

 

[vbajock]

Ok, that worked. If you use the open method, it executes the query, then automatically closes the object.
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordset
''- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection
Dim v As Procedure

For Each v In cg.Procedures
If v.Name = "Query1" Then
rs.Open v.Name, CurrentProject.Connection
exit for
End If
Next

I bet if you played with it long enough you could figure how to append the parameters to the existing query via the command object

 

[Quehay]

You CAN execute queries using ADO (but I'd agree with Ben that DAO is usually better with any native Access operations).

I created an append query as "qry_test_ADO" in the Access GUI:
[tt]
INSERT INTO applications ( APP_ID, NAME )
VALUES (444, 'a new value');[/tt]

Then here's how to execute using ADO:

[tt]

Sub testADO()
Dim cmd As New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qry_test_ado"
cmd.Execute

End Sub
[/tt]

Here's ASP code I used with parameters. The "rows updated" parameter is an output parameter to get a feedback on success. It's essentially the same in the VBA context. You'll need to do some homework on using parameters because there are a number of approaches, consequences, and bugs involved.

[tt]Sub CheckForReviewUpdate

Dim strMode
Dim strVisitID
Dim Prm_VisitGUID
Dim Prm_RowsUpdated
Dim Cmd_UPDATE
Dim intRowsUpdated

intRowsUpdated = 0

strMode = Request.ServerVariables("REQUEST_METHOD&quot

If strMode = "GET" Then

'Do nothing

ElseIf strMode = "POST" Then

strVisitID = Request.Form("visit_id&quot

set Cmd_UPDATE = Server.CreateObject("adodb.command&quot

Cmd_UPDATE.ActiveConnection = Application("CONN_STRING&quot
Cmd_UPDATE.CommandType = adCmdStoredProc
Cmd_UPDATE.CommandText = "usp_update_review"

Set Prm_VisitGUID = Cmd_UPDATE.CreateParameter ("@visit_guid",adChar,adParamInput,38,strVisitID)
Cmd_UPDATE.Parameters.Append Prm_VisitGUID

Set Prm_RowsUpdated = Cmd_UPDATE.CreateParameter ("@rows_updated",adTinyInt,adParamOutput,,0)
Cmd_UPDATE.Parameters.Append Prm_RowsUpdated

Cmd_UPDATE.Execute,,adExecuteNoRecords

intRowsUpdated = Cmd_UPDATE.Parameters("@rows_updated&quot

If intRowsUpdated = 1 Then

Response.Redirect ("list.asp&quot

Else

Response.Write (&quot;<font color=FF0000 size=2>An error occurred while processing the review update. &quot; & _
&quot;Please try again or contact the administrator if error persists.</font>&quot

End If

End If

End Sub 'CheckForReviewUpdate[/tt]

Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.InsightDataConsulting.com

 

[cmmrfrds]

Thank you vbajock and quehay for taking the time to show and test out the different methods. It appears from quehay's use of the execute method with the command object that the execute method will handle the action query as opposed to the execute method of the connection object.

 

[Buj123]

Hi,
I am not quite expert in this, but i do need to sugest one thing.
To Run the action queries in VBA we can use

Docmd.RunSql (Queryname)

Thnaks