DSM Labs Consistency check.accdb

The program

X:\CCMDB\DSM Labs Imports\DSM Labs Consistency check.accdb

generates request extracts and imports lab data as provided by DSM.

The data is stored in DSM Labs data.accdb.

Requesting/importing

• Instructions for requesting a batch of data from DSM
• Instructions for importing a batch of DSM Data

Log

changes for next version

nothing right now

version 2023-09-27

copied to server, notified Pagasa

• removed Trish from email and added Herman

version 2023-05-17

not rolled yet

• removed re-connect for PHI, shouldn't be needed any more and was causing error.

version 2023-01-12

copied to server

• updated created_AdmitDtTm query so it won't crash when there are nulls in all three dates

version 2022-11-03

• added some labs to mappings as per email from Allan. Ttenbergen 10:22, 2022 November 3 (CDT)

version 2022-08-29

copied to server Ttenbergen 15:48, 2022 August 29 (CDT)

• fixed error that was introduced into an SQL statement in the most recent update. Ttenbergen 15:48, 2022 August 29 (CDT)
  • specifically, a previous change to query 1_C_requester_cc_maker had added a "where" clause to that query, and VBA code in Sub Export_requestor_file() was trying to add another where clause; changed that to "and" and it fixed it. Ttenbergen 12:11, 2022 August 31 (CDT)

version 2022-07-07

copied to server Ttenbergen 11:49, 2022 July 12 (CDT)

• added queryRunner to get better error management for queries
• updated compact_runner that wasn't working at all
• updated the command in VBA that generates the 1_D_format_limit data to use the new Admit_DtTm, it was still using the old format.

version 2022-06-09

copied to server

• updated query import_request_matcher to do counts and filter to only 0s
• updated query 1_cc_request_maker to only list vetted records

version 2022-06-01

copied to server

• cleanup: deleted table TEMP and queries tmp_DSM_D_ID_fix_2020-10-01, s_mapping_lab_updater1 and s_group_updater; renamed s_group_updater1 to s_group_updater
• Julie had raised a scenario where duplicate data might arrive in the file; confirmed that import process de-duplicates against import file rather than deleting duplicates after
• Change to replace Accept DtTm with first Service tmp entry, and Arrive DtTm with first Boarding Loc:
  • General:
    • updated DB_Connect to also connect L TmpV2 table, since we now need Boarding Loc and Service tmp entry to make Admit DtTm
    • copied in created_AdmitDtTm query and created_FirstService query from CFE
  • requester:
    • updated query 1_C_requester_cc_maker to use new Admit_DtTm instead of old Arrive_DtTm
    • confirmed that VBA for sending request doesn't need modification
  • importer:
    • confirmed no changes should be required to importer

version 2020-09-30

• updated the specs for the importer so it will work with the RecordStatus field added to requestor in last change. Ttenbergen 14:22, 2020 September 30 (CDT)

version 2020-08-13

• Added RecordStatus field to the DSM requestor to query and file.--PTorres 15:43, 2020 August 13 (CDT)

version 2020-06-04

• fixed link to this page, it still had link to old page name
• changed import to start at Accept_DtTm instead of Arrive_DtTm as it had been; as per various discussions during Task Team Meeting - Rolling Agenda and Minutes 2020

version 2020-05-06

• added "DAO.DBEngine.SetOption dbmaxlocksperfile, 15000" to prevent out-of-diskspace error [1] (archived version)
• changed query 2_dedupe_appender_group to set DSM_TestCode to 0 rather than "not from group" for records arriving as test; this would have generated a hidden error all along but there would be no consequence to the data, this just cleans up that hidden error

version 2019-12-18

• Added several labs as per Allan Garland, incl the ABG labs.
• fixed a bug from last time, re-imported queries I had misunderstood
  • s_group_ListNewFound - used when labs first arrive to mark them with date
  • s_mapping_lab_NewFound - used when labs first arrive to mark them with date

version 2019-12-09

• added automatic reconnecting (on opening the following runs: module DB_Connect, function DB_Connect)
• removed old queries:
  • s_group_ListNewFound (replaced by s_group_imported_today)
  • s_group_ListNewlyAdded (replaced by s_group_imported_today)
  • s_mapping_lab_NewFound (replaced by s_mapping_lab_today)
  • s_mapping_lab_ListNewlyAdded (replaced by s_mapping_lab_today)
• improved error messaging when trying to run export a second time the same day, which breaks because it tries to create a directory which already exists.

version 2019-05-15

• added query Import_request_matcher to be used as part of Instructions for importing a batch of DSM Data.

version 2019-02-14

• tweaked messaging and button hover overs slightly.
• automated re-linking

version 2018-04-03

• updated table s_mapping_lab to see new fields as PTT as per email from Allan Garland to Tina Tenbergen 2018-02-28

version 2017-11-24

• updating importer to cycle through all files in a directory rather than opening each individually.

version 2017-04-29

• changed the way new labs are checked for

version 2016-09-26

Moved to X:

• some tweaks to deal with Alun's new data as of March. Ttenbergen 15:23, 2016 October 17 (CDT)

version 2015-12-01

local for now

• adding checks for locations and labs that have not been encountered before. Ran across different field lengths, emailed Alun.Ttenbergen 14:43, 2015 December 1 (CST)