Un-Updateable queries and IIf if records exist in the queries

[Oblio]

Goal: Update the table t_Scan_Errors, if there are errors, with the values obtained in queries “q_Missing_Folders” and q_Missing_MCRs_In_Folders
t_Runs is a One to Many relationship with t_Scan_Errors
Sign in under Warren Nelson, password is “Welcome” without the quotes.
To see the report, select the only report available in the combobox on the Report Parameters Form, and the date Oct 26 2017 from the date selector.

Process Overview:

Scanner fills in their day using the form Scan_Operator_Details_Center.
Once they have filled it all in they save and close it.
Having finished the scanning for the day, they run the Import Files form to import all of the files, and their respective directories
Then they run the Report Parameter form for the days summary. I have purposely deleted some folders and files, and you will notice in the Errors area, under Missing Folder #, that the folders 16 and 23 have been missed by the scan operator. This means when they were creating the sequential folder numbers to store the scans in, they skipped these two numbers.
You can also see that there are two folders listed in the “No MCR’s in Folder”, Folder 12 and 24. MCR is a particular file named 01.pdf. Every folder should have one of them, however, 12 and 24 are missing it.

t_Runs records information about each scanning of documents run.

t_Runs:
Runs_ID Primary Key, Long Integer

t_Scan_Errors is a table that holds any errors found by queries after a scanning run.
t_Scan_Errors:
Scan_Errors_ID Primary Key, Long Integer
Runs_ID_FK Foreign Key to t_Runs, Number
Error_Types_ID_FK Foreign Key to lt_Scan_Errors, Number

t_Error_Types:
Error_Types_ID Primary Key, Long Integer
Error_Name (One of Two Choices: Missing Folder or Missing File In Folder)

q_Missing_Folders is an unupdateable query that returns missing sequential folder number(s) if there are any

q_Missing_MCRs_In_Folders provides the folder number if there are any missing files named “01.pdf”

The goal is to create and then append all of the summary data to a table in a Master Database that records all of the final statistics… I have not created this until I hear advice on how I should design this. Both databases will be split and multi-user…

The report is a summary of the information I want to record as a summary table in the new database.
Also, is there a way to copy the database in VBA and compress it and move it to a folder on the server in another drive? Would anyone have a sample and/or advice?

Thanks for your help as always, VBA Express !

Oblio

[OBP]

Hello again Warren, I see that you have moved on.
I will download the database and take a look.

[OBP]

Warren, the usual problem you are using a much later version of Access than me, so I can't open the Report or run the Missing Folder Query, but I can run the Missing Mcrs one.

[Oblio]

Oh, so sorry about that.... which version of Access works for you?

[OBP]

Access 2007.

[Oblio]

Dang it, it says it requires features from Access 2016 and cannot convert it to Access 2007...I am not sure what those features are... any other ideas???

[OBP]

The feature that is causing the problem is in the t scan operators daily input table.
It obviously has a feature that is not in Access 2007.

[Oblio]

Weird....I am not sure what it is...it is a very simple table and form...will look tomorrow. Have a good night!

[OBP]

It may be the Scanned doc field if it is not a standard text field. Later Access versions have some fancy links and storage compared to the older versions.
You could try copying the data in to an Access 2007 version table.