customizing navigation pane using c#

Question

I made a tool to build databases and insert predefined queries into the database. I want to categorize the data and queries in the navigation pane of the database, as is possible using the MS Access UI.

Is there any way to customize the navigation pane in a database using c#?

Thank you.

Answer 1

There are hidden system tables named MSysNavPaneGroups, MSysNavPaneGroupCategpries, MSysNavePaneGroupToObject, and MSysNavePaneObjectIDs.  I suspect you could do what you want by manipulating these tables; however, I have not done any reading or investigation to find out exactly how Access uses these tables to control what is seen in the Nav Pane.  A quick web search does find this discussion, which may provide useful information.  I also find this one and this one on stackoverflow.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Thanks. Indeed, I found these tables in the database, but cannot seem to interact with them from c#. I tried using the DAO.Database object and via Access automation (application.DoCmd.RunSQL). With the first, I get the error that the table was not found, the second gives me "operation canceled".

Besides, interacting with these system tables, even if possible, might make my application vulnerable to changes in the core of Access. I would have preferred if there was an API for this.

Answer 3

I don't know why you wouldn't be able to read the tables from C#, though I haven't tried it.  It could be that their status as system tables, or the fact that they are hidden, requires some special handling, but I would be somewhat surprised if that were the case.  When you say you used application.DoCmd.RunSQL, what SQL were you trying to run?  The RunSQL method only works with action queries, so if you were trying to run a SELECT query to read one of the tables, I can see why it wouldn't work.

When I get a chance, I'll set up a C# project for testing this myself.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

I tried inserting a record. That should be possible according to:

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.runsql

If you could try it and get something working, that would be greatly appreciated!

Answer 5

What about simply running queries against the respective tables?

db.execute ...

---

Daniel Pineault, 2010-2018 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 6

I tried that indeed, with the result "table not found".

Answer 7

> might make my application vulnerable to changes in the core of Access.

Although that is technically true, in practice these tables don't change. Maybe features could be added, but the core is stable.

Can you start with a simpler test, such as reading from a normal table? My guess is this is a permissions issue.
Also try manually adding a record to the Group tables. That works just fine for me. Of course you have to respect data types and referential integrity.

---

-Tom. Microsoft Access MVP

Answer 8

The following code is an example to produce the "cannot find table" exception:

using Microsoft.Office.Interop.Access.Dao;

            DBEngine dBEngine = new DBEngine();
            Database database = dBEngine.CreateDatabase(@"e:\test.accdb", ";LANGID=0x0413;CP=1252;COUNTRY=0");
            database.CreateQueryDef("a", "select * from data");
            database.CreateQueryDef("b", "select * from data");
            database.Execute("insert into MSysNavPaneGroups (Flags, GroupCategoryID, Id, Name, 'Object Type Group', ObjectID, Position) values (4, 4, 11, 'test', -1, 0, 7)");
            database.Close();

The same happens with:

            database.Execute("select * from MSysNavPaneGroups");

Answer 9

Id is an AutoNumber. You cannot set its value. Omit it from the insert statement and see how you fare.

---

-Tom. Microsoft Access MVP

Answer 10

The select statement gave the same error.