Self Joins in Access

[Darragh]

Hi all!

I'm having a bit of an issue making a self join in access (similar to
the example explained on Allen Browne's excellent site -
http://allenbrowne.com/ser-06.html).

I've made the self join in the query window, however when I've tried
to run the query i get the message "table t1 opened exclusively by
another user, or it is already open through the user interface and
cannot be manipulated programmatically"

Initially I thought it might be a corruption problem - but I mocked up
a similar self join in a brand new database, and still get the same
error.

I am using Access 2003.

Any help appreciated.

Regards
Darragh

 

[John W. Vinson]

Hi all!

I'm having a bit of an issue making a self join in access (similar to
the example explained on Allen Browne's excellent site -
http://allenbrowne.com/ser-06.html).

I've made the self join in the query window, however when I've tried
to run the query i get the message "table t1 opened exclusively by
another user, or it is already open through the user interface and
cannot be manipulated programmatically"

Just how are you trying to do the self join??? Just creating a Select Query
should not give this message, unless you're trying to change the *design* of
the table. What exact steps are you taking to produce this message?

 

[Darragh]

Hi John. Thanks for your reply.

These are the steps I've taken.

I'll call the table "data" for convenience (this isn't the exact make
up of my table, but just an example which demonstrates my scenario).
The data table has a series of id numbers that may have a parent id
from the same table - so its parent-child relationship. The table
looks like

ID, parent ID
1, null
2, 1
3, 1
4, 2
5, 2

and so on.

I've defined in relationships a join from data to a copy of data,
joining fields id and parent ID in a one to many relationship
(enforced referential integrity, and have cascaded updates on)

Then I've moved on to make a self join query similar to the one
described in the link I've previously posted - what I want is to have
a query which shows an id, then all the children of that particular ID
- a recursive sort of query.

In the select query, I've put in the table data, then a copy of the
table data, and aliased them child and parent appropriately. I've then
selected a variety of fields, changing them from select *, to specific
fields to see if the query would work - each time getting the error
""table t1 opened exclusively by
another user, or it is already open through the user interface and
cannot be manipulated programmatically". I've also changed the join
conditions from inner join, left join, outer join, to see if the query
would operate. Every instance I get the same error.

I am not changing the design of any table - simply selecting.

While I wait from any feedback from you, ill try this query in my copy
of Access 2007 and see if it could be the version of access I have
installed.

Thanks!

Darragh

 

[John W. Vinson]

In the select query, I've put in the table data, then a copy of the
table data, and aliased them child and parent appropriately. I've then
selected a variety of fields, changing them from select *, to specific
fields to see if the query would work - each time getting the error
""table t1 opened exclusively by
another user, or it is already open through the user interface and
cannot be manipulated programmatically".

That is TRULY bizarre. Does compacting the database help? Deleting the query
and recreating it from scratch? Could you post the SQL of the query?

 

[Allen Browne]

Darragh, I posted this reply in comp.databases.ms-access

Suggestions:

1. While Access is not running, look in the folder where you database
resides. You should see an MDB file. If you also see a very small LDB file
with the same name as the MDB, delete the LDB. It contains locking
information. Access normally deletes it when you exit your database, but it
may be left over after a crash.

2. Make sure you have Name AutoCorrect turned off.
Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact/repair the database:
Tools | Database Utilities | Compact/Repair

3. Under Tools | Options, make sure you have the locking strategy set to:
No Locks
If you use table-level locking, you could have problems trying to use
multiple instances.