Connecting to a MS Access database through a function

Question

This is my function:

Public Function DBConnection(ByVal path As String)
    ' This function makes the database connection and returns the object
    ' to reference it.
    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + ";")
    cn.Open()
    Return cn
End Function

As you can see, I want to initialize a database connection and return it so I can use it in my forms. This function is in a module and my variables are as follows:

Public cn As OleDbConnection
Public cmd As OleDbCommand
Public dr As OleDbDataReader

But I'm not sure how I can use this in my forms, do I just call the function DBConnection and then proceed with my SQL statements? Or do I have to do something else? Help would be very much appreciated, cheers.

Also, I need some opinions. My application relies on a MS Access database. Is it better to initialize the connection on Form_Load and then close the connection when the user closes the program, or open and close the connections as the queries are run? I'm planning to use some database queries on multiple forms hence the reason I was putting it into a module, but I'm not 100% on how I should proceed with this.

Thanks.

Answer 1

A couple things. That function will open a connection every time it's called. You better make sure you are closing the database as well, or that will start eating up memory.

You might look at some other options (such as using NHibernate or another ORM.)

However, if you stay with this model, you could then use the cn that is returned to access your database.

Answer 2

From: How to bind Microsoft Access forms to ADO recordsets

Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset

   'Use the ADO connection that Access uses
   Set cn = CurrentProject.AccessConnection

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM Customers"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open 
   End With

   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs

   Set rs = Nothing
   Set cn = Nothing
End Sub