rony001 0 Light Poster

I have an Access Database on which I have tables with similar structure and columns name columnA and columnB.I can use the below query on tables created design time to get this output.

Table | result
-------|--------

SELECT "Table1" AS Table, SUM(a) - SUM(b) AS Result FROM table1 UNION SELECT "Table2" AS Table, SUM(a) - SUM(b) AS Result FROM table2 UNION SELECT "Table3" AS Table, SUM(a) - SUM(b) AS Result FROM table3 

I would like to know is there any way to write a query for table created on run time in ms access from vb.net?

Imports System.Data.OleDb
 Public Class Form1


    Dim Cmd As New OleDbCommand
    Dim Reader As OleDbDataReader
    Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
                                             "Source=|DataDirectory|\tv.mdb; Jet OLEDB:Database Password=***")

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Cmd.Connection = Cn
        Cmd.CommandText = "SELECT col1 AS column1, Col2 AS Column2 FROM Table1"
        Cn.Open()
        Reader = Cmd.ExecuteReader
        lv_Trans.Clear()

        For i As Integer = 0 To Reader.FieldCount - 1
            lv_Trans.Columns.Add(Reader.GetName(i), 130, HorizontalAlignment.Center)
        Next
        While Reader.Read
            Dim LI As New ListViewItem
            LI.Text = Convert.ToString(Reader.Item("column1"))
            LI.SubItems.Add(Convert.ToString(Reader.Item("Column2")))
            lv_Trans.Items.Add(LI)
        End While
        Reader.Close()
        Cn.Close()

        Cmd.CommandText = "SELECT SUM(col1), SUM(col2) FROM Table1"
        Cn.Open()
        Reader = Cmd.ExecuteReader
        While Reader.Read
            lbl_col1.Text = Reader.GetDouble(0)
            lbl_col2.Text = Reader.GetDouble(1)
        End While
        Reader.Close()
        Cn.Close()
    End Sub
End Class

Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.