Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies

[mdlueck]

Greetings,



Previously we got working:
ADO.Command + Disconnected ADO.Recordset for SELECT
https://www.access-programmers.co.uk...=2#post1318234

I am trying to arrive at similar working VBA code to perform a SQL INSERT or SQL UPDATE such that I can have the ADO.Command object perform the command I have loaded into it, and it does not expect to output a new ADO.Recordset object.

I keep running into errors attempting such for a SQL UPDATE along the lines of:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. -2147217887

So, for SQL INSERT / UPDATE commands, in order to use an ADO.Command and ADO.Paramenters objects, MUST I run the command via the:

  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
     ...
    Set adoRS = .Execute()
  End With

  With adoRS
    ...
  End With

syntax allowing the adoCMD object to create a brand new adoRS object? Will only SQL SELECT statements work with the syntax style in the post mentioned above?

Does use of provided ADO.Recordset objects only work with ADO.Command objects for SELECT SQL queries?

I am thankful,

[Galaxiom]

No recordset involved to run a command on the server.
Simply set up the command then use:

adoCMD.Execute

[mdlueck]

Greetings Galaxiom,

I am angling to retain the RecordCount of the command's impact on a given table. For adoCMD objects that indeed would be the:

Set recordset = command.Execute( RecordsAffected, Parameters, Options )

syntax.

That syntax has the adoCMD returning a brand new adoRS object.

I was seeking to fabricate my own. I cannot seem to get that syntax style to work... and I got thinking in my OP, there might not be a way to use fabricated adoCMD / adoRS objects with other SQL queries than SELECT. Is that indeed the case... impossible to use that VBA style/syntax for an INSERT or UPDATE?

Thus I need to use one VBA coding style for SQL SELECT (linked to in my OP), and the other VBA coding style for SQL INSERT / UPDATE?

I was hoping to get one consistent VBA coding style that would work for all three SQL SELECT / INSERT / UPDATE.

I am thankful,

[Galaxiom]

You have to define the return value as a parameter. It is a scalar value rather than a recordset.

https://stackoverflow.com/questions/...ored-procedure

[mdlueck]

No, sorry Galaxiom... you are missing my point.

For a SQL SELECT, we were able to use the ADO Disconnected Recordset and it executes flawlessly.

I tried swapping the SQL to a SQL UPDATE, and the same VBA blows up with the error cited in the OP of this thread.

So I am thinking SQL SELECT is able to be run via VBA code with an ADO Disconnected RecordSet, but I am thinking I cannot run SQL INSERT / UPDATE statements with that style of VBA code. I must code the VBA where I receive back a brand new adoRS object returned by the adoCMD.Execute method.

Is this the correct case?

I was trying to get a consistent VBA style which would work for SELECT / INSERT / UPDATE, however I suspect I will have to have two... one for SELECT, the other for INSERT / UPDATE.

I am thankful,

[mdlueck]

Coming back to this investigation fresh... I find that the ADO Disconnected Recordset VBA style code for SQL SELECT also produces an adoRS object with two errors listed in it after it successfully processes.

I tried again the ADO Disconnected Recordset VBA style code for SQL UPDATE... sure enough, it works, just cites the two errors in the adoRS object.

And... RecordsAffected is no where in sight.

So I am thinking to have two styles of ADO code... one for SELECT, the other for INSERT / UPDATE.

For SQL INSERT / UPDATE, it is not like the code is actually doing anything with the adoRS object that gets returned by the adoCMD .Execute method.

Still, an interesting find to note that it is possible to get at least SQL SELECT and UPDATE statements to work with ADO Disconnected Recordset VBA style code.

I am thankful,

[isladogs]

Any reason why you don't just use a DAO.recordset or better still just link the tables and run your action SQL statements / queries?

[mdlueck]

Originally Posted by isladogs

Any reason why you don't just use a DAO.recordset or better still just link the tables and run your action SQL statements / queries?

Greetings isladogs,

I have found DAO objects to be more quirky when it comes to error handling, ADO objects more robust. With DAO objects, I must have more of my error handler code repeated in the section of the code actually running the DAO object based query... trying to hand off the Err. object to the centralized error handler found the error getting reset / wiped clean.

About my main use of DAO objects is nesting two DAO.QueryDef objects... inner object is the BE DB query, and outer object is a FE query mapping columns from the inner query to FE temp table fields. I have never found an ADO way to do that.

And I very much dislike having linked table objects in the FE DB. Too much risk for people to go tinkering in tables. My first choice is to Stored Proceduralize everything. When I cannot put SP's on the DB, then I use Pass-Through SQL.

I am thankful,

[Frothingslosh]

I use an error handler class all the time and have zero issue with error codes being lost. I've attached an accdb file containing everything needed to this post if you want to try it. (I've had to do a couple modifications to make it business-neutral, but they're pretty basic and rather obvious.)

[mdlueck]

Greetings Frothingslosh,

Originally Posted by Frothingslosh

I use an error handler class all the time and have zero issue with error codes being lost.

I looked back up what the sharp spot was with DAO Centralized Error Handling...

Interrogation of DAO object details in the Error Handler
http://www.access-programmers.co.uk/...d.php?t=246627

Ah, it was not the Err. object that was getting reset, rather it was the DAO.Errors object. Memory gets fuzzy after five years.

The code works reliably... just more verbose than the ADO equivalent solution.

I am thankful,

[Frothingslosh]

Perhaps you should look at the code I supplied. It specifically does what you say it can't.

ETA: All you need in a procedure's error handler is to call the class's Handle method, and supply the module and procedure names. The class handles the rest.

[mdlueck]

Greetings Frothingslosh,

Of course I will compare notes/code with your version.

I am thankful,

[mdlueck]

Greetings Scott,

Originally Posted by Frothingslosh

Perhaps you should look at the code I supplied. It specifically does what you say it can't

Interesting you were successfully able to raise the error over to your error handler class and grab the current error at that point out of the global DBengine.

For ADO, to my knowledge there is no global ADO engine within Access. I must pass the ADO object to the error handler.

I tried to do the same with DAO error handling... and the moment the receiving error handling code got the DAO object, the error condition is wiped / cleared.

Good to know that the DAO error may be obtained out of the global DBengine. I will give it a try later... come up with a condition which stacks two error in the DAO error handler, see if I can successfully fetch them out of the global DBengine.

I am thankful,

[Frothingslosh]

The key is that you make bloody well certain that you don't reset the error collection, which means no execution of any kind of On Error statement. Maybe passing the DAO object did that on its own. Also, note that my class is very, very basic and lacks error handling of its own. I made it as simple as I could in order to (as well as I could) ensure that the handler doesn't generate its own error.

Using that error handler, I have successfully reported 4- and 5-part SQL Server errors, and every error from SQL Server includes at least two errors, as the first one is the basic 'failure' one that is what most error handlers display.

[Frothingslosh]

And no, I don't recall what that 5-parter was, just that it was a complete Charlie Foxtrot and a nightmare to fix.