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