Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-03-2018, 01:27 PM   #1
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,609
Thanks: 112
Thanked 295 Times in 283 Posts
mdlueck will become famous soon enough mdlueck will become famous soon enough
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.
mdlueck is offline   Reply With Quote
Old 12-03-2018, 01:40 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,329
Thanks: 80
Thanked 1,418 Times in 1,338 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
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:
Code:
adoCMD.Execute
Galaxiom is offline   Reply With Quote
Old 12-03-2018, 01:58 PM   #3
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,609
Thanks: 112
Thanked 295 Times in 283 Posts
mdlueck will become famous soon enough mdlueck will become famous soon enough
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.
mdlueck is offline   Reply With Quote
Old 12-03-2018, 02:58 PM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,329
Thanks: 80
Thanked 1,418 Times in 1,338 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
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
Galaxiom is offline   Reply With Quote
Old 12-03-2018, 07:16 PM   #5
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,609
Thanks: 112
Thanked 295 Times in 283 Posts
mdlueck will become famous soon enough mdlueck will become famous soon enough
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.
mdlueck is offline   Reply With Quote
Old 12-05-2018, 07:31 AM   #6
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,609
Thanks: 112
Thanked 295 Times in 283 Posts
mdlueck will become famous soon enough mdlueck will become famous soon enough
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.
mdlueck is offline   Reply With Quote
Old 12-05-2018, 07:37 AM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,439
Thanks: 92
Thanked 1,831 Times in 1,701 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
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
Previously known as ridders : 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.
isladogs is offline   Reply With Quote
Old 12-05-2018, 07:59 AM   #8
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,609
Thanks: 112
Thanked 295 Times in 283 Posts
mdlueck will become famous soon enough mdlueck will become famous soon enough
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies

Quote:
Originally Posted by isladogs View Post
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.
mdlueck is offline   Reply With Quote
Old 12-05-2018, 08:42 AM   #9
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,010
Thanks: 76
Thanked 419 Times in 379 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
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.)
Attached Files
File Type: accdb ErrHandler.accdb (392.0 KB, 10 views)
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Old 12-05-2018, 08:52 AM   #10
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,609
Thanks: 112
Thanked 295 Times in 283 Posts
mdlueck will become famous soon enough mdlueck will become famous soon enough
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies

Greetings Frothingslosh,

Quote:
Originally Posted by Frothingslosh View Post
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.
mdlueck is offline   Reply With Quote
Old 12-05-2018, 09:09 AM   #11
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,010
Thanks: 76
Thanked 419 Times in 379 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
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.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Old 12-05-2018, 10:59 AM   #12
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,609
Thanks: 112
Thanked 295 Times in 283 Posts
mdlueck will become famous soon enough mdlueck will become famous soon enough
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.
mdlueck is offline   Reply With Quote
Old 12-06-2018, 02:55 PM   #13
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,609
Thanks: 112
Thanked 295 Times in 283 Posts
mdlueck will become famous soon enough mdlueck will become famous soon enough
Re: Attempting to use ADO Disconnected Recordset VBA style for non SELECT queies

Greetings Scott,

Quote:
Originally Posted by Frothingslosh View Post
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.
mdlueck is offline   Reply With Quote
Old 12-06-2018, 03:05 PM   #14
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,010
Thanks: 76
Thanked 419 Times in 379 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
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.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Old 12-06-2018, 03:09 PM   #15
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,010
Thanks: 76
Thanked 419 Times in 379 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
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.

__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
disconnected ADO recordset as report source dclosure Visual Basic 1 09-28-2012 04:11 AM
Filters fails with ado disconnected recordset awf.franco General 1 06-09-2010 04:49 AM
UpdateBatch on disconnected recordset butler01 Modules & VBA 0 09-10-2009 07:48 AM
Show Disconnected Recordset in ListBox jal Modules & VBA 2 12-28-2008 06:02 PM
Set forms recordset to a disconnected ADO recordset Kodo Modules & VBA 7 06-07-2004 12:23 PM




All times are GMT -8. The time now is 11:44 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World