-
May 26th, 2016, 02:18 AM
#1
Thread Starter
Frenzied Member
[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.
-
May 26th, 2016, 06:41 AM
#2
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
-
May 26th, 2016, 07:13 AM
#3
Thread Starter
Frenzied Member
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.
-
May 26th, 2016, 07:39 AM
#4
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
-
May 26th, 2016, 08:20 AM
#5
Re: when to close ado connection
-
May 26th, 2016, 08:24 AM
#6
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
-
May 26th, 2016, 09:12 AM
#7
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.
-
May 26th, 2016, 09:19 AM
#8
Re: when to close ado connection
Originally Posted by techgnome
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
-
May 26th, 2016, 09:30 AM
#9
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
-
May 26th, 2016, 12:30 PM
#10
Thread Starter
Frenzied Member
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?
-
May 26th, 2016, 01:12 PM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|