Cannot delete record (1 Viewer)

Vassago

Former Staff Turned AWF Retiree
Local time
Today, 18:44
Joined
Dec 26, 2002
Messages
4,751
Dave R said:
I don't know if Vassago's problem was ever resolved but I have just come across the same thing and discovered it is to do with Access 2000 Jet engine 4. When you dsign a query in Access 2000 the "Unique Records" property defaults to "No". In the query design, go to View then Properties and set "Unique Records" to "Yes". The delete query should then work. For more info see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

While I could never find a solution to this problem, I did discover a work around. Unfortunately, this article doesn't apply for me, as I am using Access 97 and I had the properties set correctly. My solution was a lot like Len described above.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:44
Joined
Feb 19, 2002
Messages
42,976
I did some investigation and I think your problem might have been that the one-side table did NOT have a primary key. Delete queries CAN be based on a query with a join. This is a common way of restricting rows.

However,

The one-side table MUST have a primary key or the UniqueRecords property MUST be set to Yes.

When the one-side is a query, the UniqueRecords property must be set to Yes and the one-side query cannot contain any aggregate functions.
 

Vassago

Former Staff Turned AWF Retiree
Local time
Today, 18:44
Joined
Dec 26, 2002
Messages
4,751
Thanks Pat! It's likely the primary key that got me. I don't have the data anymore to test with, but I'll keep this information in mind for the next problem that arrives such as this.

Thanks again!
 

Len Boorman

Back in gainfull employme
Local time
Today, 22:44
Joined
Mar 23, 2000
Messages
1,930
Done some research via my course notes on this subject. The extract below relates to the SQL standard. As we know there are a number of "flavours" and each flavour implements SQL with some variations.

So for SQL Standard a view is classified as updateable providing that the view satisfies the following restrictions.
1) The SELECT clause can include only column names (There must be no value expressions) and cannot include DISTINCT

2) The FROM clause can include only one table (There must be no joins)

3) A WHERE clause cannot include a subquery that references itself.

4) There can be no GROUP BY clause and no HAVING clause

5) There can be no UNION.

These rules appliy to the SQL standard and so may not apply to the Access implementation of SQL in full. They do however offer some guidelines as to when the "must use updateable ....." message may be encountered and the probable source of the problem.

Len B
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:44
Joined
Feb 19, 2002
Messages
42,976
Except for #2 Jet complies. Although cartesian products (cross joins) are not updatable. A Jet extension is to allow "views" with joins to be updatable. In fact Jet goes so far as to make heterogenous joins updatable. Heterogeneous joins join tables from different databases such as Access to DB2 or SQL server to Oracle. These join are not efficient since the OCBC table needs to be pulled to the local PC to perform the join but they are a lifesaver if you need that functionality.
 

st3ve

Registered User.
Local time
Today, 22:44
Joined
Jan 22, 2002
Messages
75
This should be easy, surely

This should be easy, surely? I'm having the same problem as Vassago, namely trying to delete one record from table A that is also in table B.

I have tried the suggested solutions with no success, I either get 'recordset not updateable' or something along the lines of 'specify the table you want to delete from'!

Pat Hartman:
DELETE [zzSwitchboard Items].*
FROM [zzSwitchboard Items] INNER JOIN [Switchboard Items] ON ([zzSwitchboard Items].ItemNumber = [Switchboard Items].ItemNumber) AND ([zzSwitchboard Items].SwitchboardID = [Switchboard Items].SwitchboardID);

Try rebuilding it from scratch. Add the table you want to delete from, add the other table. Join them. Select the asterisk from the table you want to delete from. Change the query type to delete.


iancampbellian:
'forget about inner joins etc etc...you'll tie yourself in knots'. I agree ... in this instance.

st3ve:
My solution is to run an 'unmatched' query, putting the result in a new table. Then swap old and new table names via rename. NOT very elegant, but it gets the job done. I'm running A97 and A2k (though i haven't tried the solutions in 2k yet)

Thanks for the suggestions/confirmations that i'm not alone!
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:44
Joined
Feb 19, 2002
Messages
42,976
This turns out to have solved the problem:
I did some investigation and I think your problem might have been that the one-side table did NOT have a primary key. Delete queries CAN be based on a query with a join. This is a common way of restricting rows.

However,

The one-side table MUST have a primary key or the UniqueRecords property MUST be set to Yes.

When the one-side is a query, the UniqueRecords property must be set to Yes and the one-side query cannot contain any aggregate functions.
 

st3ve

Registered User.
Local time
Today, 22:44
Joined
Jan 22, 2002
Messages
75
Pat/Len:
I've just tried this

DELETE tableA.field1, *
FROM tableA
WHERE (((tableA.field1) In (Select field1 from [tableB])));

This works fine, and neither table has a pimary key or indexed field.

Thanks for your help. Now i can die happy... or at least have a stress-free week-end!
:D
 

Users who are viewing this thread

Top Bottom