In Switchboard for Change Backend Button Private Sub UpdateLinkedTables_Click() DoCmd.OpenForm "Switchboard Backend Selector" End Sub

Copy/paste Form Switchboard Backend Selector from blueprint DB

Add to Switchboard: 1 Textfields DbUser, ServerName, DbInstance, DbTrustedConnection 1 Modify switchboard form load to

Private Sub Form_Load()

    ' leave app if no db access
    If Not databaseAccess() Then
        MsgBox "No Database access." & vbCrLf & "Either wrong user credentials or Database Server not available)." & vbCrLf & "username, server" & vbCrLf & "Exiting now."
        Application.Quit
    End If

	' ... do your stuff 
	
    Call updateConnectionDetails
    Me.DbUser.value = CurrentUser()
    Me.ServerName.value = CurrentServer()
    Me.DbInstance.value = CurrentDbInstance()
    Me.DbTrustedConnection = CurrentDbTrustedConnection()
End Sub

1 Modify Switchboard to

Private Sub UpdateLinkedTables_Click()
    'RunCommand acCmdLinkedTableManager
    DoCmd.OpenForm "Switchboard Backend Selector"
    Call updateConnectionDetails
    Me.DbUser.value = CurrentUser()
    Me.ServerName.value = CurrentServer()
    Me.DbInstance.value = CurrentDbInstance()
    Me.DbTrustedConnection = CurrentDbTrustedConnection()
End Sub

Create SQL passthrough query current_user

SELECT system_user;

Create/import VBA module RelinkTables. This will allow the user to change the backend, e.g. from live to test. This will also (re-)link all tables and views from MSSQL as listed in the RelinkTables.main_RelinkTables procedure.

Change expression of field 'LinkPath' above Change DB Backend to '=IIf(IsLiveDatabase()=True,"Live data","Offline data")'

Create/import VBA module ReapplyFieldProperties

Every Recordset that is opened via VBA code must use the optional parameter dbSeeChanges with ODBC Linked Tables. All (most?) occurrences of these cases can be automatically updated by running the VBA code from MigrateCodeToMssql.main_MigrateCodeForAllModules.

Activate ODBC Fields under Current database options - Filter lookup options

As the MDW (workgroup file) is only supported in MDB format (Access 2003) and is not available with a MSSQL backend, a new approach into the current security/auditing feature need to be written.

For now the best way forward is a Trusted ODBC connection from the Access frontend to the MSSQL with automatic authentication of (Windows) users against the Active Directory. Everybody with a valid Windows account and being part of an Active Directory group would be automatically authenticated.

Once a connection is established, the current user can be retrieved from MSSQL and then used throughout the frontend. It might even be possible to overwrite the default Access function CurrentUser() with a custom global function.

Below is a prototype for this global function (not work-able as it is). This function requires an ODBC passthrough query to query MSSQL for the current user name (select SUSER_SNAME() as username;): Dim current_user As Variant Public Function CurrentUser() As String If current_user = Nothing Then Dim qdf As QueryDef Dim rst As Recordset Set qdf = CurrentDb.QueryDefs("current_user") Set rst = qdf.OpenRecordset current_user = rst!CurrentUser Else CurrentUser = current_user End If End Function

The above code runs in the frontend similar to what is currently used. An alternative could be to offload the calculation of create&update user&timestamp to the server. After this change a conversion to the

to be defined:

• exact integration with Active Directory
• different DHA-MIS groups (psm, de, admin)
• read/write permissions in MSSQL

Every (most?) controls on Forms can potentially have multiple Eventhandlers (like OnClick or OnUpdate). These Eventhandlers are labeled after the name of the control plus a postfix of the type of event (like btnDoSomething_OnClick).

Typically these handlers are automatically created when clicking in the Form Designer to the matching Control property. However after this event handler is generated in code, it is possible to remove the link/reference to this particular event handler from the Control properties. This will leave the code of event handler (including its name) intact, but removes the reference from the control to this handler. It won't be used anymore. It could be considered to be bad practice to do this, but nevertheless possible.

During the upgrade process of the old MDB frontend file to the new ACCDB format, all objects including controls and code were imported (a simple 'Save as' in the new file format didn't properly work). But this import of Access re-creates links between event handlers of controls and code whenever it finds a matching name (like btnDoSomething_OnClick) regardless of what the property of this control contained. Therefore during this upgrading previously deactivated event handlers were activated again.

The code in ExportAllControlEventHandlers loops through every control of every form and creates a file called controls.csv in the directory of the Access file. Doing this for old and migrated frontend allows a file diff to spot the differences.

Within 3000 potential Event Handlers these differences were detected:

• htc_provider_obs,htc_prov_id,OnExit,
• htc_provider_obs,Correct_pt_result,OnDirty,[Event Procedure]
• art_drug_stocks_supply_item_v10,expiry_date_min,OnDirty,[Event Procedure]
• art_clinic_obs_v8,year_quarter_id,AfterUpdate,[Event Procedure]

No differences detected among ~6300 potential event handlers.

Create new .accdb file and import every Access object from old .mdb.

VBA Code Index/seek optimization

Added table version, query version_max, form version and adjusted Switchboard header to use it.

Adding unregistered HTC counsellor with ID 4444 (which form?)

DBEngine(0).OpenDatabase(DbPath) - OpenDatabase with LinkedTables leads to runtime error 3024; once in Form_htc_provider_obs and twice in Form_obs_set. Instead of directly open the MDB files to use their index (which doesn't work with Linked Tables), it should be possible to rewrite this to directly use the proper queries.

Some sources recommend the use of .LastModified right after the record was saved to bookmark and retrieve the ID of the newly created record. There is a small chance for a race condition / concurrency conflict, so instead of using this, a new query is invoked to lookup the ID of this new record.

Import from github project / access / de-frontend / Form...

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column. (e.g. in Sub Form_Load of Form_art_clinic_obs_v8).

Used 20 times in 5 'files' (Form_art_clinic_obs_v8.cls, Form_htc_provider_obs.cls, Form_obs_set.cls, Form_psm_ro_item_set.cls, PubFunctions.bas).

Change OpenRecordset method to include additional 'dbSeeChanges' option (use + if multiple options need to be specified, use ', , dbSeechanges' if no type is specified before the options like dbSeeChanges)

For existing forms the data for page 3 is loaded together with the historical data, for new forms the page is empty. This is due to the different approach with AutoNumber fields. New numbers in Access are already created when opening a new record, while in MSSQL the number is calculated during the insert activity on the server. Therefore the newly assigned ID is only available after the insert was completed.

But for the page 3 the VBA code already pre-populates the temporary tables for drug stocks as soon as the Access backends provides an ID. With the MSSQL backend this ID is at this point in time not yet present and therefore leaving the temp drug stocks tables empty.

Adding this fetch to the event AfterInsert of the form art_clinic_obs_v8 makes the data available again:

Private Sub Form_AfterInsert()
    Drug_Stocks_Fetch
End Sub

Add Me!art_drug_stocks_supply_item.Form.Requery to sub Form_Current() of form Form_art_clinic_obs_v8.

Imports with .* from concept_live. But local table structure doesn't (and shouldn't!) have SSMA Timestamp. Replace the .* in Query definition with every single column except SSMA.

Similar to concept_app

• Is used to update (Frontend) local temporary Access tables that collect data points for an already entered cohort report of one facility.
• Only contains around 1500 rows, but is updated from 15 million obs and 400000 obs_dimensions.
• Inner joins 2 local and 2 linked tables (obs, obs_dimensions) together
• Takes much longer with MSSQL (not measured, but maybe 4x slower); now around 10 seconds
• SQL Profiler shows, that for one invocation a few 1000 ODBC calls are executed.
• Assumption (without full confirmation):
  • Nested inner joins prevent query parser from collecting 1500 joined records from MSSQL first and then invoking the update operation. Instead for every update a new request (maybe more precisely a reposition of the cursor) is invoked on MSSQL.
  • Or Access doesn't understand that the parameter (art_clinic_obs_v8.ID) to filter out the majority of records () can also be applied to the obs and obs_dimensions join and need to pull the complete result of the join (15 million records) before it can filter the records.

Optimization attempts:

1 Passthrough queries not possible due to local tables. Also passthrough queries can not be parameterized (as stupid as this sounds) 1 Server-side view between obs and obs_dimensions * Very fast, but when used within the update query with the local tables a message 'Operation must use an updatable query' appears. Even though the records from the view are only used as a source to update a local table (also stupid, but true) 1 'Outsourcing' of the join between obs and obs_dimensions in a dedicated select query in Access. When used standalone (not within the update query), it is also very fast. However when used in the update query, performance goes back to MSSQL slow speed.

1. Separating the select from update query between obs and obs_dimensions with the same parameter as the update query ((obs_dimensions.art_clinic_obs_id)=[Forms]![art_clinic_obs_v8]![ID]). This appears to give Access the hint to first properly filter the obs and obs_dimensions join and then use the result for the update operation, hence back to Access-only fast execution time.

obs_set_tmptbl_de_update_BASE:

SELECT obs.ID, obs.obs_dimensions_ID, obs.data_element, obs.data_value, obs_dimensions.art_clinic_obs_id, obs_dimensions.period_report, obs_dimensions.sub_group, obs_dimensions.User, obs_dimensions.TimeStamp
FROM obs INNER JOIN obs_dimensions ON obs.obs_dimensions_ID = obs_dimensions.ID
WHERE (((obs_dimensions.art_clinic_obs_id)=[Forms]![art_clinic_obs_v8]![ID]));

obs_set_tmptbl_de_update:

UPDATE obs_dim_set_tmptbl INNER JOIN (obs_set_tmptbl INNER JOIN obs_set_tmptbl_de_update_BASE ON obs_set_tmptbl.data_element = obs_set_tmptbl_de_update_BASE.data_element) ON (obs_dim_set_tmptbl.ID = obs_set_tmptbl.obs_dim_set_tmptbl_ID) AND (obs_dim_set_tmptbl.sub_group = obs_set_tmptbl_de_update_BASE.sub_group) AND (obs_dim_set_tmptbl.period_report = obs_set_tmptbl_de_update_BASE.period_report) SET obs_set_tmptbl.data_value_tmp = obs.data_value
WHERE (((obs_set_tmptbl_de_update_BASE.art_clinic_obs_id)=[Forms]![art_clinic_obs_v8]![ID]));

Create new .accdb file and import every Access object from old .mdb.

VBA Code Queries

Add Reference to MS XML 6.0 from VBA - Tools - References

OpenRecordset used 18 times across 9 files (Form_psm_dist_round.cls, Form_psm_relo_item.cls, Form_psm_relo_transact.cls, Form_psm_relo_transact_item_review.cls, Form_psm_relocate.cls, Form_psm_relocate_relo.cls (???), Form_psm_ro_item_set.cls, Module1.bas, PubFunctions.bas).

Use MigrateCodeToMssql to change all (known) occurrences

Change code of Form_psm_relocate_relo.Command42_Click to "Set Rs = Qd.OpenRecordset(dbOpenDynaset, dbSeeChanges)"

Form_psm_relo_transact.Form_Activate and Form_psm_relocate_relo.Form_Load

Replace Line of GetLinkedDbName with If Not IsLiveDatabase() Then and remove PubFunctions.GetLinkedDbName (Function IsLiveDatabase is included in module RelinkTables).

Event doesn't seem to fire on old Access-only backend (even though it is there). Yet on the migrated frontend the existing procedure seems to be invoked again. Most likely in the old frontend the event AfterUpdate was removed on the form, but the procedure remained in the code. And now during migrating from .MDB to .ACCDB Access automatically reactivated this event.

This procedure recalculates the expiry_date, but apparently wrong (at least for the migrated DB). So it replaces the old entered value with a new invalid value blocking further processing.

For now the event procedure is renamed to expiry_date_AfterUpdate_deactivated to clearly indicate that it isn't used.

Private Sub expiry_date_AfterUpdate()
 If Not IsNull(Me.expiry_date) Then
    Me.expiry_date = DateAdd("m", 1, DateValue("1/" & DatePart("m", Me.expiry_date) & "/20" & DatePart("d", Me.expiry_date))) - 1
 Else
    Me.expiry_date = Null
 End If
End Sub

During migration from Access 2003 to Access 2016 the images for reports were not migrated. Manually adjust them.

Change ControlSource of Textbox LinkPath to =IIf(IsLiveDatabase()=True,"Live data","Offline data")

psm_dist_item_sum

SELECT psm_dist_batch.psm_dist_round_id, psm_dist_batch.supply_item_ID, supply_item_label.supply_item_label, psm_dist_item.psm_dist_batch_id, Sum(psm_dist_item.box_delivcollect) AS box_delivcollect_sum, First([units_tot])/First([units_box]) AS box_tot, [box_tot]-Sum([box_delivcollect]) AS box_diff, First(psm_dist_batch.units_box) AS units_bundle, supply_item.sort_weight
FROM (supply_item INNER JOIN (psm_dist_round INNER JOIN (psm_dist_batch INNER JOIN psm_dist_item ON psm_dist_batch.ID = psm_dist_item.psm_dist_batch_id) ON psm_dist_round.ID = psm_dist_batch.psm_dist_round_id) ON supply_item.ID = psm_dist_batch.supply_item_ID) INNER JOIN supply_item_label ON supply_item.ID = supply_item_label.ID
GROUP BY psm_dist_batch.psm_dist_round_id, psm_dist_batch.supply_item_ID, supply_item_label.supply_item_label, psm_dist_item.psm_dist_batch_id, supply_item.sort_weight
ORDER BY supply_item.sort_weight;

subfrm_psm_dist_item_sum replace combobox supply_item_ID with label supply_item_label and use new colun supply_item_label from above query as a control source

psm_dist_item_check

ORG

PARAMETERS [Enter distribution round number] Short;
SELECT psm_dist_item.psm_dist_batch_id, psm_dist_item.ref_year_quarter_id, code_hfacility.district, concept_district.concept_name AS district_name, StrConv([hfacility_name],3) AS hfacility, psm_dist_item.hdepartment_id_hub, psm_dist_batch.supply_item_ID, supply_item_label.supply_item_label, psm_dist_item.banned, psm_dist_item.units_endq, psm_dist_item.units_adjust, psm_dist_item.units_doom, psm_dist_item.consum_now, psm_dist_item.consum_grow, psm_dist_item.consum_min, psm_dist_item.box_delivcollect, IIf([consum_min]<[consum_now],[consum_now],[consum_min]) AS consum_now_min, DateAdd("d",(YieldMonth([consum_now_min],[consum_grow],[units_endq]+[units_adjust]+[box_delivcollect]*[units_box])*30.44),[quarter_stopdate]) AS stockout_date, [units_box]*[box_delivcollect] AS units_delivcollect, psm_dist_item.User, psm_dist_item.TimeStamp, psm_dist_item.UpdateUser, psm_dist_item.UpdateTimeStamp, psm_dist_round.dist_round, psm_dist_batch.psm_dist_round_id, psm_dist_item.Lock, psm_dist_item.LockDate
FROM (supply_item_label INNER JOIN (code_hfacility INNER JOIN (psm_dist_round INNER JOIN (code_hdepartment INNER JOIN (psm_dist_batch INNER JOIN (code_year_quarter INNER JOIN psm_dist_item ON code_year_quarter.ID = psm_dist_item.ref_year_quarter_id) ON psm_dist_batch.ID = psm_dist_item.psm_dist_batch_id) ON code_hdepartment.ID = psm_dist_item.hdepartment_id) ON psm_dist_round.ID = psm_dist_batch.psm_dist_round_id) ON code_hfacility.ID = code_hdepartment.hfacility_id) ON supply_item_label.ID = psm_dist_batch.supply_item_ID) INNER JOIN concept AS concept_district ON code_hfacility.district = concept_district.ID
WHERE (((psm_dist_round.dist_round)=[Enter distribution round number]));

adding supply_item_label, district_name; removing comboboxes district and supply_item_id

Generation of auth_key only happens once a new record is explicitly saved with enter. Maybe change to automatic generation when leaving the the field Main reason.

TODO: Verfiy why Auth Key generation takes ID + 10000 (in PubFunctions)

dynamic query, built with code from Form_report_qrybuild.rep_select_qry_Click

Show data works, export fails with ODBC error: Msg 1011, Level 16, State 1, Line 1 The correlation name 'concept_district' is specified multiple times in a FROM clause.

The generated SQL uses the same aliases multiple times (unsure why). Renaming the alias for concept_district and concept_zone to e.g. cd and cz solves the problem.

Sometimes Report invocation failed with 'Missing property'. In that case the query report_site_data couldn't be opened in design view anymore. Deleting the whole query and create an empty one solved this (this SQL query is generated on the fly anyways).

Rewritten method rep_select_qry_Click:

Private Sub rep_select_qry_Click()
   
   Dim Dbs As Database
   Dim QryNew As QueryDef, QryPivot As QueryDef
   Dim CriteriaGeo As String, CriteriaQtr As String, CriteriaPer As String, CriteriaSubgp As String
   Dim CriteriaGeoTxt As String, CriteriaQtrTxt As String, CriteriaPerTxt As String, CriteriaSubgpTxt As String
   Dim StrSQL As String, StrSQLPivot As String, StrRightSQL As String
   Dim WhereStr As String
   Dim PivotTransformStr, PivotSelectStr, PivotGroupStr, PivotGroupQtr As String
   Dim PivotGroupSubgp, PivotGroupPer, PivotGroupGeo, PivotGroupCriteria, PivotFromStr, PivotPivotStr, PivotInStr As String
   Dim CtlGeo As Control, CtlQtr As Control, CtlPer As Control, CtlSubgp As Control
   Dim Itm As Variant

' Build a list of the area selections.
Set CtlGeo = Me![select_geolist]

For Each Itm In CtlGeo.ItemsSelected
    If Len(CriteriaGeo) = 0 Then
        CriteriaGeo = CtlGeo.ItemData(Itm)
        CriteriaGeoTxt = CtlGeo.Column(1, Itm)
    Else
        CriteriaGeo = CriteriaGeo & "," & CtlGeo.ItemData(Itm)
        CriteriaGeoTxt = CriteriaGeoTxt & ", " & CtlGeo.Column(1, Itm)
    End If
Next Itm

If Len(CriteriaGeo) = 0 Then
    Itm = MsgBox("Select one or more areas from the list", 0, "Select Areas")
    Exit Sub
Else
    Me!geo_txt = CriteriaGeoTxt
End If
   
' Build a list of the year_quarter selections.
Set CtlQtr = Me![select_year_quarter]

For Each Itm In CtlQtr.ItemsSelected
    If Len(CriteriaQtr) = 0 Then
        CriteriaQtr = CtlQtr.ItemData(Itm)
        CriteriaQtrTxt = CtlQtr.Column(1, Itm)
    Else
        CriteriaQtr = CriteriaQtr & "," & CtlQtr.ItemData(Itm)
        CriteriaQtrTxt = CriteriaQtrTxt & ", " & CtlQtr.Column(1, Itm)
    End If
Next Itm

If Len(CriteriaQtr) = 0 Then
    Itm = MsgBox("Select one or more quarters from the list", 0, "Select Year and Quarter")
    Exit Sub
Else
    Me!qtr_txt = CriteriaQtrTxt
End If

' Build a list of the period selections.
Set CtlPer = Me![select_period]

For Each Itm In CtlPer.ItemsSelected
    If Len(CriteriaPer) = 0 Then
        CriteriaPer = CtlPer.ItemData(Itm)
        CriteriaPerTxt = CtlPer.Column(1, Itm)
    Else
        CriteriaPer = CriteriaPer & "," & CtlPer.ItemData(Itm)
        CriteriaPerTxt = CriteriaPerTxt & ", " & CtlPer.Column(1, Itm)
    End If
Next Itm

If Len(CriteriaPer) = 0 Then
    Itm = MsgBox("Select one or more periods from the list", 0, "Select Registration Period")
    Exit Sub
Else
    Me!per_txt = CriteriaPerTxt
End If

' Build a list of the subgroup selections.
Set CtlSubgp = Me![select_subgroup]

For Each Itm In CtlSubgp.ItemsSelected
    If Len(CriteriaSubgp) = 0 Then
        CriteriaSubgp = CtlSubgp.ItemData(Itm)
        CriteriaSubgpTxt = CtlSubgp.Column(1, Itm)
    Else
        CriteriaSubgp = CriteriaSubgp & "," & CtlSubgp.ItemData(Itm)
        CriteriaSubgpTxt = CriteriaSubgpTxt & ", " & CtlSubgp.Column(1, Itm)
    End If
Next Itm

If Len(CriteriaSubgp) = 0 Then
    Itm = MsgBox("Select one or more sub groups from the list", 0, "Select Sub Groups")
    Exit Sub
Else
    Me!subgp_txt = CriteriaSubgpTxt
End If

'Concatenate the WHERE statement used in the QUERYDEF

   WhereStr = " WHERE (((report_select_obs_dimensions.[" & Me!select_geolevel & "]) In (" & CriteriaGeo & _
   ")) AND ((report_select_obs_dimensions.year_quarter_id) In (" & CriteriaQtr & _
   ")) AND ((report_select_obs_dimensions.period_report) In (" & CriteriaPer & _
   ")) AND ((report_select_obs_dimensions.sub_group) In (" & CriteriaSubgp & ")));"
   

    Set Dbs = CurrentDb()
    Set QryNew = Dbs.QueryDefs("report_select_obs_dimensions_ID")
    QryNew.sql = "SELECT art_clinic_obs_id, obs_dimensions_ID, period_report, sub_group " & _
                 "FROM report_select_obs_dimensions" & WhereStr
   
'Set prog_value control to the 2nd column of the select_program control
'(needed as parameter in following append query)

    Me.prog_value = Me.select_program.Column(2)

'Clear and append to temp tabl the list of data elements that belong to the selected program report
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("report_levels_del")
    DoCmd.OpenQuery ("report_levels_app")
    DoCmd.OpenQuery ("obs_calc_del")
    DoCmd.OpenQuery ("report_select_obs_dimensions_ID_obs")
    DoCmd.SetWarnings True
    
If Me.FrameRepTyp = 1 Then
    ' Call the public function that calculates and appends to the obs_calc temp table
    ' the calculated data elements
    ' according to the calculation rules defined by concept sets
    
    CalculateDE
    
    ' Open report
    
    DoCmd.OpenReport "report_levels_generic", acViewPreview

Else
    
    'Populate the group criteria from the option group control selected on the report_qrybuild form
    
    If Me.FrameQtr = 1 Then
        PivotGroupQtr = ", year_quarter_id"
    Else
        PivotGroupQtr = ""
    End If
    
    If Me.FrameSubgp = 1 Then
        PivotGroupSubgp = ", sub_group"
    Else
        PivotGroupSubgp = ""
    End If
    
    If Me.FramePer = 1 Then
        PivotGroupPer = ", period_report"
    Else
        PivotGroupPer = ""
    End If
    
    Select Case Me.FrameGeo
            
        Case 1 'User selected Site level aggregation
            PivotSelectStr = "SELECT cz.concept_ID_parent AS Region, cd.concept_ID_parent AS Zone, " & _
                            "code_hfacility.district AS District, art_clinic_obs.hdepartment_id, code_hdepartment.hsector"
            PivotGroupGeo = "GROUP BY cz.concept_ID_parent, cd.concept_ID_parent, " & _
                            "code_hfacility.district, art_clinic_obs.hdepartment_id, code_hdepartment.hsector"
        
        Case 2 'User selected District aggregation
            PivotSelectStr = "SELECT cz.concept_ID_parent AS Region, cd.concept_ID_parent AS Zone, " & _
                            "code_hfacility.district AS District"
            PivotGroupGeo = "GROUP BY cz.concept_ID_parent, cd.concept_ID_parent, " & _
                            "code_hfacility.district"
        
        Case 3 'User selected Zonal aggregation
            PivotSelectStr = "SELECT cz.concept_ID_parent AS Region, cd.concept_ID_parent AS Zone"
            PivotGroupGeo = "GROUP BY cz.concept_ID_parent, cd.concept_ID_parent"
        
        
        Case 4 'User selected Regional aggregation
            PivotSelectStr = "SELECT concept_region.concept_ID_parent AS Nation, cz.concept_ID_parent AS Region"
            PivotGroupGeo = "GROUP BY concept_region.concept_ID_parent, cz.concept_ID_parent"
        
        Case 5 'User selected National aggregation
            PivotSelectStr = "SELECT concept_region.concept_ID_parent AS Nation"
            PivotGroupGeo = "GROUP BY concept_region.concept_ID_parent"
            
    End Select
    
    PivotGroupStr = PivotGroupGeo & PivotGroupQtr & PivotGroupPer & PivotGroupSubgp
    
    'Define the elements of the Pivot SQL string
    
    PivotTransformStr = "TRANSFORM Sum(obs.data_value) AS SumOfdata_value"
    PivotSelectStr = PivotSelectStr & PivotGroupQtr & PivotGroupPer & PivotGroupSubgp
    PivotFromStr = "FROM (concept AS concept_data_element INNER JOIN (((code_hfacility INNER JOIN concept AS cd ON " & _
                    "code_hfacility.district = cd.ID) INNER JOIN concept AS cz ON " & _
                    "cd.concept_ID_parent = cz.ID) INNER JOIN (code_hdepartment INNER JOIN " & _
                    "(((report_select_obs_dimensions_ID INNER JOIN art_clinic_obs ON report_select_obs_dimensions_ID.art_clinic_obs_id = " & _
                    "art_clinic_obs.ID) INNER JOIN obs ON report_select_obs_dimensions_ID.obs_dimensions_ID = " & _
                    "obs.obs_dimensions_ID) INNER JOIN report_levels ON obs.data_element = report_levels.data_element) ON " & _
                    "code_hdepartment.ID = art_clinic_obs.hdepartment_id) ON code_hfacility.ID = code_hdepartment.hfacility_id) ON " & _
                    "concept_data_element.ID = obs.data_element) INNER JOIN concept AS concept_region ON " & _
                    "cz.concept_ID_parent = concept_region.ID"
   ' PivotFromStr = "FROM (concept AS concept_data_element INNER JOIN (((code_hfacility INNER JOIN concept AS concept_district ON " & _
  '                  "code_hfacility.district = concept_district.ID) INNER JOIN concept AS concept_zone ON " & _
 '                   "concept_district.concept_ID_parent = concept_zone.ID) INNER JOIN (code_hdepartment INNER JOIN " & _
'                    "(((report_select_obs_dimensions_ID INNER JOIN art_clinic_obs ON report_select_obs_dimensions_ID.art_clinic_obs_id = " & _
    '                "art_clinic_obs.ID) INNER JOIN obs ON report_select_obs_dimensions_ID.obs_dimensions_ID = " & _
   '                 "obs.obs_dimensions_ID) INNER JOIN report_levels ON obs.data_element = report_levels.data_element) ON " & _
  '                  "code_hdepartment.ID = art_clinic_obs.hdepartment_id) ON code_hfacility.ID = code_hdepartment.hfacility_id) ON " & _
 '                   "concept_data_element.ID = obs.data_element) INNER JOIN concept AS concept_region ON " & _
'                    "concept_zone.concept_ID_parent = concept_region.ID"
    PivotPivotStr = "PIVOT concept_data_element.concept_name"
    PivotInStr = "IN (" & XTabColHead & ");"
    
'    Debug.Print PivotInStr
'    Stop
    
    Set Dbs = CurrentDb
    StrSQLPivot = PivotTransformStr & Chr(10) & PivotSelectStr & Chr(10) & PivotFromStr & Chr(10) & _
                  PivotGroupStr & Chr(10) & PivotPivotStr & Chr(10) & PivotInStr
 '   Debug.Print StrSQLPivot
'    Stop
    'Set QryPivot = Dbs.QueryDefs("report_site_data")
    'QryPivot.sql = StrSQLPivot
    On Error Resume Next
    Dbs.QueryDefs.Delete "report_site_data_temp"
    On Error GoTo 0
    Dim qdf As QueryDef
    
    Set qdf = Dbs.CreateQueryDef("report_site_data_temp", StrSQLPivot)

    DoCmd.OpenQuery ("report_site_data_temp")

End If
End Sub

Error: You tried to assign NULL value to a variable that is not a Variant data type

Add Nz(code_hdepartment.hdepartment_hub_id, 0); (maybe also consider changing column definition of psm_dist_item.hdepartment)id_hub to NOT NULL?)

PARAMETERS [Forms]![psm_dist_round].[ID] Long; UPDATE psm_dist_round INNER JOIN (code_hdepartment INNER JOIN (psm_dist_batch INNER JOIN psm_dist_item ON psm_dist_batch.ID = psm_dist_item.psm_dist_batch_id) ON code_hdepartment.ID = psm_dist_item.hdepartment_id) ON psm_dist_round.ID = psm_dist_batch.psm_dist_round_id SET psm_dist_item.hdepartment_id_hub = nz(code_hdepartment.hdepartment_hub_id, 0) WHERE (((psm_dist_round.ID)=Forms!psm_dist_round.ID) And ((psm_dist_item.consum_now)=0) And ((code_hdepartment.hservice_paed)=False) And ((psm_dist_batch.supply_item_ID) In (35,39,40,43,44,45,46,52)) And ((code_hdepartment.hservice)=294));

Lookup columns don't work. Recreate query by copy/pasting SQL and then adjust formats and captions manually. But don not change the Region, Zone, and Site captions as this breaks their lookup columns again.

Button Review quantification rules of Stock management invokes map_regimen_supply_openfrm_Click. This calls the query psm_rule_dim_de_list_tbl (which is created by psm_rule_dim_de_list_mktbl) and then loads the various elements of the form psm_rule_supply_batch. E.g. query psm_rule_supply_batch and supply_item_label (for combobox supply item id).

Order by (Form properties) Lookup_supply__item__ID.supply_item_label somehow (?) refers to Combobox supply_item_id and its underlying query supply_item_label. This links the label concept multiple times together and Access seems to combine this query with the underlying rowsource query psm_rule_supply_batch. This query combination leads to a couple of 'multi-part identifiers could not be bound' error (see below for more details).

TODO and TO CHECK: For now the order by expression is simply removed as it is unclear anyways what it is supposed to do.

Maybe a possible solution is to outsource supply_item)label to a MSSQL view.

supply_item_label_base (MSSQL version):

SELECT supply_item.ID, concat(concept.concept_name, ' ', [strength], ' ', concept_3.concept_name, ' (', [pack_size], ' ', concept_2.concept_name, ')') AS supply_item_label, concept_3.concept_name AS pack_type_label, concept_4.concept_name AS inventory_unit_label, supply_item.paed_form
FROM concept INNER JOIN ((((supply_item INNER JOIN concept AS concept_1 ON supply_item.presentation = concept_1.ID) INNER JOIN concept AS concept_2 ON supply_item.pack_unit = concept_2.ID) INNER JOIN concept AS concept_3 ON supply_item.pack_type = concept_3.ID) INNER JOIN concept AS concept_4 ON supply_item.inventory_unit = concept_4.ID) ON concept.ID = supply_item.item
ORDER BY concat(concept.concept_name, ' ', [strength], ' ', concept_3.concept_name, ' (', [pack_size], ' ', concept_2.concept_name, ')'); 

supply_item_label:

SELECT supply_item_label_base.ID, supply_item_label_base.supply_item_label, supply_item_label_base.pack_type_label, supply_item_label_base.inventory_unit_label, supply_item_label_base.paed_form
FROM supply_item_label_base
ORDER BY supply_item_label_base.supply_item_label;

For reference purpose this is the query send the the server:

SELECT "dbo"."psm_dist_round"."ID","dbo"."psm_dist_batch"."ID","dbo"."map_regimen_supply"."ID","concept_3"."concept_name" ,
"dbo"."concept"."concept_name" ,"dbo"."supply_item"."strength" ,"dbo"."supply_item"."pack_size" ,
"concept_2"."concept_name" ,"dbo"."concept"."ID","dbo"."supply_item"."ID","concept_1"."ID","concept_2"."ID",
"concept_3"."ID","concept_4"."ID" 
FROM "dbo"."psm_dist_round","dbo"."psm_dist_batch","dbo"."concept",
"dbo"."concept" "concept_1","dbo"."concept" "concept_2","dbo"."concept" "concept_3","dbo"."concept" "concept_4",
{oj "dbo"."map_regimen_supply" 
LEFT OUTER JOIN "dbo"."supply_item" ON (("dbo"."map_regimen_supply"."supply_item_ID" = "dbo"."supply_item"."ID" ) 
AND ((((("dbo"."concept"."ID" = "dbo"."supply_item"."item" ) AND ("dbo"."supply_item"."presentation" = "concept_1"."ID" ) ) 
AND ("dbo"."supply_item"."pack_unit" = "concept_2"."ID" ) ) AND ("dbo"."supply_item"."pack_type" = "concept_3"."ID" ) ) 
AND ("dbo"."supply_item"."inventory_unit" = "concept_4"."ID" ) ) ) }
WHERE ((("dbo"."psm_dist_round"."dist_round" =  ? ) 
AND ("dbo"."psm_dist_round"."ID" = "dbo"."psm_dist_batch"."psm_dist_round_id" ) ) 
AND ("dbo"."psm_dist_batch"."supply_item_ID" = "dbo"."map_regimen_supply"."supply_item_ID" ) )

And these are the errors messages from MSSQL:

DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "dbo.concept.ID" could not be bound. (4104) 
DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "concept_1.ID" could not be bound. (4104) 
DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "concept_2.ID" could not be bound. (4104) 
DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "concept_3.ID" could not be bound. (4104) 
DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "concept_4.ID" could not be bound. (4104) 

ODBC failure, because of literal 1 in GROUP BY. Remove reference to it as it is a static value already available in SELECT clause.

PARAMETERS [Enter year] Short, [Enter quarter] Short;
TRANSFORM Sum(obs.data_value) AS data_value
SELECT 1 AS [section], art_clinic_obs.year_quarter_id
FROM code_year_quarter, art_clinic_obs INNER JOIN (obs_dimensions INNER JOIN obs ON obs_dimensions.ID = obs.obs_dimensions_ID) ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id
WHERE (((obs.data_element) In (94,175,176,178,179,434,435,1034,1037,1036,1039)) AND ((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]) AND ((obs_dimensions.period_report)=393) AND ((obs_dimensions.sub_group)=398) AND ((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[ID]) AND ((art_clinic_obs.year_quarter_id) >= [code_year_quarter].[ID]-3))
GROUP BY art_clinic_obs.year_quarter_id
ORDER BY art_clinic_obs.year_quarter_id
PIVOT obs.data_element In (94,175,176,178,179,434,435,1034,1036,1037,1039);

ODBC error, because of Literals/Access-Functions in GROUP BY clause. Doesn't seem to make much sense there anyways, and by removing Now(), CurrentUser() it works.

GROUP BY psm_dist_round.ID, psm_dist_item.hdepartment_id_hub, Now(), CurrentUser()

Multiple queries with at least one outer join. the problematic is link is between art_sched_date and code_hdepartment_tb_bmu which itself is a query containing code_hdepartment and code_hfacility. SQL generated by for MSSQL is invalid (Msg 4104 The multi-part identifier ... could not be bound). Discovered by using SQL Profiler to check which SQL statement was send to MSSQL and manually running it in SSMS.

Solution: Make code_hdepartment_tb_bmu a SQL passthrough query.

SELECT code_hfacility.hfacility_name, code_hdepartment.ID AS hdepartment_ID, code_hdepartment.hfacility_id
FROM code_hfacility INNER JOIN code_hdepartment ON code_hfacility.ID = code_hdepartment.hfacility_id
WHERE (((code_hdepartment.hservice)=299) AND ((code_hdepartment.Voided)=0) AND ((code_hfacility.Voided)=0));

• used in psm_site_stock_consumpt_adjust_allARV
• very slow, but this subquery often (always?) doesn't even run with old Access frontend
• Aggregate functions with art_drug_stocks are problematic

Solution: extract sub-query without art_drug_stocks and join this new query to art_drug_Stocks. Afterwards ensure/verify that psm_site_stock_consumpt_adjust_allARV uses this new query.

SQL for psm_site_stock_consumpt_allARV_base

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT art_clinic_obs.year_quarter_id, code_hfacility.district, art_clinic_obs.ID AS art_clinic_obs_ID, art_clinic_obs.hdepartment_id, map_regimen_supply.supply_item_ID, obs.data_value, map_regimen_supply_rule.unit_month, art_clinic_obs.visit_date AS stock_date
FROM (concept AS concept_1 INNER JOIN supply_item ON concept_1.ID = supply_item.item) INNER JOIN (code_hfacility INNER JOIN (code_hdepartment INNER JOIN ((code_year_quarter INNER JOIN art_clinic_obs ON code_year_quarter.ID = art_clinic_obs.year_quarter_id) INNER JOIN (map_regimen_supply INNER JOIN (obs_dimensions INNER JOIN (obs INNER JOIN map_regimen_supply_rule ON obs.data_element = map_regimen_supply_rule.patient_regimen) ON (obs_dimensions.period_report = map_regimen_supply_rule.period_report) AND (obs_dimensions.ID = obs.obs_dimensions_ID)) ON map_regimen_supply.ID = map_regimen_supply_rule.map_regimen_supply_ID) ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id) ON code_hdepartment.ID = art_clinic_obs.hdepartment_id) ON code_hfacility.ID = code_hdepartment.hfacility_id) ON supply_item.ID = map_regimen_supply.supply_item_ID
WHERE (((map_regimen_supply_rule.default_rule)=True) AND ((obs_dimensions.sub_group)=398 Or (obs_dimensions.sub_group)=948 Or (obs_dimensions.sub_group)=949) AND ((concept_1.concept_ID_parent)=34) AND ((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]));

psm_site_stock_consumpt_allARV

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT psm_site_stock_consumpt_allARV_base.year_quarter_id, psm_site_stock_consumpt_allARV_base.district, psm_site_stock_consumpt_allARV_base.art_clinic_obs_ID, psm_site_stock_consumpt_allARV_base.hdepartment_id, art_drug_stocks.supply_item_ID, Sum(-Int(-[unit_month]*[data_value])) AS units_consum_month, psm_site_stock_consumpt_allARV_base.stock_date, First(art_drug_stocks.units_instock) AS units_SOH, art_drug_stocks.expiry_date_min, First(art_drug_stocks.units_exp6m) AS units_SOH_exp6m
FROM psm_site_stock_consumpt_allARV_base INNER JOIN art_drug_stocks ON (psm_site_stock_consumpt_allARV_base.art_clinic_obs_ID = art_drug_stocks.art_clinic_obs_id) AND (psm_site_stock_consumpt_allARV_base.supply_item_ID = art_drug_stocks.supply_item_ID)
GROUP BY psm_site_stock_consumpt_allARV_base.year_quarter_id, psm_site_stock_consumpt_allARV_base.district, psm_site_stock_consumpt_allARV_base.art_clinic_obs_ID, psm_site_stock_consumpt_allARV_base.hdepartment_id, art_drug_stocks.supply_item_ID, psm_site_stock_consumpt_allARV_base.stock_date, art_drug_stocks.expiry_date_min;

Above query with these Field properties:

• Caption art_clinic_obs_id: Form ID
• Caption supply_item_id: Product
• units_consum_monthly: Format #,##0 , Caption Monthly consumption
• units_SOH: units_instock: Format #,##0, Decimal Places 0, Caption SOH
• units_SOH_exp6m: units_exp6m: Format #,##0, Decimal Places 0, Caption SOH_exp6m

psm_site_stock_consumpt_adjust_allARV

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT psm_site_stock_consumpt_allARV.*, psm_relocate_adjust_rep_allARV.adjust_units_net, psm_relocate_adjust_rep_allARV.[Ad-hoc allocation from warehouse], psm_relocate_adjust_rep_allARV.Disposal, psm_relocate_adjust_rep_allARV.[Relocation between sites], psm_relocate_adjust_rep_allARV.[Scheduled distribution from warehouse], Nz([units_SOH],0)+Nz([adjust_units_net],0) AS net_balance, IIf(Nz([units_consum_month],0)>0,([net_balance]/[units_consum_month]),0) AS MoS
FROM psm_site_stock_consumpt_allARV LEFT JOIN psm_relocate_adjust_rep_allARV ON (psm_site_stock_consumpt_allARV.art_clinic_obs_ID = psm_relocate_adjust_rep_allARV.art_clinic_obs_id) AND (psm_site_stock_consumpt_allARV.supply_item_ID = psm_relocate_adjust_rep_allARV.supply_item_id);

e.g. used in comboboxes for ad-hoc relocation

joins between local and linked tables and complex sitename calc

introduce underlying sql passthrough query psm_relo_site_base

SELECT code_hdepartment.ID, code_hdepartment.hfacility_id
, concat([hfacility_name], ' (', [name_healthdept],concept_hservice.concept_name, '), ', concept_district.concept_name) AS SiteName
, code_hdepartment.hservice
, IIf(code_hfacility.voided=1 Or code_hdepartment.voided=1,-1,IIf((code_hdepartment.service_end is not null),0,1)) AS active
FROM (concept AS concept_district INNER JOIN code_hfacility ON concept_district.ID = code_hfacility.district) INNER JOIN (code_hdepartment INNER JOIN concept AS concept_hservice ON code_hdepartment.hservice = concept_hservice.ID) ON code_hfacility.ID = code_hdepartment.hfacility_id
ORDER BY[hfacility_name]

change psm_relo_site to

SELECT psm_relo_site_base.ID, psm_relo_site_base.SiteName, psm_relo_site_base.hservice, psm_relo_site_base.active
FROM psm_relo_site_base INNER JOIN psm_relo_site_service_min ON (psm_relo_site_base.hservice = psm_relo_site_service_min.hservice_min) AND (psm_relo_site_base.hfacility_id = psm_relo_site_service_min.hfacility_id)
ORDER BY psm_relo_site_base.SiteName;

Between statement of crosstab a problem, replace with >= and <=

PARAMETERS [Enter year] Short, [Enter quarter] Short;
TRANSFORM Sum(obs.data_value) AS data_value_sum
SELECT art_clinic_obs.year_quarter_id
FROM code_year_quarter, (art_clinic_obs INNER JOIN obs_dimensions ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id) INNER JOIN obs ON obs_dimensions.ID = obs.obs_dimensions_ID
WHERE (((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]) AND ((obs_dimensions.period_report)=394) AND ((obs_dimensions.sub_group)=398) AND ((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[id] And (art_clinic_obs.year_quarter_id) >= [code_year_quarter].[id]-4) AND ((obs.data_element)=104 Or (obs.data_element)=96))
GROUP BY art_clinic_obs.year_quarter_id
PIVOT obs.data_element In (96,104);

• Replace Between function with <= and >=
• MS Access - Scaling of decimal value resulted in data truncation. Error 3759; add a Cdbl (ConvertDouble)

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT art_clinic_obs.year_quarter_id, [year_quarter_id]-1 AS year_quarter_prev, Sum(obs.data_value) AS alive_regimen_paed, 1/(-0.0049*(CDbl(art_clinic_obs.year_quarter_id)-37)+0.7053) AS age_0_14_factor, Sum(obs.data_value/(-0.0049*(art_clinic_obs.year_quarter_id-37)+0.7053)) AS age_0_14_raw
FROM code_year_quarter, (art_clinic_obs INNER JOIN obs_dimensions ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id) INNER JOIN (concept INNER JOIN obs ON concept.ID = obs.data_element) ON obs_dimensions.ID = obs.obs_dimensions_ID
WHERE (((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]) AND ((obs_dimensions.period_report)=394) AND ((obs_dimensions.sub_group)=398) AND ((concept.concept_ID_parent)=743 Or (concept.concept_ID_parent)=745) AND ((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[id] And art_clinic_obs.year_quarter_id >= [code_year_quarter].[id]-3))
GROUP BY art_clinic_obs.year_quarter_id, 1/(-0.0049*(art_clinic_obs.year_quarter_id-37)+0.7053);

Between Statement of art_clinic_obs.year_quarter_id doesn't work with MSSQL; rewrite condition to explicitly give upper and lower limits.

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT art_clinic_obs.hdepartment_id, obs.data_element, obs.data_value, art_clinic_obs.year_quarter_id, code_year_quarter.ID AS year_quarter_id_now
FROM code_year_quarter, (art_clinic_obs INNER JOIN obs_dimensions ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id) INNER JOIN obs ON obs_dimensions.ID = obs.obs_dimensions_ID
WHERE (((obs.data_element) In (97,102,103,104)) AND ((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[id]) AND ((art_clinic_obs.year_quarter_id) >= ([code_year_quarter].[id]-8)) AND ((obs_dimensions.period_report)=394) AND ((obs_dimensions.sub_group)=398) AND ((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]));

• Heterogeneous join between local table map_survival_cohost and remote tables art_clinic_obs, obs_dimensions, code_year_quarter
• Local map table has approx 7500 records. Appears as if for every record a query to art_clinic_obs (or obs_dimensions) is invoked. So no 'bulk query' happens, whereas with the Access the filter on art_clinic_obs is immediately available. (https://support.microsoft.com/en-us/help/286222/how-to-optimize-microsoft-access-when-using-odbc-data-sources)
• Looks as if the join between art_clinic_obs and obs_dimensions together with map_survival_cohort slows it down. Removing the join between art_clinic_obs speeds up the query. There introduce a base join without obs_dimensions and later then link it in.

chk_surv_de_reg_base

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT art_clinic_obs.ID AS Form_ID_prev, map_survival_cohort.year_quarter_id_reg, art_clinic_obs.hdepartment_id, map_survival_cohort.sub_group, map_survival_cohort.data_element_reg, map_survival_cohort.data_element_surv
FROM code_year_quarter INNER JOIN (art_clinic_obs INNER JOIN map_survival_cohort ON art_clinic_obs.year_quarter_id = map_survival_cohort.year_quarter_id_reg) ON code_year_quarter.ID = map_survival_cohort.year_quarter_id
WHERE (((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter])) ORDER BY art_clinic_obs.ID;

chk_surv_de_reg

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT chk_surv_de_reg_base.Form_ID_prev, map_survival_cohort.year_quarter_id_reg, chk_surv_de_reg_base.hdepartment_id, obs_dimensions.ID AS obs_dimensions_id, chk_surv_de_reg_base.sub_group,chk_surv_de_reg_base.data_element_reg, chk_surv_de_reg_base.data_element_surv
FROM obs_dimensions INNER JOIN chk_surv_de_reg_base ON obs_dimensions.art_clinic_obs_id = chk_surv_de_reg_base.Form_ID_prev
WHERE (((obs_dimensions.period_report)=393) AND ((obs_dimensions.sub_group)=398));

between condition, rewrite to

PARAMETERS [Enter year] Short, [Enter quarter] Short;
TRANSFORM Sum(obs.data_value) AS data_value
SELECT 1 AS [section], art_clinic_obs.year_quarter_id
FROM code_year_quarter, art_clinic_obs INNER JOIN (obs_dimensions INNER JOIN obs ON obs_dimensions.ID = obs.obs_dimensions_ID) ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id WHERE (((obs.data_element) In (94,175,176,178,179,434,435,1034,1037,1036,1039)) AND ((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]) AND ((obs_dimensions.period_report)=393) AND ((obs_dimensions.sub_group)=398) AND ((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[ID]) AND ((art_clinic_obs.year_quarter_id) >= [code_year_quarter].[ID]-3))
GROUP BY 1, art_clinic_obs.year_quarter_id
ORDER BY art_clinic_obs.year_quarter_id
PIVOT obs.data_element In (94,175,176,178,179,434,435,1034,1036,1037,1039);

Problem: select count(*) in field list of SELECT

Replace both (DateAdd("h"... occurrences with (DateAdd("HH"... and switch to SQL Passthrough query. Make sure to save ODBC connection under Properties.

TODO: Column headers and value lookups for art_sched_set_rank.hdepartment_id, art_sched_set_rank.year_quarter_ID

SELECT art_sched_set_rank.hdepartment_id, art_sched_set_rank.year_quarter_ID, art_sched_set_rank.team, art_sched_set_rank.sched_date, art_sched_set_rank.sched_seq, 1+(SELECT count(*) FROM art_sched_set WHERE art_sched_set.year_quarter_ID = art_sched_set_rank.year_quarter_ID AND art_sched_set.team = art_sched_set_rank.team AND (DateAdd("HH",art_sched_set.sched_seq, art_sched_set.sched_date)) < (DateAdd("HH",art_sched_set_rank.sched_seq, art_sched_set_rank.sched_date))) AS rank
FROM art_sched_set AS art_sched_set_rank
ORDER BY art_sched_set_rank.year_quarter_ID, art_sched_set_rank.team, art_sched_set_rank.sched_date, art_sched_set_rank.sched_seq;

Problem: Between condition of art_clinic_obs.year_quarter_id doesn't work with MSSQL, rewrite query to:

PARAMETERS [Enter year] Short, [Enter quarter] Short;
TRANSFORM Sum(obs.data_value) AS SumOfdata_value
SELECT art_clinic_obs.year_quarter_id, obs_dimensions.period_report, Sum(obs.data_value) AS [Total ART registrations]
FROM code_year_quarter, ((concept AS concept_hsector INNER JOIN code_hdepartment ON concept_hsector.ID = code_hdepartment.hsector) INNER JOIN (art_clinic_obs INNER JOIN obs_dimensions ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id) ON code_hdepartment.ID = art_clinic_obs.hdepartment_id) INNER JOIN obs ON obs_dimensions.ID = obs.obs_dimensions_ID
WHERE (((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]) AND (((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[ID] AND art_clinic_obs.year_quarter_id >= [code_year_quarter].[ID]-3)) AND ((obs_dimensions.period_report)=394 Or (obs_dimensions.period_report)=393) AND ((obs_dimensions.sub_group)=398) AND ((obs.data_element)=5))
GROUP BY art_clinic_obs.year_quarter_id, obs_dimensions.period_report
PIVOT [concept_name] & " sites ART registrations" In ("Public sites ART registrations","Private sites ART registrations");

WHERE condition: ((art_clinic_obs.year_quarter_id) Between [code_year_quarter].[ID] And [code_year_quarter].[ID]-3) AND

PARAMETERS [Enter year] Short, [Enter quarter] Short;
TRANSFORM Sum(IIf([worked]=1,1,0)) AS Expr2
SELECT concept_set_1.concept_ID_member
FROM code_year_quarter, ((art_person INNER JOIN concept_set ON art_person.Qualification = concept_set.concept_ID_member) INNER JOIN concept_set AS concept_set_1 ON concept_set.concept_ID_set = concept_set_1.concept_ID_member) INNER JOIN ((art_clinic_obs INNER JOIN code_year_quarter AS code_yq ON art_clinic_obs.year_quarter_id = code_yq.ID) INNER JOIN art_staff_obs ON art_clinic_obs.ID = art_staff_obs.art_clinic_obs_id) ON art_person.ID = art_staff_obs.art_person_id
WHERE (((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[ID]) AND ((art_clinic_obs.year_quarter_id) >= [code_year_quarter].[ID]-3) AND ((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]) AND ((concept_set_1.concept_ID_set)=1625))
GROUP BY concept_set_1.concept_ID_member
PIVOT code_yq.Year & " Q" & code_yq.Quarter;

Empty, Problematic WHERE condition: ((art_clinic_obs.year_quarter_id) Between [code_year_quarter].[ID]-1 And [code_year_quarter].[ID]-4) AND

PARAMETERS [Enter year] Short, [Enter quarter] Byte;
TRANSFORM First(obs.data_value) AS FirstOfdata_value
SELECT art_clinic_obs.year_quarter_id, art_clinic_obs.hdepartment_id
FROM code_year_quarter, art_clinic_obs INNER JOIN (obs_dimensions INNER JOIN obs ON obs_dimensions.ID = obs.obs_dimensions_ID) ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id
WHERE (((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]) AND ((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[ID]-1) AND ((art_clinic_obs.year_quarter_id) >= [code_year_quarter].[ID]-4) AND ((obs_dimensions.period_report)=393) AND ((obs_dimensions.sub_group)=398) AND ((obs.data_element) Between 175 And 179))
GROUP BY art_clinic_obs.year_quarter_id, art_clinic_obs.hdepartment_id
PIVOT obs.data_element In (175,176,178,179);

empty, problematic WHERE condition: ((art_clinic_obs.year_quarter_id) Between [code_year_quarter].[id] And [code_year_quarter].[id]-9) AND

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT art_clinic_obs.year_quarter_ID-code_year_quarter.ID AS year_quarter_id_diff, art_clinic_obs.year_quarter_id, art_clinic_obs.hdepartment_id, obs.data_element, obs.data_value
FROM code_year_quarter, art_clinic_obs INNER JOIN (obs_dimensions INNER JOIN obs ON obs_dimensions.ID = obs.obs_dimensions_ID) ON art_clinic_obs.ID = obs_dimensions.art_clinic_obs_id
WHERE (((art_clinic_obs.year_quarter_id) <= [code_year_quarter].[id]) AND ((art_clinic_obs.year_quarter_id) >= [code_year_quarter].[id]-9) AND ((obs.data_element)=57 Or (obs.data_element)=66) AND ((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter]) AND ((obs_dimensions.period_report)=393) AND ((obs_dimensions.sub_group)=398));

Aggregate count for ranking is expensive. Making a SQL passthrough query or moving the query as it is to a MSSQL view solves this.

Simply change Access query to SQL Passthrough query and make sure, the ODBC connection is stored under Query Properties.

Alternatively: As dedicated MSSQL view view_art_staff_rank:

SELECT        hdepartment_id, art_person_id,
                             (SELECT        COUNT(*) AS Expr1
                               FROM            dbo.art_staff
                               WHERE        (hdepartment_id = art_staff_rank.hdepartment_id) AND (ID < art_staff_rank.ID)) AS rank
FROM            dbo.art_staff AS art_staff_rank

Access query art_site_staff depends on art_staff_rank, therefore this also needs to point to linked MSSQL view.

TRANSFORM First(IIf(Not IsNull([art_person_ID]),[NameFirst] & " " & [NameLast] & ", " & [Position] & " (" & [Phone] & ")","")) AS staff_name
SELECT code_hfacility.district, code_hdepartment_name.Site, code_hfacility.hfactype, code_hdepartment.hauthority, code_hdepartment.hsector, code_hfacility.gps_x, code_hfacility.gps_y, code_hdepartment.service_start
FROM code_hfacility INNER JOIN (((code_hdepartment INNER JOIN code_hdepartment_name ON code_hdepartment.ID = code_hdepartment_name.ID) LEFT JOIN view_art_staff_rank ON code_hdepartment.ID = view_art_staff_rank.hdepartment_id) LEFT JOIN art_person ON view_art_staff_rank.art_person_id = art_person.ID) ON code_hfacility.ID = code_hdepartment.hfacility_id
WHERE (((code_hfacility.Voided)=False) AND ((code_hdepartment.Voided)=False) AND ((code_hdepartment.service_end) Is Null) AND ((code_hdepartment.hservice)=294) AND ((code_hdepartment.hservmode)=309))
GROUP BY code_hfacility.district, code_hdepartment_name.Site, code_hfacility.hfactype, code_hdepartment.hauthority, code_hdepartment.hsector, code_hfacility.gps_x, code_hfacility.gps_y, code_hdepartment.service_start
PIVOT [rank]+1 In (1,2,3,4,5);

TO BE VERIFIED: art_reg_cum_now_valid might have differences. In old test data set 773 records are retrieved for Q4/2016 while in new datase only 731 and for art_reg_cum_outc_valid 732 vs 731 records. Unclear if queries produce different results or the dataset are slightly different (potentially the case due to test data entry by clerks).

Part of 6. Data Cleaning - 3. Data consistency. Some of the subqueries (e.g. art_reg_cum_now_calc, art_quart_reg_prev_now, art_reg_cum_now_valid, art_reg_cum_prev_valid) fail (see graph for dependency hierarchy). And deeper nested failing queries'propagate' upwards.

For example art_quart_reg_prev_now with this ODBC error: many The multi-part identifier "MS1.period_report" could not be bound. discovered with ODBC Tracing

RIGHT/LEFT JOIN vs. INNER JOIN: Looks like INNER JOINs are ok to nest, but LEFT/RIGHT ones cause problems.

Seems as if Access is using both implicit and explicit join notation. This works with Access-only backend, but is against SQL standard (at least for MSSQL) when using ODBC. Implicit join because of ((art_clinic_obs.year_quarter_id)=[code_year_quarter].[id]-1) ???

I didn't find a solution just within Access by restructuring the queries. Instead all subqueries are moved to MSSQL views and only the top-level queries art_reg_cum_outc_valid_base and art_reg_cum_now_valid_base have a matching Access query pendant where the parameters year and quarter are specified.

view_art_reg_cum_now

SELECT        dbo.art_clinic_obs.ID AS Form_ID_now, dbo.art_clinic_obs.year_quarter_id, dbo.code_year_quarter.year, dbo.code_year_quarter.quarter, 
                         dbo.art_clinic_obs.hdepartment_id, dbo.obs.data_element, dbo.obs.data_value, dbo.concept_set.concept_ID_set, dbo.concept_set.sort_weight
FROM            dbo.code_year_quarter INNER JOIN
                         dbo.art_clinic_obs ON dbo.code_year_quarter.ID = dbo.art_clinic_obs.year_quarter_id INNER JOIN
                         dbo.obs_dimensions ON dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id INNER JOIN
                         dbo.obs INNER JOIN
                         dbo.concept_set ON dbo.obs.data_element = dbo.concept_set.concept_ID_member ON dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID
WHERE        (dbo.concept_set.concept_ID_set = 496 OR
                         dbo.concept_set.concept_ID_set = 494 OR
                         dbo.concept_set.concept_ID_set = 899) AND (dbo.obs_dimensions.period_report = 394) AND (dbo.obs_dimensions.sub_group = 398)

view_art_reg_cum_prev

SELECT        dbo.art_clinic_obs.ID AS Form_ID_prev, dbo.code_year_quarter.ID, dbo.code_year_quarter.year, dbo.code_year_quarter.quarter, dbo.art_clinic_obs.hdepartment_id, 
                         dbo.obs.data_element, dbo.obs.data_value
FROM            dbo.code_year_quarter INNER JOIN
                         dbo.art_clinic_obs INNER JOIN
                         dbo.obs_dimensions ON dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id INNER JOIN
                         dbo.obs INNER JOIN
                         dbo.concept_set ON dbo.obs.data_element = dbo.concept_set.concept_ID_member ON dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID ON 
                         dbo.code_year_quarter.ID - 1 = dbo.art_clinic_obs.year_quarter_id
WHERE        (dbo.concept_set.concept_ID_set = 496 OR
                         dbo.concept_set.concept_ID_set = 494 OR
                         dbo.concept_set.concept_ID_set = 899) AND (dbo.obs_dimensions.period_report = 394) AND (dbo.obs_dimensions.sub_group = 398)

view_art_reg_quart_now

SELECT        dbo.art_clinic_obs.year_quarter_id, dbo.code_year_quarter.year, dbo.code_year_quarter.quarter, dbo.art_clinic_obs.hdepartment_id, dbo.obs.data_element, 
                         dbo.obs.data_value
FROM            dbo.code_year_quarter INNER JOIN
                         dbo.art_clinic_obs ON dbo.code_year_quarter.ID = dbo.art_clinic_obs.year_quarter_id INNER JOIN
                         dbo.obs_dimensions ON dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id INNER JOIN
                         dbo.obs INNER JOIN
                         dbo.concept_set ON dbo.obs.data_element = dbo.concept_set.concept_ID_member ON dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID
WHERE        (dbo.concept_set.concept_ID_set = 496 OR
                         dbo.concept_set.concept_ID_set = 494) AND (dbo.obs_dimensions.period_report = 393) AND (dbo.obs_dimensions.sub_group = 398)

view_art_reg_cum_outc_valid_base

SELECT        dbo.view_art_reg_cum_now.Form_ID_now, dbo.view_art_reg_cum_prev.Form_ID_prev, dbo.view_art_reg_cum_now.year_quarter_id, 
                         dbo.view_art_reg_cum_now.year, dbo.view_art_reg_cum_now.quarter, dbo.view_art_reg_cum_now.hdepartment_id, 
                         dbo.view_art_reg_cum_now.concept_ID_set AS Section, dbo.view_art_reg_cum_now.sort_weight, dbo.view_art_reg_cum_now.data_element, 
                         dbo.view_art_reg_cum_now.data_value AS val_cum_now, dbo.view_art_reg_cum_prev.data_value AS val_cum_prev
FROM            dbo.view_art_reg_cum_prev INNER JOIN
                         dbo.view_art_reg_cum_now ON dbo.view_art_reg_cum_prev.data_element = dbo.view_art_reg_cum_now.data_element AND 
                         dbo.view_art_reg_cum_prev.hdepartment_id = dbo.view_art_reg_cum_now.hdepartment_id AND 
                         dbo.view_art_reg_cum_prev.data_value - 10 > dbo.view_art_reg_cum_now.data_value AND dbo.view_art_reg_cum_prev.year = dbo.view_art_reg_cum_now.year AND
                          dbo.view_art_reg_cum_prev.quarter = dbo.view_art_reg_cum_now.quarter
WHERE        (dbo.view_art_reg_cum_now.concept_ID_set = 899)

view_art_reg_cum_now_calc

SELECT        dbo.view_art_reg_cum_prev.Form_ID_prev, a1.hdepartment_id, a1.data_element, COALESCE (dbo.view_art_reg_cum_prev.data_value, 0) 
                         + a1.data_value AS val_cum_calc, a1.data_value AS val_quart_now, dbo.view_art_reg_cum_prev.data_value AS val_cum_prev, a1.year, a1.quarter, 
                         a1.year_quarter_id
FROM            dbo.view_art_reg_cum_prev RIGHT OUTER JOIN
                         dbo.view_art_reg_quart_now AS a1 ON dbo.view_art_reg_cum_prev.data_element = a1.data_element AND 
                         dbo.view_art_reg_cum_prev.hdepartment_id = a1.hdepartment_id
WHERE        (dbo.view_art_reg_cum_prev.year = a1.year) AND (dbo.view_art_reg_cum_prev.quarter = a1.quarter)

view_art_reg_cum_now_valid_base

SELECT        dbo.view_art_reg_cum_now.Form_ID_now, dbo.view_art_reg_cum_now_calc.Form_ID_prev, dbo.view_art_reg_cum_now.year_quarter_id, 
                         dbo.view_art_reg_cum_now.year, dbo.view_art_reg_cum_now.quarter, dbo.view_art_reg_cum_now.hdepartment_id, 
                         dbo.view_art_reg_cum_now.concept_ID_set AS Section, dbo.view_art_reg_cum_now.sort_weight, dbo.view_art_reg_cum_now.data_element, 
                         dbo.view_art_reg_cum_now.data_value AS val_cum_now, dbo.view_art_reg_cum_now_calc.val_cum_calc, dbo.view_art_reg_cum_now_calc.val_quart_now, 
                         dbo.view_art_reg_cum_now_calc.val_cum_prev
FROM            dbo.view_art_reg_cum_now_calc INNER JOIN
                         dbo.view_art_reg_cum_now ON dbo.view_art_reg_cum_now_calc.hdepartment_id = dbo.view_art_reg_cum_now.hdepartment_id AND 
                         dbo.view_art_reg_cum_now_calc.data_element = dbo.view_art_reg_cum_now.data_element AND 
                         dbo.view_art_reg_cum_now_calc.year_quarter_id = dbo.view_art_reg_cum_now.year_quarter_id AND 
                         dbo.view_art_reg_cum_now_calc.year = dbo.view_art_reg_cum_now.year AND 
                         dbo.view_art_reg_cum_now_calc.quarter = dbo.view_art_reg_cum_now.quarter
WHERE        (ABS(dbo.view_art_reg_cum_now.data_value - dbo.view_art_reg_cum_now_calc.val_cum_calc) > 2)

Access query art_reg_cum_outc_valid

SELECT view_art_reg_cum_outc_valid_base.*, view_art_reg_cum_outc_valid_base.Year, view_art_reg_cum_outc_valid_base.Quarter
FROM view_art_reg_cum_outc_valid_base
WHERE (((view_art_reg_cum_outc_valid_base.Year)=[Enter year]) AND ((view_art_reg_cum_outc_valid_base.Quarter)=[Enter quarter]))
ORDER BY view_art_reg_cum_outc_valid_base.Form_ID_now;

Access query art_reg_cum_now_valid

SELECT view_art_reg_cum_now_valid_base.*, view_art_reg_cum_now_valid_base.Year, view_art_reg_cum_now_valid_base.Quarter
FROM view_art_reg_cum_now_valid_base
WHERE (((view_art_reg_cum_now_valid_base.Year)=[Enter year]) AND ((view_art_reg_cum_now_valid_base.Quarter)=[Enter quarter]));

1. Tools - References - Add - C:\Program Files\Microsoft Office\root\VFS\SystemX86\mscomctl.ocx (for 32 Bit Office 2016) http://stackoverflow.com/questions/11984274/ms-access-doesnt-catch-treeview-events-after-win-sec-update
2. Added query current_user
3. Imported VBA modules RelinkTables and ReapplyFieldProperties
4. Added Backend Selector Form
5. Added dbSeeChanges to OpenRecordset calls
6. Changed RowSource for supply_group_select of Form supply_item to SELECT c.ID, c.concept_name FROM concept AS c INNER JOIN concept_set ON c.ID = concept_set.concept_ID_member WHERE (((concept_set.concept_ID_set)=1129)) ORDER BY c.concept_name;
7. TreeView ActiveX control: Some installations don't show the concept_set Tree view, others do. "From research, I noted that this error occurred after microsoft released a certain update in windows 2010 and 2013 which made it to misbehave. With each release of an update to the OCX, microsoft attaches a new version number and fortunately enough the version that seem to work is the latest release of the OCX file which is version 6.1.98.46. All the erroneous machines had version 6.1.98.39 or less. I copied the correct version and circulated it around the machines by unregistering and then reregistering with the desired one and it was bingo!!!". File checked in under dha-mis-migration/frontend-de/OCX.rar
8. Performance improvement for load of form_concept_set: In sub TreeFill change OpenRecordset from dbOpenDynaset to dbOpenSnapshot.

1. Park backend
2. Import mdb in accdb
3. DoCmd.RunSQL "UPDATE art_person SET [Qualification]=888 WHERE [Qualification] IS NULL;"
4. DoCmd.RunSQL "UPDATE psm_dist_item SET [units_adjust]=0 WHERE [units_adjust] IS NULL;"
5. DoCmd.RunSQL "UPDATE psm_dist_item SET [consum_min]=0 WHERE [consum_min] IS NULL;"
6. DoCmd.RunSQL "UPDATE htc_person_obs SET [correct_pt_result]=6 WHERE [correct_pt_result] IS NULL;"
7. change supply_item.CMS_code to indexed = Yes (Duplicates OK)
8. change validation rule for htc_person to "Is Null Or Is Not Null"
9. art_clinic_obs.end_time: Between #8:30:00 AM# And #11:30:00 PM# Or Is Null
10. htc_site_obs.start_time: Between #6:30:00 AM# And #6:00:00 PM# Or Is Null
11. htc_site_obs.end_time: Between #8:30:00 AM# And #11:30:00 PM# Or Is Null
12. remove cascading updates and deletes for references concept.concept_ID_parent to concept_1, htc_person.deduplicate_ID to htc_person_1, art_person.DeDuplicate_ID to art_person_1
13. htc_prov.ID disable allow zero length
14. htc_person_obs.correct_pt_result add "Or 9" to validation rule (as 1000+ such records exist)
15. psm_relocate set index uniq to non-unique, otherwise ~100 constraint violations, see XLS spreadsheet for details

1. 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
2. Start migration wizard
3. Select all tables from Access DB except MSys... and deselect queries
4. 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.
5. Don't link tables

1. backup taken -after-SSMA
2. 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

1. Adjust constraint for psm_relocate according to Access-Findings.
2. Adjust constraint for htc_person.email
   • Validation for field email contains the word/function Like. SSMA can't parse this expression.
   • Replace validation prior of migration from Is Null Or ((Like "*?@?*.?*") And (Not Like "*[ ,;]*")) to Is Null Or Is Not Null and after migration modify constraint on MSSQL for email to [email] IS NULL OR ([email] LIKE '%_@_%_._%' AND [email] NOT LIKE '%[ ,;]%')
3. backup taken -after-manual-changes.bak
4. create field_properties table from SQL and import field_properties values from field_properties.txt

CREATE TABLE [dbo].[field_properties](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Table] [nvarchar](255) NULL,
	[Field] [nvarchar](255) NULL,
	[Caption] [nvarchar](255) NULL,
	[DecimalPlace] [nvarchar](255) NULL,
	[Format] [nvarchar](255) NULL,
	[IMESentenceMode] [nvarchar](255) NULL,
	[ShowDatePicker] [nvarchar](255) NULL,
	[TextAlign] [nvarchar](255) NULL,
	[TextFormat] [nvarchar](255) NULL,
	[DisplayControl] [nvarchar](255) NULL,
	[RowSourceType] [nvarchar](255) NULL,
	[RowSource] [nvarchar](max) NULL,
	[BoundColumn] [nvarchar](255) NULL,
	[ColumnCount] [nvarchar](255) NULL,
	[ColumnHeads] [nvarchar](255) NULL,
	[ColumnWidths] [nvarchar](255) NULL,
	[ListRows] [nvarchar](255) NULL,
	[ListWidth] [nvarchar](255) NULL,
	[LimitToList] [nvarchar](255) NULL,
	[AllowMultipleValues] [nvarchar](255) NULL,
	[AllowValueListEdits] [nvarchar](255) NULL,
	[ListItemEditForm] [nvarchar](255) NULL,
	[ShowOnlyRowSource] [nvarchar](255) NULL,
 CONSTRAINT [PK_field_properties] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

1. add map_user.ActiveDirectoryUser column with nvarchar(25) and import map_user.txt
2. backup taken -before-first-data-entry.bak
3. backup taken -170417
4. Migrated updated concept and concept_set via SSMA to both -live and -test on SQL Server

supply_item

• supply_item: not migrated at all, 189 rows missing
• supply_tem.CMS_code
  • unique, but not required index definition
  • 68 of 189 rows with null value

MSSQL (or even SQL standard?) doesn't support this index constellation out of the box. Within MSSQL the NULL values are considered to be duplicates, in Access not. This leads to unique key violations during import for all remaining 67 rows with null value for CMS_code.

Solution:

• Prior to migration change Index definition in Access from Unique to 'with duplicates' to allow import.
• After migration create below Filtered Index for MSSQL http://stackoverflow.com/a/3570832 by executing this SQL statement:

create index index_supply_item_allow_null unique on supply_item(CMS_code) 
 where CMS_Code is not null;

(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).

Instead of relying on spot checks and most likely incomplete UI tests, as much as possible the whole migration should be tested automatically and as a whole.

The 'raw' datasets (before and after migration) can be quite easily compared. For this CSV dumps from every table are generated both from the old Access backend DB as well the new MSSQL DB.

Prepare environment:

1. Install NodeJS
2. Install additional modules from command prompt

npm install --save tedious
npm install --save moment
npm install --save fs
npm install --save async

1. Open VB Editor of DB
2. Import file dha-mis-migration/access/CsvExport.bas
3. Run VB Macro ExportAll (CSV files are created in My Documents)
4. Post process obs file

sed -i.bak 's/-0.00/0.00/g'  obs-access.csv   
sed -i.bak 's/5.0e-03/0.00/g'  obs-access.csv   
sed -i.bak 's/9.9e-03/0.00/g'  obs-access.csv   
sed -i.bak 's/3.0e-03/0.00/g'  obs-access.csv   
sed -i.bak 's/2.0e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/7.0e-03/0.00/g'  obs-access.csv   
sed -i.bak 's/9.69e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/9.69e-06/0.00/g'  obs-access.csv 
sed -i.bak 's/8.0e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/3.52e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/2.17e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/9.8e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/9.0e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/9.8e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/1.0e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/9.0e-03/0.00/g'  obs-access.csv 
sed -i.bak 's/5.0e-04/0.00/g'  obs-access.csv 
sed -i.bak 's/387304.0/387304.00/g'  obs-access.csv 
sed -i.bak 's/133323.0/133323.00/g'  obs-access.csv 
sed -i.bak 's/133000.0/133000.00/g'  obs-access.csv 

1. Create local login user root/root (in SSMS under tree element database/security) and run these commands in sqlcmd prompt:

use HIVData
go
exec sp_addrolemember 'db_owner', 'root'
go

1. Check file dha-mis-migration/nodesjs/tedious-csv-dump.js for correct credentials and DB connect configs
2. Run Batch file dha-mis-migration/nodesjs/tedious-csv-dump.bat (CSV files are created in Desktop/csv-export-sql)

Comparing CSV data dumps within the Access connections (one when connecting to Access backend DB and the other one when connecting to the MSSQL backend). But as a new column rowversion/timestamp is added through the migration, the dumps aren't easily comparable and require a post-process to remove this data again for every table.

For the obs table with a bash shell these statements can be used to compare the dumps

awk -F',' '{print $1,$2,$3,$4}' obs_msqsql.csv | tr ' ' ',' > obs_mssql2.csv
diff -w obs_access.csv obs_mssql2.csv

E.g. under Windows with WinMerge or under *nix with diff. Note that obs-access.csv results in an out-of-memory error of WinMerge.

Expected differences are mainly due to different representations of the value 0 within float datatype.

Besides the raw data, also the table structure and its details can be externally verified.

Access:

1. Import file dha-mis-migration/access/CsvExportTableDefinition.bas
2. Run Macro ExportallTableDefs (CSV file is created in Desktop)

SQL Server:

1. Check file dha-mis-migration/nodesjs/tedious-tabledef-dump.js for correct credendials and DB connect configs
2. Run Batch file dha-mis-migration/nodesjs/tedious-tabledef-dump.bat (CSV file is created in Desktop)

Similar to the raw data of every single table, all queries are supposed to still return the same values. Sadly Access doesn't allow to script the invocation of queries with parameters without any user interaction, so for now only param-less (Non-action) queries are included in the scripts.

With Access Backend:

1. Open frontend

2. Run script ExportAllQueries

3. Copy all CSV files from Documents to new folder (e.g. csv-export-de-access-queries)

4. Copy Desktop\File.txt to new folder

5. Sort all lines for each file. Should be possible with PowerShell, but took forever: gci | ForEach-Object { gc $.FullName | sort > "$($.BaseName)" }. Instead under Unix this was much faster: find . -type f -exec sort -o {} {} ;

6. Repeat for other frontends

MSSQL Backend:

1. Repeat steps from above Access Backend and adjust accordingly, e.g. create folder csv-export-de-sql-queries

Compare results:

1. Use a file diff (e.g. WinMerge under Windows) to search for (relevant) differences

run CsvQueryDump for both backends import & save into XLS import both into access query link based on field 3 (name), ideally incl. autonumber copy&paste result back to XLS change number decimal from . to , (for bloody german XLS)

As Access makes it easy to compose bigger queries from smaller queries, there complexity or level of re-usage goes up. While building bigger blocks out of smaller blocks is often a good thing, it can also increase the coupling of these subcomponents. Knowing what is used where can be crucial to understand and estimate changes. Access doesn't have an out-of-the-box visualizer for such object dependencies, but similar to other database management systems it offers some internal/undocumented tables that can be queried.

Check out dha-mis-migration.wiki/graphviz-access-dependencies/README.md to see queries to these meta-tables as well as instructions on how to automatically generate dependency diagrams/hierarchy levels of these links.

To spot long-running sub/procedures profiling code can be added to either every sub of the currently active CodePane or to every method of the project. Runtime statistics can be initialized/reseted with a Profiler.resetProfileManager and results printed t the console at the end of execution (or via VBA Immediate Windows) with a call of Profiler.GetProfileManager.PrintTimes.

Code is found under dha-mis-migration/access/profiler

Access and VBA lack a global error handler. While the full Access installation has a generic error handler dialog (with Debug, End, Cancel) the Access runtime simply quits the whole Frontend without the chance to recover from an unhandled error. For most of these cases an generic error handler will be useful to allow the continous use of the frontend.

Either the currently active CodePane or all VBA sources can be used. Besides adding error handlers they can also be removed. In its current form a conditional error handler is added, which is only used when run on the Access runtime.

dha-migration/access/ErrorHandlerInstructCodebase.bas

Most of the required code changes are scripted through VBA module dha-mis-migration/access/MigrateCodeToMssql.bas. This contains hardcoded search&replace patterns to redo parts of the migration for all frontends.