Instructions for exporting data using MCHP Export.accdb: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
← Older edit
JMojica (talk | contribs)
CCMDB_legit, admin, approved, Administrators
2,553 edits
JMojica (talk | contribs)
CCMDB_legit, admin, approved, Administrators
2,553 edits
 
(44 intermediate revisions by 2 users not shown)
Line 1: Line 1:
This article contains the instructions to generate data for [[Manitoba Centre for Health Policy export]] using [[MCHP Export.mdb]]
This article contains the instructions to generate data for [[Manitoba Centre for Health Policy export]] using [[MCHP Export.accdb]]


== Instructions ==
== Instructions ==
=== Schedule and Frequency ===
* Yearly export to be done anytime in April of each year - as prescribed by Dave Towns (Senior Data management Analyst, Repository MCHP) on Aug 4, 2023.
** It was decided in Task meeting 22May2024 to put on hold any data sharing with MCHP until Shared Health decides on the possible option for hosting the database (e.g. platform of the CCMDB) which at the moment has no action yet.
** Aug 12, 2024 - resume sending data to MCHP as one previous investigator is getting updated data for his study to MCHP. One thing to note with MCHP is the DSM data not being reliable and complete.
=== Preparations ===
=== Preparations ===
# [[Pull]] down the data to the usual location
# [[Pull]] down the data to the usual location (desktop/CentralizedFrontEndLive)
# copy [[MCHP Export.mdb]] to the same location
# copy [[MCHP Export.accdb]] to the same location
# copy [[DSM Labs Consistency check.accdb]] to the same location


=== generating the export file ===
=== generating the export file ===
* open [[MCHP Export.mdb]]
* open [[MCHP Export.accdb]]
* edit the code to '''set the date range''' and accommodate any data structure changes that might have occurred since the last export
* change extract dates if needed; actually, we don't generally change the dates, we always send 2019-01-01 to current
** edit the VBA code to '''set the date range'''  
*** in the program, click the "Database Tools" button bar tab a the top, then the "Visual Basic" button
*** edit the start_threshold and end_threshold variables
* accommodate any data structure changes that might have occurred since the last export (or confirm there are no such changes)
** e.g. are there any new tmp projects that should be included?
* Click the "Run MCHP Export" button
* Click the "Run MCHP Export" button
 
** if error message comes up due to large size issue, close the accdb. Get a copy again of the master MCHP Export, open, purge the data and run the MCHP Export button again.
{{Discuss | who=Julie |question=
* Do we want the exporting of PHI for MB Health to be a separate thing or a step in here? I don't want to generate it as a table in here and risk it being passed along to MCHP unencrypted. But if I generate it as an export to e.g. .csv I would need to know what format we need. Also, do we have the process for getting that encoded written on here somewhere else already? If not we should add it. Is the process only used for MCHP? If more general it should probably go on its own page and be linked.
**as far as I remember, I just simply zipped the PHI.mdb  and encrypted, saved in CD and sent to MB Health via dept mail. Similarly, I zipped the MCHP_Export.mdb and encrypted, saved in CD and hand carried it to Dave Towns at MCHP.  Can we not do the same? no need to have another separate export for PHI into another format (i.e. CSV or other).
*** Your plan could work. They would get all PHI records, ie including the newest which we wouldn't send. This may not be a problem at all, but would want to be sure it doesn't trigger some QI concerns when MCHP matches things up. If we confirm it's fine with them then we have a nice simple process. We would probably still want to have detailed contact info re. where to send at MB Health. Is it [[MB Health Crosscheck Contact]]? If so that link would do the trick.
****This is different from the category [[MB Health Crosscheck Contact]]. the contact person is already listed on the sending below
}}
* '''delete the modules and forms, they contain [[:Category:Password | Passwords]]'''
* '''delete the modules and forms, they contain [[:Category:Password | Passwords]]'''


=== sending ===
=== sending ===
* review [[MCHP_Cleanup]] to make sure there are no big outstanding questions that will make our data look bad
* review to make sure there are no big outstanding questions that will make our data look bad; a page on the wiki that might help with this is [[Portal related pages with questions]]
* copy [[MCHP Export.mdb]] to a memory stick or CD
* Zip the individual files by using the 7-zip file manager - find the files and click '''ADD''' and it will open a screen. Rename the files into CCMDB_MCHP_export_date and CCMDB_phi_date.  Encrypt the zip files by using the passwords indicated from the excel file of Passwords_MainOffice from the shared folder.
* send the memory stick or CD to Jason Berry, Privacy Policy Analyst, of Information Management and Analytics, Manitoba Health, Healthy Living & Seniors (4040D -300 Carlton Street, Winnipeg MB R3B 3M9) and email  the password to him (Jason.Berry@gov.mb.ca); Manitoba Health with de-identify the data and pass it on to MCHP.
* after zipping the files in the next two steps, try opening them on a different computer before sending them out of office; we had a problem with the zipped file during the 2020-04-30 send.
* the zipped [[MCHP Export.accdb]] and password protected
** send via  MCHP sFTP server .  Julie has been given a temporary account by Rod McRae, IManager, Manitoba Centre for Health Policy. Every time needed, Julie has to request and email Rod McRae <Rod_McRae@cpe.umanitoba.ca> and Dave Towns.
*** go to  https://collab.mchp.ca/sites/sft. Enter the provided username and password by MCHP (see Passwords_MainOffice). Double click document CriticalCare_Medicine. Drag the zipped file to upload or choose the file to start uploading.  Sign out once done.
** email  the password to Dave Towns (Dave_Towns@cpe.umanitoba.ca).
* the zipped [[PHI.mdb]] and password protected
** send password encrypted PHI.mdb via MHSAL’s secure file transfer system to Jason Berry, A/Director, of Information Management and Governance Unit, Information Management and Analytics, Resources and Performance, Manitoba Health, Healthy Living & Seniors (4041 -300 Carlton Street, Winnipeg MB R3B 3M9) and email  the password to him (Jason.Berry@gov.mb.ca); Manitoba Health with de-identify the data and pass it on to MCHP. Julie has an account to MHSAL secure file transfer system.
**starting Aug 2023, the contact person has been changed to Fan Zhaopeng (Zhaopeng.Fan@gov.mb.ca), manager of the Axway at MB Health.
*** go to https://mbhtransfer.mbhealth.ca/. Enter the userid and pw (see Passwords_MainOffice). Go to mailbox and compose a new email directed to Fan Zhaopeng (Zhaopeng.Fan@gov.mb.ca) and attach the zipped [[PHI.mdb]] and send. Logout.
 
=== saving ===
* make a new sub-folder labelled with the date data was generated in the folder '''S:\MED\MED_CCMED\CCMDB_Special_Projects\MCHP'''.
* save a copy [[MCHP Export.accdb]] and [[PHI.mdb]] and their zip files from the desktop to this new folder.
 
== Legacy info ==
{{LegacyContent
|explanation=old process
|successor=see above
|content=
* We used to hand deliver this data:
** copying to a memory stick or CD and  hand carry the memory stick or CD to Dave Towns, Senior Data Management Analyst, of Manitoba Centre for Health Policy (MCHP), Dept of Community Health Sciences, University of Manitoba - stopped Aug 2019
** starting  April 2020, data is transferred via sFTP server as per instructions above
}}


== Related articles ==  
== Related articles ==  

Latest revision as of 10:37, 2024 August 13

This article contains the instructions to generate data for Manitoba Centre for Health Policy export using MCHP Export.accdb

Instructions

Schedule and Frequency

  • Yearly export to be done anytime in April of each year - as prescribed by Dave Towns (Senior Data management Analyst, Repository MCHP) on Aug 4, 2023.
    • It was decided in Task meeting 22May2024 to put on hold any data sharing with MCHP until Shared Health decides on the possible option for hosting the database (e.g. platform of the CCMDB) which at the moment has no action yet.
    • Aug 12, 2024 - resume sending data to MCHP as one previous investigator is getting updated data for his study to MCHP. One thing to note with MCHP is the DSM data not being reliable and complete.

Preparations

  1. Pull down the data to the usual location (desktop/CentralizedFrontEndLive)
  2. copy MCHP Export.accdb to the same location
  3. copy DSM Labs Consistency check.accdb to the same location

generating the export file

  • open MCHP Export.accdb
  • change extract dates if needed; actually, we don't generally change the dates, we always send 2019-01-01 to current
    • edit the VBA code to set the date range
      • in the program, click the "Database Tools" button bar tab a the top, then the "Visual Basic" button
      • edit the start_threshold and end_threshold variables
  • accommodate any data structure changes that might have occurred since the last export (or confirm there are no such changes)
    • e.g. are there any new tmp projects that should be included?
  • Click the "Run MCHP Export" button
    • if error message comes up due to large size issue, close the accdb. Get a copy again of the master MCHP Export, open, purge the data and run the MCHP Export button again.
  • delete the modules and forms, they contain Passwords

sending

  • review to make sure there are no big outstanding questions that will make our data look bad; a page on the wiki that might help with this is Portal related pages with questions
  • Zip the individual files by using the 7-zip file manager - find the files and click ADD and it will open a screen. Rename the files into CCMDB_MCHP_export_date and CCMDB_phi_date. Encrypt the zip files by using the passwords indicated from the excel file of Passwords_MainOffice from the shared folder.
  • after zipping the files in the next two steps, try opening them on a different computer before sending them out of office; we had a problem with the zipped file during the 2020-04-30 send.
  • the zipped MCHP Export.accdb and password protected
    • send via MCHP sFTP server . Julie has been given a temporary account by Rod McRae, IManager, Manitoba Centre for Health Policy. Every time needed, Julie has to request and email Rod McRae <Rod_McRae@cpe.umanitoba.ca> and Dave Towns.
      • go to https://collab.mchp.ca/sites/sft. Enter the provided username and password by MCHP (see Passwords_MainOffice). Double click document CriticalCare_Medicine. Drag the zipped file to upload or choose the file to start uploading. Sign out once done.
    • email the password to Dave Towns (Dave_Towns@cpe.umanitoba.ca).
  • the zipped PHI.mdb and password protected
    • send password encrypted PHI.mdb via MHSAL’s secure file transfer system to Jason Berry, A/Director, of Information Management and Governance Unit, Information Management and Analytics, Resources and Performance, Manitoba Health, Healthy Living & Seniors (4041 -300 Carlton Street, Winnipeg MB R3B 3M9) and email the password to him (Jason.Berry@gov.mb.ca); Manitoba Health with de-identify the data and pass it on to MCHP. Julie has an account to MHSAL secure file transfer system.
    • starting Aug 2023, the contact person has been changed to Fan Zhaopeng (Zhaopeng.Fan@gov.mb.ca), manager of the Axway at MB Health.
      • go to https://mbhtransfer.mbhealth.ca/. Enter the userid and pw (see Passwords_MainOffice). Go to mailbox and compose a new email directed to Fan Zhaopeng (Zhaopeng.Fan@gov.mb.ca) and attach the zipped PHI.mdb and send. Logout.

saving

  • make a new sub-folder labelled with the date data was generated in the folder S:\MED\MED_CCMED\CCMDB_Special_Projects\MCHP.
  • save a copy MCHP Export.accdb and PHI.mdb and their zip files from the desktop to this new folder.

Legacy info

Legacy Content

This page contains Legacy Content.
  • Explanation: old process
  • Successor: see above

Click Expand to show legacy content.

  • We used to hand deliver this data:
    • copying to a memory stick or CD and hand carry the memory stick or CD to Dave Towns, Senior Data Management Analyst, of Manitoba Centre for Health Policy (MCHP), Dept of Community Health Sciences, University of Manitoba - stopped Aug 2019
    • starting April 2020, data is transferred via sFTP server as per instructions above


Related articles

Related articles:
Retrieved from "https://ccmdb.kuality.ca/index.php?title=Instructions_for_exporting_data_using_MCHP_Export.accdb&oldid=161679"