Member Avatar for rony001
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

  • 1 Contributor
  • 0 Replies
  • 179 Views

Dani AI

Generated

For : yes — tables created at runtime can be queried from VB.NET, but you must build the SQL dynamically (Access does not accept a table name as a parameter). Two practical patterns work well depending on how many runtime tables you have and how you want the results returned.

One-shot UNION approach (good for a moderate number of tables). Enumerate the table names from the connection schema (or MSysObjects if you can read it), validate each name, and build a single SQL string that UNIONs a per-table aggregate. Benefits: one round‑trip to the database and a single result set. Downsides: very long SQL can hit Access limits and is harder to debug. Example outline (validate table names before concatenation):

' outline: get table list, build UNION ALL, run once
Dim schema As DataTable = cn.GetSchema("Tables")
Dim sb As New System.Text.StringBuilder()
For Each r As DataRow In schema.Rows
  Dim t As String = r("TABLE_NAME").ToString()
  If r("TABLE_TYPE").ToString().ToUpper() = "TABLE" AndAlso Not t.StartsWith("MSys") Then
    ' validate t (whitelist/regex) then append
    sb.Append("SELECT '" & t & "' AS Tbl, NZ(SUM([colA]),0)-NZ(SUM([colB]),0) AS Result FROM [" & t & "] UNION ALL ")
  End If
Next
If sb.Length > 0 Then
  sb.Length -= " UNION ALL ".Length
  cmd.CommandText = sb.ToString()
  Dim da As New OleDb.OleDbDataAdapter(cmd)
  Dim out As New DataTable()
  da.Fill(out)
End If

Iterative approach (better for many tables). Loop the validated table list, run a small aggregate query per table (ExecuteScalar or a tiny DataAdapter.Fill) and append each result to an in-memory DataTable. This avoids enormous SQL strings and is simpler to recover from individual table errors.

Practical tips: always validate table names (prevent injection), wrap names in square brackets, handle NULL sums (use NZ(...) in SQL or check DBNull in VB), dispose connections/commands with Using, and if running on 64‑bit use the ACE provider instead of the legacy Jet provider. Choose UNION for fewer tables and single-result convenience; choose iterative queries for large or unpredictable numbers of tables.

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.