Subroutine to connect to MS Access database

Question

I have this subroutine setup to connect to a MS Access database:

Public Sub MakeDBConnection(ByVal source As String)
    Try
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & source & ";")
        cn.Open()
    Catch e As Exception
        ReportError("CRITICAL", e.Message)
    End Try
End Sub

It is in a module, and any function that uses it in the module it works with, however, when I try and use it from Main.vb (my main form) it doesn't seem to do anything, as any tries with executing SQL queries come back with an error saying I must initialize the connection.

I have tried setting all variables it uses to Public, but it doesn't work. Maybe I need to return something? I don't know.

Any help is appreciated, thanks.

Answer 1

The problem is that the scope of variable cn is local to the Sub. You should change this to a Function and return an OleDbConnection object. So the code would look like this:

Public Function MakeDBConnection(ByVal source As String) As OleDbConnection
    Try
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & source & ";")
        cn.Open()

        Return cn

    Catch e As Exception
        ReportError("CRITICAL", e.Message)
    End Try
End Function

Calling code would look like this:

' For example:
myOleDbCommand.Connection = MakeDBConnection(source)

Also consider having the MakeDBConnection function read from the app.config file for the data source, if that fits your architecture.