When to open/close a connection?

[Denaes]

When should you open and close connections to a database?

 

Should you open a connection and keep it open until you close the program (on a program that is going to constantly keep accesing the database)?

 

on one form, I have a code to edit, delete, update and make a new record. Each procedure starts with opening the connection and then closes it after its over.

 

This also seems to cause some lag when click on the combobox to change tables.

 

Is it ok to keep a connection open?

 

This is the sort of thing I'm doing:

 

   Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
       Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & Application.StartupPath & "\Customers.mdb")
       MyConnection.Open()
       Dim MyCommand As New OleDbCommand("DELETE FROM " & strTable & " WHERE " & strItem & " = '" & lstSelect.GetItemText(lstSelect.SelectedItem) & "'", MyConnection)
       MsgBox("There was " & MyCommand.ExecuteNonQuery() & " records affected")
       MyConnection.Close()
       MyCommand.Dispose()
       itemChanged()
   End Sub

 

Obviously I'm taking a direct rout with this. Just feeding SQL directly through the connection.

 

The variables, in this case, are received from a combo box (the table to delete from) and the selected item of a list box (in this case, the one you want to delete).

 

As always, thank you for your input :)

[Robby]

In most cases I prefer to open late and close early.

In other words open only as needed.

[Denaes]

In most cases I prefer to open late and close early.

In other words open only as needed.

 

By open and close early you mean open it, get all the data like into a dataset, then close it, right?

 

You can't just open and close it early if you're constantly manipulating the database directly, you'd have to open it up every time you need to read/change/add to it