Make Table Query Problem

[LAgnor]

I inherited a db that uses a series of make table queries. Yesterday, when I went to run the queries I received the error message, "You can't delete table [ ]; it is participating in one or more relationships". Can anyone tell me what the error is, how to fix it, why it just started?

Thanks,

Len

 

[MarcieFess]

Can you post the SQL code for the query in question so we can take a look at it?

It sounds like somewhere, sometime since the last time you ran these queries, someone modified the database in such a way that one of these tables had a relationship created to another object, which is locking that table so it can't be deleted.

One solution is to insert a global delete command which would delete the contents of the table without actually deleting the table.

Marcie

 

[Mihail]

Open the Relation Ships window and locate the table.
I'm pretty sure that you will see a relationship between this table and another one.

An work around is to use a DELETE query in order to remove all records from that table and a APPEND query in order to repopulate the table.

 

[LAgnor]

SQL code
SELECT IIf(Len([1b_Import BOM]![Material])<8,IIf(Left([1b_Import BOM]![Material],1)<>9,Null,[1b_Import BOM]![Material]),IIf(Left([1b_Import BOM]![Material],1)=1,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=2,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=3,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=4,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=5,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=6,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=7,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=8,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=9,[1b_Import BOM]![Material],Null)))))))))) AS Material, [1b_Import BOM].Plant, [1b_Import BOM].Usage, [1b_Import BOM].[BOM #], [1b_Import BOM].[Alt #], [1b_Import BOM].Item, [1b_Import BOM].Component, IIf([1b_Import BOM]![Component]<9000000,Null,[1b_Import BOM]![Quantity]) AS Quantity, [1b_Import BOM].UOM, IIf([1b_Import BOM]![Description] Like "*by*",IIf([1b_Import BOM]![Quantity]<=0,Null,[1b_Import BOM]![Description]),[1b_Import BOM]![Description]) AS Description, [1b_Import BOM].Status, [1b_Import BOM].[Cost Rel], [1b_Import BOM].Bulk, [1b_Import BOM].Decription, [1b_Import BOM].[(Cnctnt) Plant Material], [1b_Import BOM].[(Cnctnt) Plant Component], [1b_Import BOM].[(Cnctnt) Plant Mat'l / Component / Useage] INTO [1c_BOM_in SAP]
FROM [1b_Import BOM]
WHERE (((IIf(Len([1b_Import BOM]![Material])<8,IIf(Left([1b_Import BOM]![Material],1)<>9,Null,[1b_Import BOM]![Material]),IIf(Left([1b_Import BOM]![Material],1)=1,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=2,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=3,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=4,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=5,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=6,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=7,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=8,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=9,[1b_Import BOM]![Material],Null))))))))))) Is Not Null) AND (([1b_Import BOM].Plant)<>1600 And ([1b_Import BOM].Plant)<>2100) AND (([1b_Import BOM].[Alt #])=1) AND (([1b_Import BOM].Component)>999999 And ([1b_Import BOM].Component)<3000000) AND ((IIf([1b_Import BOM]![Description] Like "*by*",IIf([1b_Import BOM]![Quantity]<=0,Null,[1b_Import BOM]![Description]),[1b_Import BOM]![Description])) Is Not Null)) OR (((IIf(Len([1b_Import BOM]![Material])<8,IIf(Left([1b_Import BOM]![Material],1)<>9,Null,[1b_Import BOM]![Material]),IIf(Left([1b_Import BOM]![Material],1)=1,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=2,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=3,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=4,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=5,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=6,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=7,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=8,[1b_Import BOM]![Material],IIf(Left([1b_Import BOM]![Material],1)=9,[1b_Import BOM]![Material],Null))))))))))) Is Not Null) AND (([1b_Import BOM].Plant)<>1600 And ([1b_Import BOM].Plant)<>2100) AND (([1b_Import BOM].[Alt #])=1) AND (([1b_Import BOM].Component)>3999999) AND ((IIf([1b_Import BOM]![Description] Like "*by*",IIf([1b_Import BOM]![Quantity]<=0,Null,[1b_Import BOM]![Description]),[1b_Import BOM]![Description])) Is Not Null))
ORDER BY [1b_Import BOM].Component;