vba ADO stored procedure in recordset 'Operation is not allowed when the object is closed'

by Mark Deraeve 12. September 2012 07:04

I was trying to open a recordset from a stored procedure. I had the hardest time gettings this to work.

When me and my collegue Koen looked for solutions, Koen Goyens found this solution that worked: Add NextRecordset before you use the recordset, because the first recordset will always be empty. Here's a function that will return the recordset when you give a parameter and the name of the stored procedure:

 

' ------------------------------------------------------------------------------------------
' ---   Function opens a recordset using a Stored Procedure passed as parameter (sSQL)    ---
' ------------------------------------------------------------------------------------------
  Public Function OpenADOStoredProcedureRecordset(ByVal sSQL As String) As ADODB.Recordset

    Dim tmpConn As ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim cmd As ADODB.Command

    On Error GoTo RecordsetError

    ' --- Get the active connection (it will be opened if it's closed) ---------------------
    Set tmpConn = ADOConnection
    LogInfo "Started Stored Procedure: " & sSQL
    Set RS = New ADODB.Recordset ' --- create new ADO recordset struct. & get data ------
    RS.Open sSQL, tmpConn, adOpenUnspecified, adLockUnspecified

    Set OpenADOStoredProcedureRecordset = RS.NextRecordset
        
    LogInfo "End Stored Procedure: " & sSQL

    Exit Function

RecordsetError:
    AdoStatus = False
    AdoErr = Err.Number
    AdoErrDescr = Err.Description
    LogError "Error in OpenADOStoredProcedureRecordset: " & Err.Description
    Set RS = Nothing
    Set cmd = Nothing

  End Function

 The parameter sSql looks like this: exec spoMyStoredProc '0145698725658'

So when I call this function I get my result in the recordset and can start looping over it.


Tags:

Comments (13) -

personal injury lawyers austin
personal injury lawyers austin United States
12/24/2013 1:11:46 PM #

Also stop by my weblog :: propecia - mcculloch.ciber.ulpgc.es/wiki/index.php/Usuario:GarlandBlackloc I am going to at once snatch your rss as I'm able to not in acquiring your email subscription hyperlink or newsletter service. Does one have any? Kindly allow me realize so that I may perhaps just subscribe. Many thanks.

personal injury lawyers austin
personal injury lawyers austin United States
1/4/2014 7:31:28 AM #

platform are you applying for this page? I'm having sick and tired

personal injury lawyers Austin
personal injury lawyers Austin United States
1/9/2014 8:18:17 AM #

Check out out my weblog :: lear funds gold bullion - stonersnetworking.com/.../ There is noticeably plenty of cash to comprehend relating to this. I assume you've got produced specified superior points in functions also. I like this online web site so significantly, saved to favorites . Thanks for give very very good informations. Your web web page is goodI am impressed by the specifics which you have on this weblog. It shows how effectively you realize this topic. Bookmarked this webpage, will come back for any good deal extra. You, my close friend, ROCK! I identified just the aspects I currently searched everywhere and just couldn There are unquestionably quite a lot of aspects like that to assume addicted to consideration. With the function of can be a great height to convey awake. I submit the concepts previously mentioned as natural inspiration but evidently right here are queries such as 1 you convey awake in which by far the most weighty fixation can ensue powerful in straightforward very good faith. I don?t identify if best procedures have emerged round possessions comparable that, within the contrary I am positive that your occupation is openly identified like a sunny match. All girls and boys certainly touch the impact of only a second's enjoyment, on behalf in the rest of their lives. Hey! Would you thoughts if I share your site with my facebook group? You will find lots of folks that i believe would definitely enjoy your written content. Be sure to permit me know. Many thanks

car accident attorney Austin
car accident attorney Austin United States
1/23/2014 6:20:20 PM #

Also go to my weblog - private school loan - thepreacher.info/.../guest_book.php This world-wide-web internet site is often a walk-through for all with the know-how you required concerning this and did not know who should. Glimpse right here, and you will unquestionably uncover it. Fantastic day! Does one know if they make any plugins to protect against hackers? I'm kinda paranoid about losing everything I've worked difficult on. Any guidelines? It's totally uncomplicated to find out any matter on net as compared to textbooks, as I found this paragraph at this website.

accident attorney Austin
accident attorney Austin United States
1/26/2014 11:42:14 AM #

My homepage: unstitched salwar kameez clothing garments - allinchrist.net/.../index.php extremely nice put up, i basically really like this internet site, retain on it Remarkable items listed here. I am quite glad to search your write-up. Many thanks so much and i am wanting forward to touch you. Will you remember to drop me a mail?

Austin injury attorneys
Austin injury attorneys United States
2/24/2014 11:56:48 PM #

I found your finest piece by using bing ..

Maile Massetti
Maile Massetti United States
3/5/2014 9:02:16 PM #

Whoa this was unusual. I just wrote an incredibly lengthy comment but once I clicked submit my comment couldn't display.  Ah... well I'm not writing that all over again. Regardless, just wished to say superb web site!

Graig Stanbaugh
Graig Stanbaugh United States
4/6/2014 11:07:24 PM #

That is definitely some incredibly interesting and refreshing information!

Lynsey Constantine
Lynsey Constantine United States
4/6/2014 11:07:26 PM #

Aw, this is a really quality article. In theory I'd like to generate such as this too. Nonetheless It's taking time as well as genuine work  to produce a good write-up... but what can I say... I procrastinate alot and not seem to get a thing accomplished.

Tajuana Seidel
Tajuana Seidel United States
4/6/2014 11:07:26 PM #

I was suggested this site by my friend. You are wonderful! Bless you!

Maisie Norton
Maisie Norton United States
4/30/2014 12:30:07 AM #

I'll gear this review to 2 types of people: current Zune owners who are considering an upgrade, and people trying to decide between a Zune and an iPod. (There are other players worth considering out there, like the Sony Walkman X,  And We Make HomePage And WebSite AT <A href="http://www.akibare-hp.jp">;ホ�Eムペ�Eジ作�E</A><A href="http://www.blogdehp.jp">;ビジネスブログホ�Eムペ�Eジ作�Eビジネスブログ</A>  but I hope this gives you enough info to make an informed decision of the Zune vs players other than the iPod line as well.)

us
us United States
5/23/2014 1:59:02 PM #

I was reading through some of your articles on this internet site and I conceive this internet site is rattling instructive! Retain putting up.

Phyllis Chloe MacKenzie
Phyllis Chloe MacKenzie United States
8/3/2014 11:29:10 AM #

I'll gear this review to 2 types of people: current Zune owners who are considering an upgrade, and people trying to decide between a Zune and an iPod. (There are other players worth considering out there, like the Sony Walkman X,  And We Make HomePage And WebSite AT�@<A href="www.akibare-hp.jp">www.akibare-hp.jp</A>;  but I hope this gives you enough info to make an informed decision of the Zune vs players other than the iPod line as well.)

About the author

I started as a VB6 developer, programming all kinds of applications. I also got involved in the development of a e-market in ASP.

When .Net was introduced, I had the privilege to start in a team of professionals and develop a framework. I learned a lot and started to work as a c#.Net consultant from then on.

After 2 years I also got the chance to work for several months with SharePoint. SharePoint kept chasing me all the years after and when the product got more and more mature I started to love it. Then SharePoint was a daily occupation for me. I implemented a large quality document management system at PMRL being the only developer/technical analyst. Working close together with the QA department we succeeded in making and improving a very good Quality document management system. Its used up until this day.

I worked for 6 years as a .Net consultant for 2 of the largest consultancy companies in Belgium. One of my last projects with the consultancy was creating a custom LIMS system in c#.Net. This was a success and the customer asked me to stay and help to expand and maintain this LIMS. I had great years in this company. There was a focus on innovation and a lot of opportunities for improving and developing new IS systems. Apart from several small projects I further more improved the custom LIMS system, created a SOP library based on SharePoint and a web based Skill matrix system to handle the skills of the employees in the company. 

Then I was involved in transferring the LIMS to a new lab in Singapore. This was done in a short time frame. The deployment was again a success and it passed the GLP inspection tests. I learned a lot from the Quality engineers about validation of computerized systems.

At my current job I have a wide range of challenges. Mainly I'm still working with .Net (VB.net and C#.Net). But outside of the Microsoft world I also do some ABAB programming in our SAP system. Like developing Adobe Forms and the program behind it. Or making small changes to customized code.

I'm working for 2 years with objective C now. Creating apps for factories across Europe. People in the factory are working with IPad to take pictures and upload them to orders and more. Already more and more projects are coming my way. The iOS apps are supported by a home made .Net architecture that exposes WCF services. Its working great. My second app is almost ready for deployment in factories. Now I started an App for the management team so they can manage projects on their IPAD and IPHONE. Next generation apps are build with a .Net Web API background combined with EF 6.0.

My linkedIn!

Month List

Tag cloud

Locations of visitors to this page