count all records in all tables in MS access database in vb.Net-CodePudding

I have a database in MS access and I have 4 tables in it. I want to count all the records of all the tables using vb.Net. Is there any oledbcommand command to count all the records in all the tables in the database?

    con1.Open()
    Dim Restrictions() As String = {Nothing, Nothing, "Table1", Nothing}
    Dim CollectionName As String = "Columns"
    Dim dt As DataTable = con1.GetSchema(CollectionName, Restrictions)
    For Each TableRow As DataRow In dt.Rows
        Console.WriteLine(TableRow.Item("COLUMN_NAME").ToString)
    Next
    con1.Close()

CodePudding user response：

I have created 2 functions. The first gets the table names by calling GetSchema on the connection. I excluded the system tables by checking if the name started with MS. I also excluded views by only adding TABLE_TYPE = TABLE.

The second function takes the list of table names and gets the count in each table. Note the brackets around table name in case the name contains a space.

Private cs As String = My.Settings.AccessAddressConnection

Private Function GetTableNames() As List(Of String)
    Dim TableNames As New List(Of String)
    Dim dt As New DataTable
    Using cn As New OleDbConnection(cs)
        cn.Open()
        dt = cn.GetSchema("Tables")
    End Using
    For Each row As DataRow In dt.Rows
        If Not row("TABLE_NAME").ToString.StartsWith("MS") AndAlso row("TABLE_TYPE").ToString = "TABLE" Then
            TableNames.Add(row("TABLE_NAME").ToString)
        End If
    Next
    Return TableNames
End Function

Private Function GetTotalRecords(lst As List(Of String)) As Integer
    Dim counts As Integer
    Using cn As New OleDbConnection(cs),
            cmd As New OleDbCommand()
        cmd.Connection = cn
        cn.Open()
        For Each TName In lst
            cmd.CommandText = $"Select Count(*) From [{TName}];"
            counts  = (CInt(cmd.ExecuteScalar))
        Next
    End Using
    Return counts
End Function

Private Sub Button1_Click() Handles Button1.Click
    Dim lst = GetTableNames()
    Dim Total = GetTotalRecords(lst)
    MessageBox.Show(Total.ToString)
End Sub