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

Solution 1:

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