PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VBA-Excel error 3704- operation is not allowed when the object is closed-VBForums
Results 1 to 10 of 10

Thread: VBA-Excel error 3704- operation is not allowed when the object is closed

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    4

    VBA-Excel error 3704- operation is not allowed when the object is closed

    hi,

    i am using VBA(excel-macros) to pull data from MS-Sql 2000. There is a common module that connects to the database and calls different stored procedures.

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim Parameter As String
    Set cnn = New ADODB.Connection 'Create Connection
    Set rst = New ADODB.Recordset 'Create Recordset

    With cnn
    cnn.Open "Provider=SQLOLEDB; " .............."
    End With

    Set rst = cnn.Execute("EXEC [dbo].[" + NameProcedure + "] " & Parameter)

    If rst.EOF = True Then
    '
    '
    Now at this point(rst.EOF), i'm getting the error.

    The parameters passed 'NameProcedure'(name of the stored procedure in SQL) and 'Parameter' comes from other modules which calls this module to execute on SQL. Different modules provides different 'NameProcedure' for this execute statement.

    There is no problem with the connection because the same code is working fine for modules that call other stored procedures. Even when i run the Stored procedure on SQL, it is working fine.

    The problem is when one particular stored procedure (which is giving proper results when run on sql) is being made to run.

  2. #2
    Addicted Member
    Join Date
    Jan 2009
    Posts
    233

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    What's the error message? print screen the error with no private data
    The taller the bamboo grows the lower it bends...

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    4

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    here is the screen-shot:
    Attached Images Attached Images  

  4. #4
    Addicted Member
    Join Date
    Jan 2009
    Posts
    233

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    check out this link..it might give you an idea

    http://visualbasic.ittoolbox.com/gro...50?cv=expanded
    The taller the bamboo grows the lower it bends...

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    4

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    hey dude thanx for replying. that link didn't help much

    what i'm looking for is : wat might be the possible reasons for this kind of behaviour where even though the sql stopred proc is working fine, the result set is not transfered from the back end to the front end. And there is no problem with the connection either because the other modules are working fine.

    Hoping u might be able to help me out in this

  6. #6
    Addicted Member
    Join Date
    Jan 2009
    Posts
    233

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    have you check all the fields and tables..maybe some data fields doesn't match? it could result to this kind of problem...
    The taller the bamboo grows the lower it bends...

  7. #7
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    Not Sure but try something like this...

    vb Code:
    1. Dim cnn As ADODB.Connection
    2. Dim rst As ADODB.Recordset
    3. Dim Parameter As String
    4. Set cnn = New ADODB.Connection 'Create Connection
    5. Set rst = New ADODB.Recordset 'Create Recordset
    6.  
    7. '~~> Use this line
    8. If cnn.State = adStateOpen Then cnn.Close
    9.  
    10. cnn.Open "Provider=SQLOLEDB; " .............."
    11.  
    12. Set rst = cnn.Execute("EXEC [dbo].[" + NameProcedure + "] " & Parameter)
    13.  
    14. If rst.EOF = True Then
    15. '
    16. '
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  8. #8

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    4

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    hey JJJCR_FOX and koolsid, thanx so much for replying and giving suggestions. The problem is resolved now. The problem was that the stored procedure had a sum of column query which was throwing error (coz the integer column had some null values). I was not able to catch the error in the beginning itself because the stored procedure as a whole was not throwing any error and executed properly. Since the query inside it was throwing an error, the front end Excel (macro) immediately caught that error and the record set object was closed.

  9. #9
    Addicted Member
    Join Date
    Jan 2009
    Posts
    233

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    good..that's why i ask you to check tables and fields..because just recently we have same problem..fields and data doesn't match...
    The taller the bamboo grows the lower it bends...

  10. #10
    New Member
    Join Date
    May 2011
    Posts
    1

    Re: VBA-Excel error 3704- operation is not allowed when the object is closed

    Hi, I was also having this issue - but connecting to SQL Server 2008 and using a stored procedure to return a subset of results. I found that if I set SET NOCOUNT ON within the stored procedure and I was not printing anything else to the Messages tab within SQL Server - this error no longer appeared.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width