Delete contents of one table from another

[Steve]

Hello, I have tables tblDeleted and tblBaseline. I want
all records in tblDeleted to be deleted from
tblBaseline. Four attributes comprise the composite key,
[Dept Nm], [Job Tle Cd], [Stn Cd2] and [Dept Nbr]. The
info comes from the data warehouse so I can't control the
spaces in the field names. Here is my SQL:
DELETE tblBaseline.*
FROM tblBaseline INNER JOIN tblDeleted ON (tblBaseline.
[Dept Nm] = tblDeleted.[Dept Nm]) AND (tblBaseline.[Job
Tle Cd] = tblDeleted.[Job Tle Cd]) AND (tblBaseline.[Stn
Cd2] = tblDeleted.[Stn Cd2]) AND (tblBaseline.[Dept Nbr]
= tblDeleted.[Dept Nbr]);

I get an error: Cound not delete from specified table.

Any Idea on what causes this and how to fix it?

Thanks

 

[John Vinson]

Hello, I have tables tblDeleted and tblBaseline. I want
all records in tblDeleted to be deleted from
tblBaseline. Four attributes comprise the composite key,
[Dept Nm], [Job Tle Cd], [Stn Cd2] and [Dept Nbr]. The
info comes from the data warehouse so I can't control the
spaces in the field names. Here is my SQL:
DELETE tblBaseline.*
FROM tblBaseline INNER JOIN tblDeleted ON (tblBaseline.
[Dept Nm] = tblDeleted.[Dept Nm]) AND (tblBaseline.[Job
Tle Cd] = tblDeleted.[Job Tle Cd]) AND (tblBaseline.[Stn
Cd2] = tblDeleted.[Stn Cd2]) AND (tblBaseline.[Dept Nbr]
= tblDeleted.[Dept Nbr]);

I get an error: Cound not delete from specified table.

Any Idea on what causes this and how to fix it?

Thanks

Do you in fact have a unique Index (such as a Primary Key) on the
combination of the four fields? And do you have delete permission on
the table? The SQL looks correct if so...

 

[Steve]

I actually will answer my own question for the first time!

I am using Access 2002 and it is different than versions
previous to 2000 in that the default setting for the
UniqueRecords property is "No". This must be changed
to "Yes" for the query to work. Previous Access versions
defaulted to "Yes"