Access to SQL Server via SSMA - x-ian/dha-mis-migration GitHub Wiki

Process steps to migrate data from DHA-MIS Access backend DB to MSSQL with the help of the SQL Server Migration Assistant for Access.

Prerequisites

  • Access, SQL Server, SSMA
  • Compacted and permission-less Access 2003 Backend DB

Access

  • 'Cleanup' Access by executing these statements in the Immediate window of VBA
DoCmd.RunSQL "UPDATE art_person SET [Qualification]=888 WHERE [Qualification] IS NULL;"
DoCmd.RunSQL "UPDATE psm_dist_item SET [units_adjust]=0 WHERE [units_adjust] IS NULL;"
DoCmd.RunSQL "UPDATE psm_dist_item SET [consum_min]=0 WHERE [consum_min] IS NULL;"
DoCmd.RunSQL "UPDATE htc_person_obs SET [correct_pt_result]=6 WHERE [correct_pt_result] IS NULL;"
' column gone? DoCmd.RunSQL "UPDATE htc_person_obs SET [pt_passed_last_Q]=9 WHERE [pt_passed_last_Q] IS NULL;
  • Change index definition for supply_item.CMS_code according to Access-Findings -* Remove index uniq from psm_relocate for now, needs to be adjusted in code (old: Change index definition for psm_relocate according to Access-Findings)
  • Handle validation rule for dates in htc_site_obs and art_clinic_obs, replace > < with Between for start_time & end_time Access-Findings
  • Change validation rule for email in htc_person temporarily to 'Is Null Or Is Not Null' just for migration purpose
  • Deactivate cascading updates and deletes for concept.concept_ID_parent to concept_1, htc_person.deduplicate_ID to htc_person_1, art_person.DeDuplicate_ID to art_person_1 (according to Access-Findings)
  • Change index definition for htc_prov.ID to required and disable 'allow zero length'
  • htc_person_obs.correct_pt_result values and validation need to be re-checked, allowed from 0 to 6 (with 6 for blank), but also NULL and 9 exists

Cannot insert duplicate key row in object 'dbo.psm_relocate' with unique index 'psm_relocate$uniq'. The duplicate key value is (760, 2719, 14, Jun 27 2012 12:00AM, ). Cannot insert duplicate key row in object 'dbo.psm_relocate' with unique index 'psm_relocate$uniq'. The duplicate key value is (734, 3089, 42, Jul 8 2014 12:00AM, ). Cannot insert duplicate key row in object 'dbo.psm_relocate' with unique index 'psm_relocate$uniq'. The duplicate key value is (795, 2786, 50, Dec 14 2015 12:00AM, ). Cannot insert duplicate key row in object 'dbo.psm_relocate' with unique index 'psm_relocate$uniq'. The duplicate key value is (782, 3663, 5, Jan 26 2017 12:00AM, ).

SSMA

  • Change Default project settings
    • General - Conversion - Tables
      • Add timestamp columns: Never
      • Message when PK includes nullable columns: Error
    • General - Migration - Dates Correction - Replace unsupported dates: Replace with nearest supported date
    • Change Type mapping of Access dates from datetime2 to datetime
  • Start migration wizard
  • Select all tables from Access DB except MSys... and deselect queries
  • Let SSMA create a new SQL Server DB
    • Overwriting an existing instance might be possible, but due to constraint violations it fails. Manually following and deleting the relevant tables in the correct order could work though.
  • Don't link tables

SQL Server

  • Add timestamp column to every table (via SQL Server Management Studio)
  • Paste code into new query of SSMS windows and execute
SELECT
'ALTER TABLE [' + OBJECT_NAME(t.object_id) + ']
ADD access_timestamp TIMESTAMP;'
FROM sys.tables t
WHERE NOT EXISTS
(
SELECT 1
FROM sys.columns
WHERE system_type_id =
(
SELECT system_type_id
FROM sys.types
WHERE name = 'timestamp'
)
AND object_id = t.object_id
)
AND t.type = 'U'
ORDER BY 1
-- from: http://wvmitchell.blogspot.de/2011/06/sql-timestamp-and-access.html
  • Select whole result and copy/paste and execute into another new query windows of SSMS
  • Adjust constraint for psm_relocate according to Access-Findings.
  • Add validation for dates in art_clinic_obs and htc_site_obs
  • Adjust constraint for htc_person.email according to Access-Findings; htc_person email validation Is Null Or ((Like "?@?.?") And (Not Like "[ ,;]*"))

Access

(Re-)link to tables and queries (really necessary?) in frontends

  • Either by manually using the Linked Table Manager / Import features (and potentially removing the dbo_ prefix of every linked table; see VBA script CDDB_ChopTableName for automatic renaming). Prior to this set up a ODBC File datasource to point to the MSSQL DB instance (by using a file-based DSN as opposed to a User or System based DSN), no additional ODBC config is required when copying the MDB frontend. Because Access automatically converts file-based DSNs into DSN-less connections.
  • Or by Invoking the VBA script RelinkAllTables (uses DSN-less ODBC connection, which is encoded in VBA).
⚠️ **GitHub.com Fallback** ⚠️