Results 1 to 11 of 11

Thread: [RESOLVED] when to close ado connection

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Resolved [RESOLVED] when to close ado connection

    I have this select, insert, update and delete action queries.

    As usual, I open connection and recordset objects. Then, created the sql string to execute then I do
    cn.execute string_name, then immediately I close the recordset object and the connection object but after closing that I get error cannot execute the the connection is closed.

    cn.execute string_name
    if rs.state=adstateopen then
    rs.close
    set rs=nothing
    end if

    if cn.state=adstateopen then
    cn.close
    set cn=nothing
    end if

    So I guess that is because the closed the recordset and connection object.

    So when is the right time to close the recordset and connection object.

    I really need to close the recordset and connection object with this particular specific transaction.
    Last edited by codesearcher; May 26th, 2016 at 02:37 AM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: when to close ado connection

    The short answer is: when you're done with it.

    The longer answer is: It's been a long time since I've used RecordSets in VB6... but if memory serves, I don't recall explicitly closing the recordset. I would close the connection, but not the recordset. Additionally, I also remember setting the .ActiveConnection of the recordset to Nothing first and then closing the connection. This creates a disconnected recordset that you can then work with.

    That said - you code doesn't return a recordset, so there is nothing to close. Secondly, you shouldn't be having to check for the open state because the code should be small enough that you know what the state is. C) There are states other than Open and Close... and your code will fail to close if they are in one of those other states.

    Also in the future, it helps to highlight or note some how the line that it fails on. Is it failing in the Recordset.Close or in the connection.close... or some other line? beats me.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: when to close ado connection

    TG, thanks for replying.

    I will take note about the states.

    TG, basically, I follow this way of opening and closing connection, and like you said, close it when you're done with it.

    For example, I have sample structure code for adding record.
    Code:
    Dim CN as New ADODB.Connection
    Set CN = New ADODB.Connection
    CN.connectionstring=connection string here
    CN.open
    
    Dim RS as New ADODB.Recordset
    Set RS = New ADODB.Recordset
    
    Dim strSQL as string
    strSQL = "select * from tablename or queryname"
    RS.Open strSQL, CN, CursorType, LockType, Options
    
    With RS	
    	.Addnew
    	.fields("field1") = text1.text
    	.fields("field2") = text2.text
    	.fields("field3") = combo1.text
    	.Update
    End With	
    
    If (RS.State And adStateOpen) = adStateOpen Then	
    	RS.Close
    End If	
    Set rs = Nothing	
    
    If (cn.State And adStateOpen) = adStateOpen Then	
    	cn.Close
    End If	
    Set cn = Nothing
    That is just like that. But I got error cannot execute because the connection is closed.
    So I tried to remove the last part which is closing the connection object and just leaving the recordset open, and the problem was gone.

    But my connection object now here is still open. so when I click add button, though it will still work but shouldn't it works best to close it until i open it on later adding record transaction.

    .
    Last edited by codesearcher; May 26th, 2016 at 07:31 AM.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: when to close ado connection

    Code:
    Dim CN as New ADODB.Connection ' <-- don't do this... drop the New... it may be part of the problem. When you Dim as New ... EVERYtime you access the variable, even after setting it to nothing, VB checks to see if it exists, and if not, creates
    Set CN = New ADODB.Connection ' <-- since you NEw the connection object here (which is safer), you do not need it on the previous line
    CN.connectionstring=connection string here
    CN.open
    
    Dim RS as New ADODB.Recordset ' <-- same applies to this as to connection above
    Set RS = New ADODB.Recordset
    
    Dim strSQL as string
    strSQL = "select * from tablename or queryname"
    RS.Open strSQL, CN, CursorType, LockType, Options
    
    With RS	' <-- this is useless here too... With is simply a shortcut for developers to save on typing... here all it saves you is "RS"
    	RS.Addnew '<-- and then you don't even use it...
    	RS.fields("field1") = text1.text
    	RS.fields("field2") = text2.text
    	RS.fields("field3") = combo1.text
    	RS.Update
    End With	
    
    'If (RS.State And adStateOpen) = adStateOpen Then	
    '	RS.Close
    'End If	
    'Set rs = Nothing	
    '
    'If (cn.State And adStateOpen) = adStateOpen Then	
    '	cn.Close
    'End If	
    'Set cn = Nothing
    
    'Here's how I'd close it:
    Set RS.ActiveConnection = Nothing 'Disconnect it
    Set RS = Nothing 'Drop the recordset
    cn.Close 'Close the connection - I know I opened it, so I know it's in an open state
    Set cn = nothing 'Dispose
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    gibra
    Guest

    Re: when to close ado connection

    10 ADO best practices - TechRepublic
    http://www.techrepublic.com/blog/10-...est-practices/

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: when to close ado connection

    Most of those items I agree with ... I'm not sold on using recordsets to insert data either... unless it's done in a disconnected manner and then you use .BatchUpdate after re-connecting ... to do one-off inserts like the above... I would just use a SQL command, create a command object and execute it that way. I don't like the above method because it pulls down the whole table just to insert one record.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: when to close ado connection

    If not sure why you would add these conditional blocks given that you opened these just a few lines before you know they are open at this point unless and error occurred during open.
    Code:
    If (RS.State And adStateOpen) = adStateOpen Then	
    	RS.Close
    End If	
    Set rs = Nothing	
    
    If (cn.State And adStateOpen) = adStateOpen Then	
    	cn.Close
    End If	
    Set cn = Nothing
    So there is no reason for the conditional statements there.

    Personally in a project that will be doing db access to any extent I would generally make my connection object have a higher scope either from level or global.
    I would then create the connection object in my form load of my startup form.
    In each routine that needs to use the connection I would add a conditional block to check if the connection is open and open it if it is not.
    I would close and destroy the connection as part of my unload routine.

    I also commonly reused a recordset object so I do not destroy it each time I close it but rather when I am done with it.

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: when to close ado connection

    Quote Originally Posted by techgnome View Post
    I don't like the above method because it pulls down the whole table just to insert one record.
    -tg
    Yes, that is or can be a major issue. If one is to use a recordset to add a new record then the select query that is used to open that recordset should definitely use a where clause that limits the number of records to as few as possible. Usually 1 or 0 records would be desired by the open query, definitely not all records in a table

  9. #9
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: when to close ado connection

    I re-organized the code from post number 3 making a few changes
    Code:
    Option Explicit
    Dim CN As ADODB.connection
    Dim RS As ADODB.Recordset
    
    Private Sub Form_Load()
    Set CN = New ADODB.connection
    Set RS = New ADODB.Recordset
    End Sub
    
    Private Sub AddRecord()
    If Not (CN.State And adStateOpen) = adStateOpen Then
        CN.open connectionstring
    End If
    Dim strSQL As String
    strSQL = "select * from tablename or queryname"  'add a where false or other such clause to cause the query to record 0 records
    RS.open strSQL, CN, CursorType, LockType, Options
    
    With RS
        .Addnew
        .fields("field1") = text1.Text
        .fields("field2") = text2.Text
        .fields("field3") = combo1.Text
        .Update
    End With
    
    RS.Close
    End Sub
    Private Sub Form_Unload(Cancel As Integer)
    Set RS = Nothing
    If (CN.State And adStateOpen) = adStateOpen Then
        CN.Close
    End If
    Set CN = Nothing
    End Sub

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: when to close ado connection

    Thanks Datamiser for organizing better. With your code, rs and cn object as destroyed on form unload. May I just ask to how what is the difference if I placed that on the addrecord sub?

  11. #11
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: when to close ado connection

    The difference would be that you would need to re-create them every time in the sub and every other sub that used the previous methods. While in some cases this is fine it does not make a lot of sense if you are going to be needing that connection again very soon, same for the recordset. The difference in speed isn't much but every little thing makes a difference.

Posting Permissions

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



Click Here to Expand Forum to Full Width