CODEWITHSUNDEEP
vbams-access
Shadyjunior
Shadyjunior

Reputation: 457

Access - Update Imported Data

In the morning, data is pulled from Microsoft Navision into Excel...

enter image description here

This data is then copied into Access, where this data is utilised for production scheduling etc. When this data copied into Access, a few extra columns are added to help with scheduling and traceability, ie cut, folded, finished.

enter image description here

What I would like to be able to do, is that if we run the Microsoft Navision report again and lets say A3, A5 and A12 statuses have all changed to Finished, that when we copy and paste all these rows into Access that the status will change dependant on the Prod. Order No. related to that status.

Upvotes: 0

Views: 838

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider using a temp table in Access that regularly cleans out previous and imports current Excel output. Then, run an update inner join query against your final Access table with conditional IIF logic.

Below are three action commands that you can save as three separate queries and run them together in VBA with DoCmd.OpenQuery or CurrentDb.Execute during your import process. Of course adjust to actual names.

Delete all data in temp table

DELETE FROM ExcelTempTable;

Append to temp table

INSERT INTO ExcelTempTable
SELECT * 
FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Workbook.xlsx].[SheetName$] AS t;

Update final table

UPDATE AccessFinalTable a
INNER JOIN ExcelTempTable e ON a.[Production Number] = e.[ProdOrderNo] 
SET a.Finished = IIF(e.Status = 'Finished', True, False),
    a.Folded = IIF(e.Status = 'Folded', True, False),
    a.Cut = IIF(e.Status = 'Cut', True, False);

Append final table (append records of only new Production Numbers)

INSERT INTO AccessFinalTable (Col1, Col2, Col3, ...)
SELECT e.Col1, e.Col2, e.Col3, ...
FROM ExcelTempTable e
LEFT JOIN AccessFinalTable a
ON e.[Production Number] = a.[ProdOrderNo] 
WHERE a.[Production Number] IS NULL

Upvotes: 1

Related Questions