Run Time error 3086 - Can't delete a table using query

B

BlueWolvering

Hello,
I am trying to delete entries out of a table (call it Error Log). The
criteria for deleting entries is complicated so bear with me.

I am comparing two tables (Input and Storage) and seeing the discrpancies
with them. Right now, I generate error messages that get logged into the
Error Log for things that aren't really errors, like the input and the
storage being identical. So I want to delete any entries in the error log
where the quad of FCN, FCP, FCS, and VIN match with input and storage.

SO I have a select query storing all the matched of FCN, FCP, FCS, VIN (call
that foursome the QUAD) between Input and Storage. So all QUADS where input
and storage match are stored in a query, call MATCHUPS.

Now, I compare the ERROR LOG to MATCHUPS for QUAD matches. I want to write
a delete query to delete any entries in ERROR LOG where QUAD matches and
entry in MATCHUPS.

Here is a translation so that you can read the code I am going to attach. I
didn't use real field names above both for clarity and brevity.

Input = ex_FuelCardInput
Storage = t_FuelCardInventory
Error Log = t_FuelCardErrors
MATCHUPS = q_FCErrors_Identical_reload
FCN = FuelCardNo (or FCN, or Fuel Card Number, but something like that)
FCP = FuelCardProvider (etc etc as above)
FCS = FuelCardSource (etc etc as above)
VIN = VIN

Here is the SQL for MATCHUPS:
SELECT t_FuelCardInventory.FuelCardNo, t_FuelCardInventory.FuelCardSource,
t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM ex_FuelCardInput INNER JOIN t_FuelCardInventory ON
(ex_FuelCardInput.[Fuel Card Number] = t_FuelCardInventory.FuelCardNo) AND
(ex_FuelCardInput.[Fuel Card Provider] =
t_FuelCardInventory.FuelCardProvider) AND (ex_FuelCardInput.[Fuel Card
Source] = t_FuelCardInventory.FuelCardSource) AND (ex_FuelCardInput.VIN =
t_FuelCardInventory.VIN);

Here is the SQL for the Delete Query:
DELETE t_FuelCardErrors.*
FROM t_FuelCardErrors INNER JOIN q_FCErrors_Identical_reload ON
(t_FuelCardErrors.FCNo = q_FCErrors_Identical_reload.FuelCardNo) AND
(t_FuelCardErrors.FCProvider = q_FCErrors_Identical_reload.FuelCardProvider)
AND (t_FuelCardErrors.FCSource = q_FCErrors_Identical_reload.FuelCardSource)
AND (t_FuelCardErrors.VIN = q_FCErrors_Identical_reload.VIN);

Here is the error message from the VBA call:
<<<DoCmd.OpenQuery "q_FCErrors_Delete_Reloads>>>
Run-time error '3086':
Could not delete from specified tables.

Here is what I have tried:
There is a primary key defined in Error Log. The key is all five fields
(QUAD + ErrorDescription)
There are no key violations, no duplicates of QUAD + ErrorDesc.

I can't figure out how to check read only, but I am the creator, editor,
writer and basically god of this Access file. I have not intentionally
specified read only on anything.

I am using Microsoft Access 2003.

Thanks.
 
J

John Spencer

First thing to try is adding DISTINCTRow to the query

DELETE DISTINCTROW t_FuelCardErrors.*
FROM t_FuelCardErrors INNER JOIN q_FCErrors_Identical_reload ON
(t_FuelCardErrors.FCNo = q_FCErrors_Identical_reload.FuelCardNo) AND
(t_FuelCardErrors.FCProvider =
q_FCErrors_Identical_reload.FuelCardProvider)
AND (t_FuelCardErrors.FCSource =
q_FCErrors_Identical_reload.FuelCardSource)
AND (t_FuelCardErrors.VIN = q_FCErrors_Identical_reload.VIN);

Next try this query

DELETE
FROM t_FuelCardErrors
WHERE FCNo & FCProvider & FcSource & VIN IN
(SELECT FuelCardNo & FuelCardProvider & FuelCardSource & VIN
FROM q_FCErrors_Identical_reload )



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello,
I am trying to delete entries out of a table (call it Error Log). The
criteria for deleting entries is complicated so bear with me.

I am comparing two tables (Input and Storage) and seeing the discrpancies
with them. Right now, I generate error messages that get logged into the
Error Log for things that aren't really errors, like the input and the
storage being identical. So I want to delete any entries in the error log
where the quad of FCN, FCP, FCS, and VIN match with input and storage.

SO I have a select query storing all the matched of FCN, FCP, FCS, VIN (call
that foursome the QUAD) between Input and Storage. So all QUADS where input
and storage match are stored in a query, call MATCHUPS.

Now, I compare the ERROR LOG to MATCHUPS for QUAD matches. I want to write
a delete query to delete any entries in ERROR LOG where QUAD matches and
entry in MATCHUPS.

Here is a translation so that you can read the code I am going to attach. I
didn't use real field names above both for clarity and brevity.

Input = ex_FuelCardInput
Storage = t_FuelCardInventory
Error Log = t_FuelCardErrors
MATCHUPS = q_FCErrors_Identical_reload
FCN = FuelCardNo (or FCN, or Fuel Card Number, but something like that)
FCP = FuelCardProvider (etc etc as above)
FCS = FuelCardSource (etc etc as above)
VIN = VIN

Here is the SQL for MATCHUPS:
SELECT t_FuelCardInventory.FuelCardNo, t_FuelCardInventory.FuelCardSource,
t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM ex_FuelCardInput INNER JOIN t_FuelCardInventory ON
(ex_FuelCardInput.[Fuel Card Number] = t_FuelCardInventory.FuelCardNo) AND
(ex_FuelCardInput.[Fuel Card Provider] =
t_FuelCardInventory.FuelCardProvider) AND (ex_FuelCardInput.[Fuel Card
Source] = t_FuelCardInventory.FuelCardSource) AND (ex_FuelCardInput.VIN =
t_FuelCardInventory.VIN);

Here is the SQL for the Delete Query:
DELETE t_FuelCardErrors.*
FROM t_FuelCardErrors INNER JOIN q_FCErrors_Identical_reload ON
(t_FuelCardErrors.FCNo = q_FCErrors_Identical_reload.FuelCardNo) AND
(t_FuelCardErrors.FCProvider = q_FCErrors_Identical_reload.FuelCardProvider)
AND (t_FuelCardErrors.FCSource = q_FCErrors_Identical_reload.FuelCardSource)
AND (t_FuelCardErrors.VIN = q_FCErrors_Identical_reload.VIN);

Here is the error message from the VBA call:
<<<DoCmd.OpenQuery "q_FCErrors_Delete_Reloads>>>
Run-time error '3086':
Could not delete from specified tables.

Here is what I have tried:
There is a primary key defined in Error Log. The key is all five fields
(QUAD + ErrorDescription)
There are no key violations, no duplicates of QUAD + ErrorDesc.

I can't figure out how to check read only, but I am the creator, editor,
writer and basically god of this Access file. I have not intentionally
specified read only on anything.

I am using Microsoft Access 2003.

Thanks.
Click to expand...
 
B

BlueWolvering

DISTINCTROW worked, so far. Thank you!


John Spencer said:
First thing to try is adding DISTINCTRow to the query

DELETE DISTINCTROW t_FuelCardErrors.*
FROM t_FuelCardErrors INNER JOIN q_FCErrors_Identical_reload ON
(t_FuelCardErrors.FCNo = q_FCErrors_Identical_reload.FuelCardNo) AND
(t_FuelCardErrors.FCProvider =
q_FCErrors_Identical_reload.FuelCardProvider)
AND (t_FuelCardErrors.FCSource =
q_FCErrors_Identical_reload.FuelCardSource)
AND (t_FuelCardErrors.VIN = q_FCErrors_Identical_reload.VIN);

Next try this query

DELETE
FROM t_FuelCardErrors
WHERE FCNo & FCProvider & FcSource & VIN IN
(SELECT FuelCardNo & FuelCardProvider & FuelCardSource & VIN
FROM q_FCErrors_Identical_reload )



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello,
I am trying to delete entries out of a table (call it Error Log). The
criteria for deleting entries is complicated so bear with me.

I am comparing two tables (Input and Storage) and seeing the discrpancies
with them. Right now, I generate error messages that get logged into the
Error Log for things that aren't really errors, like the input and the
storage being identical. So I want to delete any entries in the error log
where the quad of FCN, FCP, FCS, and VIN match with input and storage.

SO I have a select query storing all the matched of FCN, FCP, FCS, VIN (call
that foursome the QUAD) between Input and Storage. So all QUADS where input
and storage match are stored in a query, call MATCHUPS.

Now, I compare the ERROR LOG to MATCHUPS for QUAD matches. I want to write
a delete query to delete any entries in ERROR LOG where QUAD matches and
entry in MATCHUPS.

Here is a translation so that you can read the code I am going to attach. I
didn't use real field names above both for clarity and brevity.

Input = ex_FuelCardInput
Storage = t_FuelCardInventory
Error Log = t_FuelCardErrors
MATCHUPS = q_FCErrors_Identical_reload
FCN = FuelCardNo (or FCN, or Fuel Card Number, but something like that)
FCP = FuelCardProvider (etc etc as above)
FCS = FuelCardSource (etc etc as above)
VIN = VIN

Here is the SQL for MATCHUPS:
SELECT t_FuelCardInventory.FuelCardNo, t_FuelCardInventory.FuelCardSource,
t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM ex_FuelCardInput INNER JOIN t_FuelCardInventory ON
(ex_FuelCardInput.[Fuel Card Number] = t_FuelCardInventory.FuelCardNo) AND
(ex_FuelCardInput.[Fuel Card Provider] =
t_FuelCardInventory.FuelCardProvider) AND (ex_FuelCardInput.[Fuel Card
Source] = t_FuelCardInventory.FuelCardSource) AND (ex_FuelCardInput.VIN =
t_FuelCardInventory.VIN);

Here is the SQL for the Delete Query:
DELETE t_FuelCardErrors.*
FROM t_FuelCardErrors INNER JOIN q_FCErrors_Identical_reload ON
(t_FuelCardErrors.FCNo = q_FCErrors_Identical_reload.FuelCardNo) AND
(t_FuelCardErrors.FCProvider = q_FCErrors_Identical_reload.FuelCardProvider)
AND (t_FuelCardErrors.FCSource = q_FCErrors_Identical_reload.FuelCardSource)
AND (t_FuelCardErrors.VIN = q_FCErrors_Identical_reload.VIN);

Here is the error message from the VBA call:
<<<DoCmd.OpenQuery "q_FCErrors_Delete_Reloads>>>
Run-time error '3086':
Could not delete from specified tables.

Here is what I have tried:
There is a primary key defined in Error Log. The key is all five fields
(QUAD + ErrorDescription)
There are no key violations, no duplicates of QUAD + ErrorDesc.

I can't figure out how to check read only, but I am the creator, editor,
writer and basically god of this Access file. I have not intentionally
specified read only on anything.

I am using Microsoft Access 2003.

Thanks.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

APPEND QUERY FED FROM FORM DOES NOT WORK 3
Form displaying multiple records in subtable, when only one exists 1
Error in Code - never had this before - Run-time error '3061': 2
Error in code - never been there before - set RS as openrecordset( 4
Delete Query error 3086 9
Can't Run Delete Query to Get Rid of Dupes 4
simple Delete Query not working, (Error 3086) 2
Could not delete from specified tables. (Error 3086) 0

Top