One for Progaemmers and Gerdeners

As used for e.g. expert systems, LDAP directories etc. But Access is a relational database management system, using it to build a hierarchical database requires code, probably Visual Basic for Applications, as the standard Access mechanisms don't know about recursion.

I'd have thought that whoever wrote the hierarchical application would have included routines to dump and reload, possibly to some variant of XML. It's been a while since I touched Access, but as I recall, the F11 key should get you the sidebar listing queries, reports etc., and it's worth looking down here for anything that seems likely to do what you want. I'm assuming you've explored the user forms for anything that looks suitable.

The short answer is that since Access doesn't have a native tree structure, there are no generic solutions to your problem. Any solution will be specific to the database application you have.

I think the shadow of Not Invernted Here runs throughout all of MS products. Remember how they made it hard to use the internet when they believed they could crush it with MSN ? And then how they spiked the browser they forced onto Windows to break - sorry "improve" - websites with all sorts of unstandard and eventually unsupported cruft ?

I am writing the program in C#.

For flexibility I have 2 x Access tables, one is for categories which form the main nodes of the TreeView and the other has the actual data and a note of the Category record number. This means I can pull the categories to populate the original TreeView then when a node is expanded add the records where the Category record number equals the Tag of the TreeNode.

Bit of a brief summary as I'm sure most people aren't interested!

Just to add I shied away from serialising the TreeView because it is less flexible (and MSFT deprecates serialising data now it's found all the bugs in it!).

OK, I'd obviously assumed that you were using an application written elsewhere.

OK, I think it's brute force and ignorance, then. You will already have routines for walking the tree, I think you have to design a format for the dump, then write routines for conversion to and from your format. If you keep it all in your domain, you're not relying on Microsoft's bugs.

There may be some help on the Net for generic tree working, there are a number of articles about hierarchical databases in Access, but a quick look suggests they involve a fixed number of levels, and usually a table for each level. This approach is obviously a non-starter for arbitrary levels of nesting.

A quick note: are you using a .mdb file for storage? If so, get it all onto an SQL server as soon as possible. The single file format is quite fragile, especially over a network connection. My daughter spent a couple of years with a care agency, which had a reporting system based on Access. She seemed to spend a lot of her time fixing broken .mdb files and merging with backups etc.

It's accdb, just a later version but probably still vulnerable. I could use SQLite but the Access front end is quite useful.

I'd use Mysql for that, the tree structure is built using the relations in the database.

Access works with SQL servers. I have a couple of applications in Access which use data stored in mariadb, a fork of MySQL. I believe Access can even work with data in a spreadsheet, but it doesn't seem a good idea apart from interfacing.

Isn't Access the jet engine ?

ADO could work with several data sources. IIRC so could DAO it's predecessor.

You could certainly use SQL statements to query an access database back in 1998. It was when I started preferring SQL to coding database work.

Probably means the TreeView in Microsoft ActiveX Common Controls (MSCOMCTL.OCX), that would be used on a form in Access or anything else VB/VBA via user forms and add-ins.

Horrible.

Ditch all that stuff and do GUI interfaces in XAML ....

The query statements for Access work on SQL as well. I wrote a program some while ago that you chuck a handful of variables at and it writes a database schema from scratch (Access, SQL or XML), it also write all the C# files needed to access the DB and prototypes of the various procedures needed to read/write the data out to Windows forms, create, update and delete records etc.

I'm nor sure I could do it from scratch again but it takes all the grunge work out of any program that uses a DB.

Have you considered moving the data to a back-end like MySQL. PostgreSQL or SQL Server?

Sounds a lot like a graph database. Are you using Markov Chains?

No, it's just a TreeView of categories linked to a ListView of items, bit like Windows File Explorer.

Easy peasy for a relational database. A table of items A table of category links to items, so that item can be in more than one category A table of category links to categories, so that a category can be in more than one category A table of categories.

I ended up with a table of items and a table of categories, each item has a categoryID. I can build a TreeView of categories and add items to the nodes or just use a ListView as I did originally.

Graph databases are designed around that sort of data. You might find an off-the-shelf product that will just slurp all of your data. A purpose-built tool is likely to have much better performance.