12-03-2018, 01:27 PM
|
#1
|
Sr. Application Developer
Join Date: Jun 2011
Posts: 2,614
Thanks: 113
Thanked 295 Times in 283 Posts
|
Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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:
Code:
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,
__________________
--
Michael Lueck
Lueck Data Systems
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-03-2018, 01:40 PM
|
#2
|
Super Moderator
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,364
Thanks: 84
Thanked 1,428 Times in 1,347 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
No recordset involved to run a command on the server.
Simply set up the command then use:
|
|
|
12-03-2018, 01:58 PM
|
#3
|
Sr. Application Developer
Join Date: Jun 2011
Posts: 2,614
Thanks: 113
Thanked 295 Times in 283 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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,
__________________
--
Michael Lueck
Lueck Data Systems
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-03-2018, 02:58 PM
|
#4
|
Super Moderator
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,364
Thanks: 84
Thanked 1,428 Times in 1,347 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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
|
|
|
12-03-2018, 07:16 PM
|
#5
|
Sr. Application Developer
Join Date: Jun 2011
Posts: 2,614
Thanks: 113
Thanked 295 Times in 283 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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,
__________________
--
Michael Lueck
Lueck Data Systems
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-05-2018, 07:31 AM
|
#6
|
Sr. Application Developer
Join Date: Jun 2011
Posts: 2,614
Thanks: 113
Thanked 295 Times in 283 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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,
__________________
--
Michael Lueck
Lueck Data Systems
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-05-2018, 07:37 AM
|
#7
|
Part time moderator
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,298
Thanks: 99
Thanked 2,124 Times in 1,973 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.
Website links: To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. , To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. , To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. , To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10, To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. , To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-05-2018, 07:59 AM
|
#8
|
Sr. Application Developer
Join Date: Jun 2011
Posts: 2,614
Thanks: 113
Thanked 295 Times in 283 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
Quote:
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,
__________________
--
Michael Lueck
Lueck Data Systems
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-05-2018, 08:42 AM
|
#9
|
Premier Pale Stale Ale
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,129
Thanks: 79
Thanked 437 Times in 397 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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.)
__________________
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-05-2018, 08:52 AM
|
#10
|
Sr. Application Developer
Join Date: Jun 2011
Posts: 2,614
Thanks: 113
Thanked 295 Times in 283 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
Greetings Frothingslosh,
Quote:
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,
__________________
--
Michael Lueck
Lueck Data Systems
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-05-2018, 09:09 AM
|
#11
|
Premier Pale Stale Ale
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,129
Thanks: 79
Thanked 437 Times in 397 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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.
__________________
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-05-2018, 10:59 AM
|
#12
|
Sr. Application Developer
Join Date: Jun 2011
Posts: 2,614
Thanks: 113
Thanked 295 Times in 283 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
Greetings Frothingslosh,
Of course I will compare notes/code with your version.
I am thankful,
__________________
--
Michael Lueck
Lueck Data Systems
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-06-2018, 02:55 PM
|
#13
|
Sr. Application Developer
Join Date: Jun 2011
Posts: 2,614
Thanks: 113
Thanked 295 Times in 283 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
Greetings Scott,
Quote:
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,
__________________
--
Michael Lueck
Lueck Data Systems
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-06-2018, 03:05 PM
|
#14
|
Premier Pale Stale Ale
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,129
Thanks: 79
Thanked 437 Times in 397 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
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.
__________________
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
12-06-2018, 03:09 PM
|
#15
|
Premier Pale Stale Ale
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,129
Thanks: 79
Thanked 437 Times in 397 Posts
|
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies
And no, I don't recall what that 5-parter was, just that it was a complete Charlie Foxtrot and a nightmare to fix.
__________________
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
Thread Tools |
|
Display Modes |
Rate This Thread |
Linear Mode
|
|
All times are GMT -8. The time now is 09:44 PM.
|
|