Access Get All Tables

How can I get table names from an MS Access Database?

To build on Ilya's answer try the following query:

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6)))
order by MSysObjects.Name 

(this one works without modification with an MDB)

ACCDB users may need to do something like this

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6))
        AND ((MSysObjects.Flags)=0))
order by MSysObjects.Name 

As there is an extra table is included that appears to be a system table of some sort.

can we list all tables in msaccess database using sql?

Use MSysObjects

SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0

Query to return list of tables in Access

Figured it out on my own actually

SELECT Name
FROM MSysObjects
WHERE (Name NOT LIKE "MSys*") AND (Type=1 OR Type=6);

This one worked for me

Access get all tables

This will bring back all tables in the MS Access database (including linked tables)

SELECT MSysObjects.*, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1)) OR (((MSysObjects.Type)=6));

It also inclued Sys tables, so you might want to exclude tables starting with MSys

Have a look at

• Using MSysObjects
• SELECT "Table" AS [Table],
  MSysObjects.Name, MSysObjects.

Retrieve List of Tables in MS Access File

I just found the following solution from David Hayden

// Microsoft Access provider factory
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

DataTable userTables = null;
using (DbConnection connection = factory.CreateConnection()) {
  // c:\test\test.mdb
  connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test\\test.mdb";
  // We only want user tables, not system tables
  string[] restrictions = new string[4];
  restrictions[3] = "Table";

  connection.Open();

  // Get list of user tables
  userTables = connection.GetSchema("Tables", restrictions);
}

List<string> tableNames = new List<string>();
for (int i=0; i < userTables.Rows.Count; i++)
    tableNames.Add(userTables.Rows[i][2].ToString());

How to loop through all Tables in an MS Access DB

The Database Documenter wizard with these options should give you what you want with the least effort.

If that approach is not satisfactory, you can use custom VBA code to gather the information you want. You can retrieve the names of the tables in your database by looping through the DAO TableDefs collection.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
    ' ignore system and temporary tables
    If Not (tdf.name Like "MSys*" Or tdf.name Like "~*") Then
        Debug.Print tdf.name
    End If
Next
Set tdf = Nothing
Set db = Nothing

To get the field details you want, adapt Allen Browne's TableInfo() function ... substitute file write statements for the Debug.Print statements. Note that function uses 2 helper functions, GetDescrip and FieldTypeName, both of which are included in that linked page.

Here is an Immediate window output sample from TableInfo() for a table in my database --- I think it includes the field information you want.

TableInfo "foo"
FIELD NAME    FIELD TYPE    SIZE          DESCRIPTION
==========    ==========    ====          ===========
id            AutoNumber     4            
MyNumber      Long Integer   4            
MyText        Text           255          
bar           Long Integer   4            
==========    ==========    ====          ===========

After you've adapted the function, call it from the For Each tdf loop in the sample above and feed it each tdf.name:

TableInfo tdf.name

How can I get a list of tables in an Access (Jet) database?

Try the GetSchema()

    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\access.mdb";    

    connection.Open();

    DataTable userTables = connection.GetSchema("Tables");

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

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

---

Related Topics