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!