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!


BillKollatosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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

0
BillKollatosAuthor Commented:
Yes thankyou.  I can get around the issue.  I do so by having another update query that updates DeliveryFranchisee for the relevant accounts.

The thing is that this has been working and sometimes just simply stops.  I guess I wanted to know if there is a clear MS Access technical reason for it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BillKollatosAuthor Commented:
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?
0
pcelbaCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.