1

I'm trying to update a closure table using data macros, which requires running the following code whenever a new record is inserted into the main table:

INSERT INTO tblClosure (parent_id, child_id, depth)
SELECT p.parent_id, c.child_id, p.depth+c.depth+1
FROM tblClosure AS p, tblClosure AS c
WHERE p.child_id=$PARENT_ID and c.parent_id=$CHILD_ID;

As you can see, it would be possible to perform this operation using a nested loop:

Iterate through tblClosure
For each record p with child_id = $PARENT_ID {
    Iterate through tblClosure
    For each record c with parent_id = $CHILD_ID {
        Insert (p.parent_id, c.child_id, p.depth + c.depth + 1) into tblClosure
    }
}

Both the main table (tblNodes) and the closure table (tblClosure) are stored in the backend database, so it seems a data macro should be able to do what I want.

Data macros also seem to have functions Create Record (in...) and For Each Record (in...). But I can't get that second one to fire at all.

This is my code (I have defined temp_node and temp_parent as parameters):

enter image description here

luk2302
  • 55,258
  • 23
  • 97
  • 137
Mark Butler
  • 895
  • 1
  • 5
  • 18
  • Are you trying to update the same table when a new record is inserted? – Krish Jun 29 '15 at 11:53
  • No, the new records are inserted into tblNodes. It's tblClosure that I need to iterate through and add nodes into. Both tables are however in the same (back-end) database. – Mark Butler Jun 29 '15 at 12:00
  • please provide table definitions of both tables, relationship between them and the required/desired action/output. Your code doesn't help to understand what you are trying to do. – Krish Jun 29 '15 at 12:16
  • It's a hierarchical structure. tblNodes contains a list of 'nodes', each with a unique node_id and a parent_id (which is a foreign key from the table to itself). Some nodes have NULL parent_id (they are the first in a chain). A node might have many children. – Mark Butler Jun 29 '15 at 12:21
  • tblClosure records all possible paths from one node to another. It has closure_id (irrelevant autonumber field), parent_id, child_id and depth. For example, it has a line (1, 1, 0) saying node 1 is a parent of node 1 at depth 0. (1, 5, 8) would say that node 1 is a parent of node 5 at depth 8. – Mark Butler Jun 29 '15 at 12:23
  • The code is based on this article here: http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html However, MS Access does not support triggers so I am having to make do with data macros. – Mark Butler Jun 29 '15 at 12:24
  • what version of access are you using? access 2010 >= has triggers/data macros – Krish Jun 29 '15 at 12:34
  • 2013. I am using data macros but they are not doing what I need. See the screenshot from my post for what I have tried. However, no events inside the "for each record in" seem to work. I'm wondering if it's some limitation to do with the scope of these functions for iterating through other tables. – Mark Butler Jun 29 '15 at 12:38

1 Answers1

1

The Create Record macro command will not work when nested within a For Each Record loop. See here (https://social.msdn.microsoft.com/Forums/office/en-US/f00a88f7-8cdc-48ec-93f9-ca3d698aa6f3/access-2010-datamacro-exception-quotcreaterecord-cannot-be-used-inside-of-a?forum=accessdev) for why.

Instead, I recommend creating a query in your Access database from the values portion of the insert statement that you provided:

SELECT p.parent_id, c.child_id, p.depth+c.depth+1
FROM tblClosure AS p, tblClosure AS c
WHERE p.child_id=$PARENT_ID and c.parent_id=$CHILD_ID;

Next, create a union query of the tblClosure table and the query that you just created. This will show the two together, as if they were in one table. From this union query, you can perform other actions on it as if it were a table in your Access database.

possum
  • 27
  • 5