adodb.connection issues

[zakman]

Hello everyone,

Suppose that a program that will be accessing a database
quite often. What are the advantages/disadvantages of:

1. Opening the connection at the beginning of the program & closing it when you exit the program

as compared to

2. opening/closing the connection as needed?

--Kevin

 

[CassandraR]

Hi Kevin:

Per my comments in your other post on this subject:

It is better to open and to close the connection as needed. It provides more safety on the database, permits multiple access to the same database, reduces latency of changes made to the database and there are other reasons.

Just my ever-so-humble opinion.

Cassie

 

[thornmastr]

Kevin,

I strongly agree with Cassie.

I make it a bit easier by creating the string used for the connection in the first forms load event as such:

Private Sub Form_Load()

If constr & "" = "" Then
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\StudentTracking.mdb;Persist Security Info=False"
End If

End Sub

Constr is defined as a string in a module common to the application.

The following is a delete subroutine which shows how this works.

Private Sub DelButton_Click()
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sCMD As String
Dim sSQL As String

If MSHFlexGrid1.RowSel < 1 Then
Call MsgBox(&quot;You must pick a row to delete&quot;, vbInformation, App.Title)
Exit Sub
End If

Set con = New ADODB.Connection
con = constr
con.Open
MSHFlexGrid1.Col = 0
sCMD = &quot;delete * from tblstudents where pkstudent = &quot; & MSHFlexGrid1.Text
con.Execute sCMD, , adCmdText
sSQL = &quot;select PKStudent,LastName,FirstName,MI,DOB,Gender from tblstudents&quot;
Set rst = New ADODB.Recordset
rst.Open sSQL, con, adOpenStatic, adLockOptimistic
Set MSHFlexGrid1.Recordset = rst
rst.Close
con.Close
Set rst = Nothing
Set rst = Nothing
End Sub

Notice that both the new recordset and the new connection are both closed and set to nothing.


Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@earthlink.net

 

[zakman]

Thanks Cassie & Robert,

I have been considering establishing my connection at the very
beginning the application and closing it when finished with the
first task. Then re-opening it each time that I need to issue a
command/data. Finally destroying the object upon closure of the
application.

The reason I chose to open the connection & leave it open is that
I've always heard that the most time consuming task was to open
a database connection. Perhaps that has changed from RDO to ADO
and with newer operating systems, etc.

Open/Close the connection as needed makes sense as I do that all
the time in ASP. Hmmm...

The purpose for this thread was to see how other programmers
handle this situation.

--Kevin