Creating an object oriented model in VBA using COM and ADODB from 2 depended SQL tables

The story...

A bit of background info and how is the database designed...

Please notice you don't really have to rebuild the tables in SQL but I shared an SQL Fiddle just in case and screenshots1 of what the database looks like. I thought it was going to be easier to explain the story of what I am doing + you can always quickly build your own if you wanted to.

So the tables look like:

The PART table basically stores all Parts. The PARTARC is a table that stores relationships.

In this scenario a more logical explanation of what PARTARC actually represents would be:

• PART1 is a complete KIT and includes:

• PART2 (a LEFT-HAND model)

• PART5 (a RIGHT-HAND model)

• PART3 (a LABEL/STICKER)

• PART2 is a left-hand model made up of 2 components

• PART4 (a B (Buy) type component)

• PART6 (a B (Buy) type component)

• PART3 is just a sticker/label. The M type means it's made at the factory.

• PART4 is a low-level component of B type.

• PART5 is what PART2 really is but the RIGHT-HAND model, made up of

• PART4 (a B (Buy) type component)

• PART6 (a B (Buy) type component)

• PART6 is a low-level component of B type.

The point here is that PART1 is the top-level assembly part and it's made up of other components like for example PART2 or PART5 which are of type M which means they can also be made and sold separately as top-level assemblies. The B means that the part is not sold separately and can't be a top level assembly - this is why you shouldn't (will not) find the B type parts in column A on spreadsheet.

Hope this is now all clear.

The goal...

To build an object oriented data structure off of the tables and populate the spreadsheet in a very specific way.

The goal is to print out all Parent parts followed by their Children relationship to spreadsheet in a very specific format shown below. (click the image for full resolution):

Note: the prices may seem illogical as PART1 is made up of other

Anytime you run SQL queries in a loop, where the number of queries scales according to the amount of data you have, performance is likely to be poor. Ideally, you should be able to fetch all the data you need using a fixed number of queries.

Essentially, what you are trying to do is a depth-first tree traversal, where the tree is represented by an adjacency list. There is an MSDN article on that topic, with a similar example.

A query to fetch the tree, adapted to your problem, could look like this:

WITH Parts (Path, ParentName, PartId, Type, Name, Price) AS (
SELECT FORMAT(PartId, 'X8'), CAST(NULL AS VARCHAR), PartId, Type, Name, Price
FROM PART
WHERE Type = 'M'
UNION ALL
SELECT CONCAT(Parent.Path, '/', FORMAT(Child.PartId, 'X8')), Parent.Name, Child.PartId, Child.Type, Child.Name, Child.Price
FROM
Parts AS Parent
INNER JOIN PARTARC
ON Parent.PartId = PARTARC.Part
INNER JOIN PART AS Child
ON PARTARC.Son = Child.PartId
)
SELECT Name, Type, Price, ParentName
FROM Parts
ORDER BY Path;


The results would look like:

|  Name | Type | Price | ParentName |
|-------|------|-------|------------|
| PART1 |    M |   4.5 |     (null) |
| PART2 |    M | 12.78 |      PART1 |
| PART4 |    B |  7.86 |      PART2 |
| PART6 |    B |  7.55 |      PART2 |
| PART3 |    M |  2.45 |      PART1 |
| PART5 |    M |  17.9 |      PART1 |
| PART4 |    B |  7.86 |      PART5 |
| PART6 |    B |  7.55 |      PART5 |
| PART2 |    M | 12.78 |     (null) |
| PART4 |    B |  7.86 |      PART2 |
| PART6 |    B |  7.55 |      PART2 |
| PART3 |    M |  2.45 |     (null) |
| PART5 |    M |  17.9 |     (null) |
| PART4 |    B |  7.86 |      PART5 |
| PART6 |    B |  7.55 |      PART5 |

It should be easy to convert that table into the desired layout with a little bit of VB. Proceeding tuple by tuple, anytime you encounter a NULL for the ParentName, start a new row in the spreadsheet; otherwise, append four columns to the current row. Of course, you can populate the in-memory data structure with that information as you go.

StackExchange Code Review Q#69734, answer score: 9