1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run time error 3704 operation not allowed when object is closed

Discussion in 'VBA Macros' started by Satish.digit, Apr 1, 2014.

  1. Satish.digit

    Satish.digit Member

    Messages:
    44
    Hi,

    I am using below code to update record based on ID but i am getting Runtime error 3704 can any one help me on this.

    Private Sub LblAllot_Click()
    Dim counter As Integer, cn As New ADODB.Connection, ssql14 As String
    counter = 0
    DB_Open_Connection
    For i = 0 To lstClaimLineList.ListCount - 1
    If lstClaimLineList.Selected(i - counter) Then
    ssql14 = ("update Table1 set [Name] = 'agandhi' where ID = 2")
    cn.Execute ssql14
    End If
    Next i
    DB_Close_Connection
    End Sub
  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,385
    There are several objects/macros references in your sample that we have no insight to. Do you have a check to make sure that the query is returning at least 1 record? When you get the error message, what specific line is highlighted when you debug?
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Satish ,

    Correct me if my understanding is wrong , but DB_Open_Connection is a subroutine , which is opening the database connection ; since you are declaring :

    cn As New ADODB.Connection

    within the parent procedure , even if the connection is opened in the subroutine , cn will never get a value ; when you return to the parent procedure and execute the statement :

    cn.Execute ssql14

    cn is not an open object.

    You might have to rewrite your code either to make DB_Open_Connection a function so that you can have something like :

    cn = DB_Open_Connection()

    or pass cn as a parameter to the DB_Open_Connection procedure so that when you return to the parent procedure cn is available.

    Narayan
    Luke M likes this.

Share This Page