[RESOLVED] Proper way to open recordset/connection to the database

[Tomexx]

Hi,
Please help me with a proper way (better or more flexible) to open/close recordset and connection to the Access database.

My requirements:
I need to be able to replace the .mdb file any time without closing the program, so I'm opening and closing the connection each time I need to access the db (no opening in Form_Load() and closing in Form_Unload())

1. Open db
2. Read or Write data
3. Close db

Here's the way I do it now:
Should I split the OpenStatsDB() to OpenConnection() and OpenRecordset() to gain flexibility. Is that the proper way to do this?

Code in Module

Public con As New ADODB.Connection
Public rec As New ADODB.Recordset

Public Function OpenStatsDB(sEvent As String) As Boolean
  Call CloseStatsDB
  
  con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & STATS
  
  If sEvent = "" Then
    rec.Open "SELECT * FROM myTable", con, adOpenStatic, adLockOptimistic
  Else
    rec.Open "SELECT * FROM myTable WHERE Event = '" & sEvent & "'", con, adOpenStatic, adLockOptimistic
  End If

  If rec.BOF And rec.EOF Then
    OpenStatsDB = False
  Else
    OpenStatsDB = True
  End If

End Function

Public Sub CloseStatsDB()
  If rec.State = adStateOpen Then rec.Close
  If con.State = adStateOpen Then con.Close
End Sub

Code in form

Private Sub Command1_Click()
  On Error GoTo Leave
   
  If OpenStatsDB("") Then
    ' do my updates here
    text1.text = rec.fields[0].value
  End If
  Call CloseStatsDB
    
  Exit Sub
  
Leave:
  Call CloseStatsDB
End Sub