[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

[Hack]

You should always have a separate sub to open/close the connection.

A recordset object should be declared up front, and then used, and closed on the fly, as needed.

[Tomexx]

Thanks for replaying Hack,
Could you show a little sample?

[Hack]

A sample of what?

[Tomexx]

The right way declaring, openning, pulling data, closing recordset/connections. Something that would be easy to reuse and flexible (used with different tables, being able to update different fields etc)

[Hack]

Something like this for the connection.

Option Explicit
Public ADOCn As ADODB.Connection
Public ConnString As String
Public adoRS As ADODB.Recordset
 
Public Sub ConnectDB(strStatus As String)
If strStatus = "Open" Then
   ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\myAccessDB.mdb;" & _
        "Persist Security Info=False"
 
   Set ADOCn = New ADODB.Connection
   ADOCn.ConnectionString = ConnString
   ADOCn.Open ConnString
Else 'assume close connection
   ADOCn.Close
   Set ADOCN = Nothing
End If
End Sub
 
Private Sub Form_Load()
ConnectDB "Open"
End Sub
 
'somewhere else where you want to close your connection
ConnectDB "Close"

Then, when you want to run your queries, just do something like

Dim sSQL As String
Set adoRS = New ADODB.Recordset
 
sSQL = "SELECT blah blah blah FROM table "
adoRS.Open sSQL, ADOCn
'do something with the resulting recordset
adoRS.Close
Set adoRS = Nothing
'repeat as necessary throughout you your program for SELECTS
'for action queries like updates do
Dim sSQL As String
sSQL = "UPDATE tablename SET field = 'Blah'
ADOCn.Execute sSQL

Is this what you mean?

[Tomexx]

Ok, thanks,

Where would I use "action query"?
I see that you use that directly with the connection object as opposed to the recordset.

[randem]

With access you need to be careful of processing the way you are...

[Tomexx]

randem,
what do you mean?

[GaryMazzone]

Action queries are Insert, Delete and Updates. When you return data from the database you use recordsets

[randem]

Opening an closing an Access database repeatedly like you do you will need to constantly repair and compact it to avoid corruption depending on how frequently you are doing such a process. Access is not build for repeated processes such as that in a short period of time.