Delete query won't run!

[Guest]

I've created a delete query by converting an unmatched query. The Unmatched
query uses the table I want to delete from and a query. The Delete query
provides me the proper records when viewing the results but won't run giving
me the message "Could Not Delete From Specified Tables" and the "Help" points
me to Microsoft Jet Error 3086 which says I don't have proper rights to edit
the table.

I am the owner of the database and have checked in User and Group
Permissions. What am I doing wrong? Please help, thanks.

 

[Ken Snell [MVP]]

Either your query does not include the primary key of the table from which
you wish to delete the records, or it needs to be modified to use a
different query syntax.

Post the SQL of the delete query that you're trying to run. Tell us about
the primary keys for the different tables.

 

[John Spencer (MVP)]

I'm willing to bet that the query is trying to delete from both tables at once.
I would guess that the user left all the fields "visible" in the query instead
of unchecking all but one from the table that the user wishes to delete from.

I guess we will see if they post the sql.

 

[Guest]

Sorry, here is the sql

DELETE DISTINCTROW [Track FSMA Serial #s Used].*
FROM [qry Create New Track FSMA D] LEFT JOIN [Track FSMA Serial #s Used] ON
[qry Create New Track FSMA D].[Serial Number] = [Track FSMA Serial #s
Used].[Serial Number]
WITH OWNERACCESS OPTION;

 

[Guest]

Here is the sql. It is working now, but has duplicate rows.

 

[Ken Snell [MVP]]

You don't say for sure, but I'm guessing that you want to delete the records
in [qry Create New Track FSMA D] query? Normally, a delete query will be
used to delete records directly from a table, using a criterion expression
in some cases.

Additionally, by using a LEFT JOIN, I'm not sure that you're doing yourself
any favor, as all records in [qry Create New Track FSMA D] query will be
returned for the query to use.

Tell us more about exactly what you're trying to do here. What do you want
to delete? And what are the criteria that tell the query which records are
to be deleted? What are [qry Create New Track FSMA D] and [Track FSMA Serial
#s Used]? (I admit to not being familiar with the clause "WITH OWNERACCESS
OPTION", so I won't comment on it at this time.)
--

Ken Snell
<MS ACCESS MVP>

Sorry, here is the sql

DELETE DISTINCTROW [Track FSMA Serial #s Used].*
FROM [qry Create New Track FSMA D] LEFT JOIN [Track FSMA Serial #s Used]
ON
[qry Create New Track FSMA D].[Serial Number] = [Track FSMA Serial #s
Used].[Serial Number]
WITH OWNERACCESS OPTION;

I'm willing to bet that the query is trying to delete from both tables at
once.
I would guess that the user left all the fields "visible" in the query
instead
of unchecking all but one from the table that the user wishes to delete
from.

I guess we will see if they post the sql.

 

[Guest]

I'm running this delete query to delete rows from [Track FSMA Serial #s
Used]. It does not have a primary key becuase I just want all unmatched
instances of the field [Serial Number] deleted from it.

So, I made a unmatched query comparing [Track FSMA Serial #s Used] to
another query that has updated serial #s (has less serial #s in it). I want
to use this unmatched query for the criteria for a delete query. When I try
to convert the unmatched query to a delete query I have the initial problem
of "Could Not Delete From Specified Tables".

I then tried leaving the unmatched query as a Select query and creating a
Delete Query based on the unmatched query, for which I provided the sql for.
It gets the job done, but not without having to igore error messages.

I'm sorry, but still learning Action queries. Any help or links to where I
can learn would be great. Thanks.

Sorry, here is the sql

DELETE DISTINCTROW [Track FSMA Serial #s Used].*
FROM [qry Create New Track FSMA D] LEFT JOIN [Track FSMA Serial #s Used] ON
[qry Create New Track FSMA D].[Serial Number] = [Track FSMA Serial #s
Used].[Serial Number]
WITH OWNERACCESS OPTION;

I'm willing to bet that the query is trying to delete from both tables at once.
I would guess that the user left all the fields "visible" in the query instead
of unchecking all but one from the table that the user wishes to delete from.

I guess we will see if they post the sql.

 

[Ken Snell [MVP]]

OK - try this:

DELETE [Track FSMA Serial #s Used].*
FROM [Track FSMA Serial #s Used]
WHERE [Track FSMA Serial #s Used].[Serial Number]
NOT IN (SELECT [qry Create New Track FSMA D].[Serial Number]
FROM [qry Create New Track FSMA D]
GROUP BY [qry Create New Track FSMA D].[Serial Number]);


--

Ken Snell
<MS ACCESS MVP>

I'm running this delete query to delete rows from [Track FSMA Serial #s
Used]. It does not have a primary key becuase I just want all unmatched
instances of the field [Serial Number] deleted from it.

So, I made a unmatched query comparing [Track FSMA Serial #s Used] to
another query that has updated serial #s (has less serial #s in it). I
want
to use this unmatched query for the criteria for a delete query. When I
try
to convert the unmatched query to a delete query I have the initial
problem
of "Could Not Delete From Specified Tables".

I then tried leaving the unmatched query as a Select query and creating a
Delete Query based on the unmatched query, for which I provided the sql
for.
It gets the job done, but not without having to igore error messages.

I'm sorry, but still learning Action queries. Any help or links to where I
can learn would be great. Thanks.

Sorry, here is the sql

DELETE DISTINCTROW [Track FSMA Serial #s Used].*
FROM [qry Create New Track FSMA D] LEFT JOIN [Track FSMA Serial #s Used]
ON
[qry Create New Track FSMA D].[Serial Number] = [Track FSMA Serial #s
Used].[Serial Number]
WITH OWNERACCESS OPTION;

I'm willing to bet that the query is trying to delete from both tables
at once.
I would guess that the user left all the fields "visible" in the query
instead
of unchecking all but one from the table that the user wishes to delete
from.

I guess we will see if they post the sql.

:

Either your query does not include the primary key of the table from
which
you wish to delete the records, or it needs to be modified to use a
different query syntax.

Post the SQL of the delete query that you're trying to run. Tell us
about
the primary keys for the different tables.

--

Ken Snell
<MS ACCESS MVP>

I've created a delete query by converting an unmatched query. The
Unmatched
query uses the table I want to delete from and a query. The Delete
query
provides me the proper records when viewing the results but won't
run
giving
me the message "Could Not Delete From Specified Tables" and the
"Help"
points
me to Microsoft Jet Error 3086 which says I don't have proper
rights to
edit
the table.

I am the owner of the database and have checked in User and Group
Permissions. What am I doing wrong? Please help, thanks.