Asking for code design advice: working with VB.Net and Ms-Access

Question

Perhaps naively, I created a class (AdminDatabase) to handle connection to different MS-Access database files (see code at bottom). The purpose of the class was to allow retrieval of data from a MS-Access database and to manipulate the data. This works well. I can feed an instance of the AdminDatabase class an SQL statement and fill a dataTable with the result (getDataTable method in the code section. Now I have added data sets to the project using Visual Studio's data designer.

Now I am a bit confused. The AdminDatabase class set's the connection string once the user has chosen the relevant data file. Since the new data sets also use the same setting, My.Settings.AdminConnectionString for the connection, which brings me to my questions:

1. After instantiating a AdminDatabase object, can I assume that the data sets I created using the data designer will connect to the MS-Access database file chosen by the user at run time?

2. Should I write methods in my connection class to access data in the data sets created with the data designer, or would accessing the data sets directly be ok? I guess I could just have a method somewhere to set the connection string setting in the project.

3. How else can I approach this?

Code

     public class AdminDatabase
       ' stores the connection string which is set in the New() method
       dim strAdminConnection as string

       public sub New()
       ...
       adminName = dlgopen.FileName
       conAdminDB = New OleDbConnection
       conAdminDB.ConnectionString = "Data Source='" + adminName + "';" + _
           "Provider=Microsoft.ACE.OLEDB.12.0"

       ' store the connection string in strAdminConnection
       strAdminConnection = conAdminDB.ConnectionString.ToString()
       My.Settings.SetUserOverride("AdminConnectionString", strAdminConnection)
       ...
       End Sub

       ' retrieves data from the database
       Public Function getDataTable(ByVal sqlStatement As String) As DataTable
            Dim ds As New DataSet
            Dim dt As New DataTable
            Dim da As New OleDbDataAdapter
            Dim localCon As New OleDbConnection


            localCon.ConnectionString = strAdminConnection

            Using localCon
                Dim command As OleDbCommand = localCon.CreateCommand()
                command.CommandText = sqlStatement
                localCon.Open()
                da.SelectCommand = command
                da.Fill(dt)
                getDataTable = dt
            End Using

        End Function
    End Class

Answer 1

If you are using strongly typed datasets for the returns rather than the generic DataTable, I would see those as being different methods, returning the proper dataset for the operation being completed.

I personally don't use DataSets much anymore, so I'm not 100% sure if there is a cleaner way of doing it...