Retrieving rows from a MS Access Database View using Vb.Net

Question

I've managed to get the following code...

            con.ConnectionString = My.Settings.dbConnection
        Dim sqlCmd As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand()
        con.Open()
        sqlCmd.Connection = con

        Dim schemaTable As DataTable
        schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Views, Nothing)

To retrieve a list of Views in my Access database, but now I want to retrieve the results based on a selected View.

Is there a correct method in doing this, or do I take the SQL Statement from the DataTable returned for each row?

Answer 1

Suppose you have Query1 (View) in your Access database (Database1.accdb file). The following code will output each row of the query to the console (for demo purposes):

    Dim con As OleDbConnection = New OleDbConnection()

    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database1.accdb;Persist Security Info=False;"
    Dim sqlCmd As OleDbCommand = New System.Data.OleDb.OleDbCommand()

    sqlCmd.CommandType = CommandType.StoredProcedure
    sqlCmd.CommandText = "Query1"
    sqlCmd.Connection = con

    con.Open()

    Dim reader As OleDbDataReader

    reader = sqlCmd.ExecuteReader()

    If reader.HasRows Then
        While reader.Read()
            Console.WriteLine(reader("Column1")) 'output specific column
        End While
    End If

    Console.ReadLine()

Hope this helps