Why does 'MSysNavPaneGroupCategories' show up in a .NET OleDbProvider initiated query to an MS Access 2K database when the criteria specifies "WHERE MSysObjects.Name NOT LIKE 'MSys*'"?

Question

I want to list all tables and their row count, in an MS Access database, in a grid view. I am using a query as follows:

SELECT MSysObjects.Name, CLng(DCount('*',[name])) AS RecordCount FROM MSysObjects WHERE (((MSysObjects.Type)=1) AND (MSysObjects.Name NOT LIKE 'MSys*')) ORDER BY MSysObjects.Name;

In MS Access Query pane this works just fine. But when I run the query via an OleDbCommand object in .NET, although the query produces a resultset I get a row of data with MSysNavPaneGroupCategories in it. This row always throws an error when i try to :

 DataRow row = null;
 do
 {
    row = dt.NewRow();
    row["TableName"] = (string)dr["Name"];
    row["RecordCount"] = (int)dr["RecordCount"]; // Fails here when dr["Name"]==MSysNavPaneGroupCategories
    dt.Rows.Add(row);
 } while (dr.Read());

The error message is:

System.InvalidOperationException was unhandled.

The provider could not determine the Int32 value. For example, the row was just created, the default for the Int32 column was not available, and the consumer had not yet set a new Int32 value. Source="System.Data"

So my workaround is to MAKE a TEMP table and read from that instead (or set a default value for the column... which overcomes the error but still includes the rogue table in the result set).

What's going on here? MSysNavPaneGroupCategories shouldn't even have made it into the result set.

Theres not much info on the MSysNavPaneGroupCategories system table.

This url says MSysNavPaneGroupCategories is one of three system tables that

define all the content within the Navigation Pane.

.. in Access 2007.

This Microsoft url says

The Navigation Pane, new in Microsoft Office Access 2007, is a central location from which you can easily view and access all your database objects (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.), run reports, or enter data directly in tables.

... in Access 2007.

Why would this table be showing up in an Access 2K database table listing when its an Access 2007 feature, and why does it show up at all in a query for which it doesn't match the criteria?

Answer 1

Jet's native data access library, DAO, provides a TableDefs collection that should make it easy to get the recordcounts and poke the data into your grid. Something like this would do the job in VBA (which could get your started -- it's air code):

  Dim db As DAO.Database
  Dim tdf As DAO.TableDef
  Dim strTableName As String
  Dim strConnect As String
  Dim strType As String 
  Dim strDatabase As String
  Dim strConnectType As String

  Set db = DBEngine.OpenDatabase("[path/filename of MDB/ACCDB file]")
  For Each tdf In db.TableDefs
    If (tdf.Attributes And dbSystemObject) = 0 Then ' examine only non-system tables
       strTableName = tdf.Name
       If Len(tdf.Connect) > 0 Then
          strConnect = tdf.Connect
          strDatabase = Split(strConnect, "=")(1)
       End If
       If (tdf.Attributes And dbAttachedTable) Then ' linked Jet/ACE table
          Debug.Print strTableName & ": " & DBEngine.OpenDatabase(strDatabase).TableDefs(strTableName).RecordCount
       ElseIf (tdf.Attributes And dbAttachedODBC) Then ' ODBC table
          Debug.Print strTableName & ": " & DBEngine(0)(0).OpenRecordset("SELECT COUNT(*) FROM " & strTableName)(0)
       Else ' local table
          Debug.Print strTableName & ": " & tdf.RecordCount
       End If
    End If
  Next tdf
  Set tdf = Nothing
  db.Close
  Set db = Nothing

The code above handles only Jet/ACE and ODBC data sources, not Excel spreadsheets, text/CSV or DBF files, but if you needed those you could make the ODBC case into the CASE ELSE, and handle them all with a SELECT COUNT(*).

The above could also clearly be optimized by looping through all the non-system TableDefs first and getting a list of all the unique back ends so you'd have to do the OpenDatabase only once for each Jet/ACE back end. You could also then use a single ODBC connection to your ODBC data source, or convert the ODBC connect string into ADO and get more information if you needed.

Now, whether or not this is a good idea or not depends on the particular situation. All TableDefs have a RecordCount property that is maintained as part of the table's metadata by Jet/ACE. But it's only immediately accessible on local tables -- that is, it doesn't work on linked tables. That's why the attached Jet/ACE tables are checked based on opening the back-end database.

But, again, I'm necessarily saying this is the best way, or even a good way. I'm just throwing it out there as food for thought, mostly because I like taking advantage of the RecordCount property (it is, I believe, what is returned by SELECT COUNT(*) FROM table, but is faster because you don't have the overhead of opening the recordset), where it is available to you.