same table twice in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to combine a table twice (or more) in the same query without
getting this error message:

The table is already opened exclusively by another user, or it is
already open through the user interface and cannot be manipulated
programmaticaly
 
You should not get the exclusive lock message, unless you have deliberately
moved away from optimistic locking.

In query design view, it is very easy to add the table to the query a second
time. Access will alias it with a "_1" suffix. You can use the Properties
box to change the table alias if you wish.

That's assuming that you want to join different columns from the same table.
It would also be possible to create a UNION query if you wanted to combine 2
columns into one.
 
Select * from TableName
Union All
Select * from TableName

Is this what you looking for?

Thanks,
Saran.
 
Thanks Allen,

well do get that message, so I must have moved away from optimistic locking.
But not deliberately, I can asure you. So how can I get to optimistic locking?

Optimo
 
Thanks Saran,
no, that is not what I'm looking for.
More like

SELECT Table1![field1], Table2![field2], Table3![field3] FROM Table1 INNER
JOIN Table2 ON Table1.FKey1 = Table2.PKey INNER JOIN Table2 AS Table3 ON
Table1.FKey2 = Table3.PKey

And here I get the Error
 
Depends in what situation you get the message.

First place to check is:
Tools | Options | Advanced | Default Record Locking
Optimistic is "No Locks".

In a query or form, the Record Locks property is on the Data tab of the
Properties box when looking at the properties of the form or query.

If you used OpenRecordset() in code, the 4th argument determines the
locking.

Or perhaps you did open the table in design view.
 
Back
Top