0

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()
Vivek
  • 5
  • 4
  • You should be able to combine [How can I get table names from an MS Access Database?](https://stackoverflow.com/questions/201282/how-can-i-get-table-names-from-an-ms-access-database) with `SELECT COUNT(*) FROM [tableName]` and add up the results. – Andrew Morton Jan 17 '22 at 11:12
  • Any idea how can I combine them. I mean the complete code with loop or something? – Vivek Jan 17 '22 at 12:12
  • I think using this link, I can get table names but how to store and loop them dynamically and use select count? https://stackoverflow.com/questions/37446649/how-to-retrieve-ms-access-tables-names-from-specific-database-using-sql-in-vb-ne – Vivek Jan 17 '22 at 12:37

1 Answers1

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
Mary
  • 14,926
  • 3
  • 18
  • 27
  • It shows an error Access address connection not a part of my settings – Vivek Jan 18 '22 at 15:57
  • Yes, that is my connection string for testing. Substitute your connection string. – Mary Jan 18 '22 at 19:39
  • bro can you please help me with this question also :- https://stackoverflow.com/questions/70768873/change-value-of-selected-records-from-ms-access-database-in-datagridview-in-vb-n – Vivek Jan 19 '22 at 10:23
  • Thanks for answering bro. I'm a noob in vb.net thanks for helping me. I marked this as answer. Was busy for some days so didn't see this – Vivek Jan 21 '22 at 10:12