ACL Table Already Open error message

acl table leggyOh, I hate the Table Already Open ACL error message with a passion.

And the Command Cancelled message (see the end of this post regarding that message).

Usually, it means I did something stupid, and I can figure out what, and fix it pretty fast.

Sometimes I have to scratch my head for quite some time before I figure it out.

I wrote a post in 2017 about Deleting ACL Table Covers A Multitude of Sins. This post is an expansion of that post, but mainly focuses on the “Table Already Open” error.

If you haven’t read that post lately (or ever), I’d read that one, preferably before continuing.

Reasons for the Error Message

1) The table is already open (really!).

1a) Usually this occurs because you opened the table, extracted data, and named the TO table the same as the original table. For example:

OPEN Table1
EXTRACT ALL TO Table1

Oops! Just rename the table in the last line to something else.

1b) Another issue is more rare: The table is open in another ACL project.

There’s a number of ways this can happen, but I won’t go into them in detail. I recommend not sharing tables between projects, but this can also happen when you COPY a table to another project and then forget to copy the associated .FIL also, and then ensure the copied table is connected to your .FIL in your new project–not the original project from which the table was copied.

2) A totally different situation exists, and the table isn’t open – ACL just can’t write to that table.

This happens when you change the table layout (add a field, change the length of the field, etc.) of a table (TableX) and then overwrite a table that already exists (TableY).

For example, let’s say you have a script containing lines 1 and 3 (see black lines below).  You run the script and create TableY just fine. But then you add line 2 and run the script again. That’s when you’ll get the Table Already Open error on TableY. Huh?

1 OPEN TableX
2 DEFINE FIELD c_Name COMPUTED SUBSTRING(Name, 1, 25)
3 SUMMARIZE ON ALL TO TableY.FIL OPEN PRESORT

So what is happening? You changed the layout of TableX and then tried to write the results to TableY, but the fields being written to Table Y don’t match the current table layout of TableY. So ACL stumbles and gives you an error that doesn’t quite match what’s really going on.

The solution is to delete TableY and run the script again. Presto!

So the solution is the same as the post I mentioned previously: delete the offending table.

Update: I just got the Command Cancelled message when doing a SUMMARIZE command for the same exact reason.  I had changed a field in the table that the SUMMARIZE command was going to write to. Deleting the table that the command was going to write to did the trick!

2 Comments

Filed under ACL, Audit, Data Analytics, How to...

2 responses to “ACL Table Already Open error message

  1. Pingback: Master List of ACL Articles and Tips | ITauditSecurity

  2. Great stuff! Thanks.

    Liked by 1 person

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.