asked on

record is deleted - more wierd than ever

Hi All.
I am getting a "record is deleted" error when trying to append records into a linked table from another MSACCESS DATABE.

- The two databases are linked on a network
- I have compacted and repaired both databases
- The APPEND QUERY tables are linked to another server over the network to an SQL Database via ODBC
- The is no #Deleted# message in either the append query nor the appending database
- If I make the table local (by copying and pasting", and try to append locally I get the sqame error
- The append was preceded by a delete all query
- I can run the query as a make table query locally
- IF I OPEN THE QUERY AND COPY ALL RECORDS AND THEN OPEN THE LINKED TABLE AND PASTE THEM, IT WORKS (unfortunaetly, I need to run this asutomatically)
- IF I REMOVE THE LEFT JOIN ON ACCOUNTS_2, IT WORKS!!
- The error occurs by trying to update the "ACCOUNTS_2.AccountName AS DeliveryFranchisee" field which does contain empty values. (IT IS APPENDING TO A TEXT FIELD)

below is the append query:
==========================================================================
INSERT INTO tblPackingBarcodes ( FranchiseePackingAllocation, CustomerGroupAllocation, FranchiseePackingOrder, FranchiseeID, FranchiseeDeliveryID, CustomerID, CustomerOrder, BarcodeSalesDTLID, DeliveryDate, Franchisee, DeliveryFranchisee, Customer, Barcode, PackingSectionSplit, ItemNumber, ItemName, ItemUOM, PackingComment, PickQty, Quantity, MultipleBarcodes )
SELECT ACCOUNTS_1.FranchiseePackingAllocation, 0 AS CustomerGroupAllocation, 0 AS FranchiseePackingOrder, SALESHEADER.ParentAccountID AS FranchiseeID, ACCOUNTS.DeliveryAccountID, SALESHEADER.AccountID AS CustomerID, 0 AS CustomerOrder, SALESDETAIL.SalesDTLID AS BarcodeSalesDTLID, SALESHEADER.DeliveryDate, ACCOUNTS_1.AccountName AS Franchisee, ACCOUNTS_2.AccountName AS DeliveryFranchisee, ACCOUNTS.AccountName AS Customer, StrConv([SalesDTLID],1) AS Barcode, ITEMPACKINGLOCATIONS.PackingSectionSplit, ITEMS.ItemNumber, ITEMS.ItemName, ITEMUOMS.ItemUOM, SALESDETAIL.PackingComment, 0 AS PickQty, SALESDETAIL.Quantity, ITEMUOMS.MultipleBarcodes
FROM (ITEMPACKINGLOCATIONS INNER JOIN (((ACCOUNTS INNER JOIN ACCOUNTS AS ACCOUNTS_1 ON ACCOUNTS.ParentAccountID = ACCOUNTS_1.AccountID) INNER JOIN SALESHEADER ON ACCOUNTS.AccountID = SALESHEADER.AccountID) INNER JOIN ((ITEMS INNER JOIN ITEMUOMS ON ITEMS.ItemSellUOMID = ITEMUOMS.ItemUOMID) INNER JOIN SALESDETAIL ON ITEMS.ItemID = SALESDETAIL.ItemID) ON SALESHEADER.SalesHDRID = SALESDETAIL.SalesHDRID) ON ITEMPACKINGLOCATIONS.PackingID = ITEMS.PackingID) LEFT JOIN ACCOUNTS AS ACCOUNTS_2 ON ACCOUNTS.DeliveryAccountID = ACCOUNTS_2.AccountID
WHERE (((SALESHEADER.DeliveryDate)=Date()) AND ((SALESDETAIL.Quantity)>0) AND ((ACCOUNTS.AccountLevel)=3) AND ((SALESHEADER.InvoiceStatusID)="OR"));
=====================================================================

THANKING YOU IN ADVANCE!

Pavel Celba

I would recommend to remove LEFT JOIN and DeliveryFranchisee column insertion because it works for you. But you should insert DeliveryAccountID into tblPackingBarcodes table and create additional query with LEFT JOIN:

INSERT INTO tblPackingBarcodes (
FranchiseePackingAllocation, CustomerGroupAllocation, FranchiseePackingOrder,
FranchiseeID, FranchiseeDeliveryID, CustomerID,
CustomerOrder, BarcodeSalesDTLID,
DeliveryDate, Franchisee,
Customer, Barcode, PackingSectionSplit,
ItemNumber, ItemName, ItemUOM, PackingComment,
PickQty, Quantity, MultipleBarcodes, DeliveryAccountID )
SELECT ACCOUNTS_1.FranchiseePackingAllocation, 0 AS CustomerGroupAllocation, 0 AS FranchiseePackingOrder,
SALESHEADER.ParentAccountID AS FranchiseeID, ACCOUNTS.DeliveryAccountID, SALESHEADER.AccountID AS CustomerID,
0 AS CustomerOrder, SALESDETAIL.SalesDTLID AS BarcodeSalesDTLID,
SALESHEADER.DeliveryDate, ACCOUNTS_1.AccountName AS Franchisee,
ACCOUNTS.AccountName AS Customer, StrConv([SalesDTLID],1) AS Barcode, ITEMPACKINGLOCATIONS.PackingSectionSplit,
ITEMS.ItemNumber, ITEMS.ItemName, ITEMUOMS.ItemUOM, SALESDETAIL.PackingComment,
0 AS PickQty, SALESDETAIL.Quantity, ITEMUOMS.MultipleBarcodes, ACCOUNTS.DeliveryAccountID
FROM (ITEMPACKINGLOCATIONS INNER JOIN (((ACCOUNTS INNER JOIN ACCOUNTS AS ACCOUNTS_1 ON ACCOUNTS.ParentAccountID = ACCOUNTS_1.AccountID) INNER JOIN SALESHEADER ON ACCOUNTS.AccountID = SALESHEADER.AccountID) INNER JOIN ((ITEMS INNER JOIN ITEMUOMS ON ITEMS.ItemSellUOMID = ITEMUOMS.ItemUOMID) INNER JOIN SALESDETAIL ON ITEMS.ItemID = SALESDETAIL.ItemID) ON SALESHEADER.SalesHDRID = SALESDETAIL.SalesHDRID) ON ITEMPACKINGLOCATIONS.PackingID = ITEMS.PackingID)
WHERE (((SALESHEADER.DeliveryDate)=Date()) AND ((SALESDETAIL.Quantity)>0) AND ((ACCOUNTS.AccountLevel)=3) AND ((SALESHEADER.InvoiceStatusID)="OR"));

SELECT t.*, ACCOUNTS_2.AccountName AS DeliveryFranchisee
  FROM tblPackingBarcodes t
  LEFT JOIN ACCOUNTS AS ACCOUNTS_2 ON t.DeliveryAccountID = ACCOUNTS_2.AccountID ;

Open in new window

ASKER CERTIFIED SOLUTION

BillKollatos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BillKollatos

ASKER

sorry pcelba, one more thing. How exactly do you suggets to run this secondary select query? within the existing append query? not sure how you do this in an access query?

Pavel Celba

The update is good solution in your situation.

The second query was oferred as a replacement for tblPackingBarcodes table without DeliveryFranchisee column. It means the append query can insert everything except DeliveryFranchisee which will be added in the second query without update necessity.

Everything above is a work around and Access 2003 Jet engine is probably unable to handle it as you need in one simple append query.

You could try ACE engine in Access 2007 which is much better.