Access Findings - x-ian/dha-mis-migration GitHub Wiki

Relevant aspects and findings of the 'raw data' migration from the Access backend DB to MSSQL.

• UI-related definitions on tables and columns: captions, formats and input masks (potentially workaround available)
• Validation Texts
• Aspects of backend-related Lookup Columns
• Edit data in datasheet view for tables using lookup column convenience (potentially workaround available)
• Automatic 'Push' for data updates between different Access-only users. In MSSQL an explicit pull/refresh is required if data on the server changed. At least within one Access DB data changes are automatically available to other users. Doesn't appear to be relevant here and unclear if this even works with the current split in frontend and backend DBs.
• The completely-offline-scenario by simply copying the DB files doesn't work. This could be useful for performance reasons, presentation, testing or dev purposes, but requires a local MSSQL installation and some custom scripting/processes.
• Relationships Diagram; now mostly the auto-defining of links between tables in Query Wizard which doesn't work anymore

• Tables with relations
• Indexes
• Data Constraints
• Default values
• Select Queries, maybe Unions and Crosstabs too. But for now no query from the backend DB is considered as there is little to no benefit having these backend DB queries migrated (unlike the Frontend queries).

• All Lookup columns migrated?

• Exact definition of lookup columns used?

• All Indexes migrated?

• All Default values migrated? E.g. art_person.date works, but others like dates?

• All Validation Rules for fields migrated?

• All Validation Rules for tables migrated?

• art_sched_person.art_sched_team_id is a simple number field, yet the migration marked it as a foreign key to art_sched_team. How/why?

• YesNo Datatype

  • SQL doesn't have a dedicated boolean type; often datatype bit is used and queries need to explicitly test for =0 (false) or =1 (true)
  • Yes/No fields are not-nullable in Access
  • SSMA message: "The bit column 'Voided' has no default value. To avoid problems with null values in Access applications, the default value '0' was added to target column."
  • Converted to nullable bit with default value 0, TODO: why not nullable in the first place?
  • In VBA code 0: true, -1: false; In Access export and SQL 1: true, 0: false; Impact on VBA code or other expressions?
  • Used in 12 tables

• Transactional behavior / concurrent edits?

• New errors(?):

  • Synchronization Error Introducing FOREIGN KEY constraint 'art_person${CA55C24B-9004-47AF-80C6-3B6BBDB84807}' on table 'art_person' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.
  • Synchronization Error Introducing FOREIGN KEY constraint 'concept${4A80ABC1-7C3F-4BBB-A42C-BAF71EED6BCD}' on table 'concept' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.
  • Synchronization Error Introducing FOREIGN KEY constraint 'htc_person${83AFCB26-7BC2-4DC4-B3BC-34BCE09B2294}' on table 'htc_person' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

The Linked Table manager is still the main tool to manually establish links between an Access frontend and a MSSQL backend. This time an ODBC DSN (datasource name) is used. DSNs can be defined either system-wide for every user (via registry), just configured for a specific user (also via registry), taken from a file or set up programmatically (DSN-less).

Unlike the system or user DSNs the File-based DSNs does not require any upfront system configuration and therefore can be easily passed around. Furthermore Access 'imports' the contents from the File-DSN (just a text/ini file) and basically uses a DSN-less connection after it was used. With this the frontend is 'self-contained' and can be used by other users/workstations with re-config as it knows by itself how and where to connect.

However it needs to be carefully used as the password for the connection can be (and sometimes needs to be saved) somewhere. It will be cleartext in the DSN-file (which should be ok as only 'database admins' need access to it), but it seems that with enough effort it could also be possible to extract this password from the Access frontend.

MSSQL also offers Trusted connections where the current Windows user (possibly from an Active Directory) is used to authenticate against MSSQL without exposing the passwords. Given the current understanding of both the environment at the office and this technical implementation, it seems like the best way forward.

MS offers several ODBC drivers to connect to a MSSQL database:

• SQL Server: Pre-installed with Windows and extremely outdated
• SQL Server Native Client 11.0: Used to be the primary ODBC driver back in the days; still comes with SQL Server 2014 installation; but according to https://msdn.microsoft.com/library/ms131415.aspx no longer supported
• ODBC Driver 11 for SQL Server: Nowadays the preferred choice of the day https://msdn.microsoft.com/library/mt654048.aspx. If MSSQL is not installed, it requires an upfront installation of the ODBC driver package on every system to be connected from.

When using the Linked Table manager to link to MSSQL tables for the first time, every tables is prefixed with the schema name (dbo_ is the default). Tables can be manually renamed to match the current names, but this is a tedious activity. The following VBA script automates this process

Sub CDDB_ChopTableName()
	Dim tdf As DAO.TableDef
	For Each tdf In CurrentDb.TableDefs
		If Mid(tdf.Name, 1, 4) = "dbo_" Then
			tdf.Name = Mid(tdf.Name, 5, 64)
		End If
	Next
	Debug.Print "done"
End Sub

Even more convenient is the script 'RelinkAllTables' which automatically links (and re-links) to all known tables by simply removing (if necessary) and creating even the initial setup of the linked tables in any DB. With this script the File-based DSN is no longer needed and it can quickly establish the relevant links in any Access DB. (But when using username/password for the ODBC connection they need to be hard-coded in the script.) This approach could also be used to switch between different databases, e.g. for production, development, or testing.

Linked Tables need to be updated whenever the definition of the underlying tables changes in MSSQL (columns and/or indexes).

"When you create an ODBC-linked table, Office Access looks first for a clustered unique index. Each SQL Server table can only have one clustered index, which determines the order in which the data is physically stored. The clustered index does not have to be the primary key and does not even have to be a unique index (if not, SQL Server adds a value for each row that makes it unique). If all the unique indexes are nonclustered, Office Access uses the one that happens to be listed first alphabetically, which may not be the most efficient choice."

"If you have an index that you want Office Access to use for the keyset, it should either be a clustered index or it should have a name that sorts to the top of an alphabetical list. To control the choice of index programmatically or when you don't have control over the index names on SQL Server, there is no Office Access property that you can set in code. However, you can do something that seems like it shouldn't be possible: you can execute a Data Definition Language (DDL) query in Office Access that creates an index on the Office Access link itself, without affecting the linked SQL Server table. If you want to change a link from using one unique index to using another, you first must drop the index being used, because Office Access prevents you from creating a second unique index for a link that already has one assigned." (from https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx)

The conclusion is that for ease of use and consistency a linked table should always a dedicated unique, not nullable Primary Key.

Unlike Access states when opening the Design view of a linked table, it still allows to change some of its settings for fields. E.g. Format patterns for a field like phone number or Lookup Columns to be automatically used in the default data sheet view. However these changes will get overwritten after re-linking the table. It is unclear if such UI-related configs on Linked Tables will always work the same way as in the Access-only environment (e.g. automatically being used in derived forms, ...). But if yes, then it could be worthwhile to explore this further. As these settings get overwritten during each (re)link (as their is no default place on the MSSQL table definitions), it might be helpful to have some VBA code adding them automatically (maybe with even storing these config in MSSQL as extended properties for every table/field).

• http://sourcedaddy.com/ms-access/modifying-linked-tables.html
• https://bytes.com/topic/access/answers/207818-change-input-mask-code

With MSSQL tables Access looses the implicit feature of having RowSource Lookup Column (semi-) automatically populate the generic datasheet with 'proper names' for relations with foreign keys (instead of displaying the IDs).

Through updateable views there might be a way to gain back some/most of this convenience: View and Edit with RowSource Lookup columns appear to be possible; Create and Delete most likely not. However when then editing a row make sure that only columns from one underlying table of the view is changed. Another problem is that the RowSource property of a LinkedTable disappears, when relinking/updating the links. So these RowSource properties would need to be redefined as well. Maybe there is a clever way to automate this setup.

Setup:

1. A materialised view in MSSQL (https://msdn.microsoft.com/en-us/library/ms180800.aspx) with linking the Lookup values together
2. Linking this view as a linked table into Access
3. Define the identifying column for the 'base table'
4. Configure RowSource properties for every column accordingly

An alternative approach could be to create a 'continuation form' for every relevant linked table where a RowSource Lookup feature could be helpful.

The current queries in the backend DB are either used as Lookup Columns, are historical leftovers or provide some assistance in admin activities. As the lookup columns won't work as they are right now and there is no need to keep the outdated query definition, none of these queries is currently considered to be worthwhile to migrate (unlike potentially some queries from the frontends). However as during the migration evaluation they were included in the analysis. These findings are listed below for future reference.

When linking (select) queries aka views into Access through ODBC, they simply appear as linked tables.

Some differences between Access and MSSQL:

• String concatenation in Access is done through &, e.g. person.lastname & " " & person.firstname. If in a result column of a query one of the concatenated values is NULL, then an empty string '' is used instead. In MSSQL however a concatenation is done with + (like person.lastname + ' ' + person.firstname) and will result in a NULL value itself for the whole field, if one or more concatenated values contain NULL. Instead of using + (as it is also done through the migration assistant), the function CONCAT can be used to avoid testing for IsNull on every nullable field. This leads to CONCAT(person.lastname, ' ', person.firstname).
• In SQL 'ordinary' views can't have an order criteria. As a workaround, a view definition with TOP can be used.
• In general Access SQL is a superset of MSSQL SQL, therefore there are some expressions within Access that do not (easily) match to a MSSQL statement. These statements either need to be rewritten, or are simply executed on the frontend side (with most likely a severe performance bottleneck because more data needs to be fetched to the local DB).
• As per standard SQL, the order of records in a view is undefined whereas Access allows an ORDER BY criteria. However for MSSQL there is a workaround to allow view sorting (by using TOP 99999999999)
• • Performance limitations: As Access SQL is more expressive (e.g. with the Iif statements, Date arithmetic, Trim) and the SQL parser of Access will not always find a way to efficiently create pure SQL statements for MSSQL, it simply retrieves the whole data set and does the processing client-side on the frontend. This can be very inefficient.

The MSSQL backend (as well as newer Access versions) doesn't work with MDW (workgroup) files; permission/security needs to be removed for the migration:

1. Create empty DB
2. Import external data (all tables except the internal MSys... & queries) from another DB

Replacement thoughts:

• A couple of ODBC connection choices:
  • Trusted Connection through current Windows provider
  • Dedicated user created on SQL Server
    • Share one SQL Server account for all ODBC connections and have a Access-local setup to verify users
    • Create a SQL Server user account for every valid DB user
• Create/update user/timestamp fields are currently managed in VBA code for every form. So these fields only get updated if this code is executed. Updates to the generic datasheet view won't automatically pick up the relevant values.
• If the SQL Server knows about all valid users, then it might automatically update these values on server side with triggers http://stackoverflow.com/questions/31634918/how-do-i-add-a-last-modified-and-created-column-in-a-sql-server-table
• But the current VBA code should continue to work with most likely only minor modification to replace the CurrentUser calls with something else.

select system_user returns either current local SQL Server username or Windows AD user (as domain\username). Within a SQL passthrough query this will return the current user login of MSSQL

A custom public function CurrentUser() will overwrite built-in Access Security function.

1 Create AD user, with AD domain 1 Add new login to MSSQL (under database, security, right click) 1 Advanced search for either user or group for AD location (group search is deactivated as default) 1 Select element from search 1 User Mapping to DB HIVDatabase3 1 Create Server Role and add login as member 1 Set permissions for each table, assign role and tweak permissions

http://dba.stackexchange.com/questions/51311/using-ad-groups-vs-roles-in-sql-server

In Access Lookup columns with its RowSource setting serve these multiple purposes from below and blurry the line between client (UI) and server (backend) with convenient features:

1. Define foreign key relationship optionally define a constraint to ensure only valid foreign keys are used, e.g. map District ID to District name
2. Define a 'filtered' foreign key relationship with a WHERE clause, e.g. only allow relationship to certain parent concepts
3. Define the UI elements/component to be used for edit, e.g. a Combobox
4. Define a static set of possible values
5. Define formatting and behavior of the UI element, e.g. size of dropdown box and restriction to values of underlying query
6. 'Derived forms' inherit/copy these settings as defaults to set up standard UI elements

Lookup column rowsource properties can be defined in different flavours' (TODO: describe what the SSMA is doing in each case, if at all)

• Lookup columns referring to pre-defined Access (Select) queries
• Lookup columns using SQL SELECT statements (in Access SQL dialect)
• Lookup columns with static set of possible values (e.g. 'N', 'Y', '')

Current usage of Lookup columns:

• 109 Lookup columns
• 17 Lookups make use of 5 predefined select queries
• 18 have static mappings like 0 for N, 1 for Y, 9 for blank
  • 5 'categories' of mappings, could be worthwhile to also refer to an externalised definition to avoid future copy&paste and increase consistency (e.g. always representing 0 with N instead of No in one case)
• 74 have direct SQL SELECT statements
  • ~30 queries across 6 categories are exact clones, for maintenance & consistency already candidates to be externalised/unified in dedicated queries/views
  • ~13 overall 'categories' of mappings (assuming minor differences can be unified), worthwhile to try and unify the ones with minor differences

(Notes to myself: The Lookup definition can be summarized and partially analysed by custom CSV dump of Access table definitions and some scripting: tr '|' '\n' < TableCreateDefinition.csv | grep Lookup | sort)

Besides Lookup Column Select queries can't be referred from the frontends anyways. Appears that if they are required by the frontend, they are already duplicated there. And Lookup RowSources won't work for the UI with AutoDropdown boxes with (unmodified) linked tables.

These queries have been confirmed to be historical:

• Malaria_append_DL29: Database Documenter reports 'The MS Office Access database engine cannot find the input table or query 'NMCP_DL29_export'. Make sure it exists and that its name is spelled correctly.'
• malaria_append_query
• Malaria_append_query_DL30
• DL30_sites_not_appended
• malaria_dl30_appended
• malaria_file

These ones are not referred in lookup columns. If required by the frontend, they should already be duplicates of them there.

• art_staff_obs_app (append query)
• obs_dimension_append_query (append query)
• ANC_New_Reg_validation (crosstab query)
• summation_check_anc (select query): Database Documenter was unable to document query.
• Merging_temp_table (select query)

Action Item: Remove queries of current backend DB altogether.

If in future, e.g. to import new Malaria list or other administrative purpose, temporary queries are required, they might either be moved to the Admin frontend or a new 'Backend-Frontend' Access DB with linked tables to MSSQL can be used. This also might make sense to continue using Access to quickly view data from the tables.

used in:

• art_sched_person
• art_staff
• art_staff_obs

Issues: Syntax Errors with "identifier not converted (4); View definition contains 2 Format and 2 Iif statements"

Solution: Create a view with this definition:

USE [HIVData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[art_person_deduplicate]
AS 
SELECT TOP 9223372036854775807 WITH TIES   
  art_person.ID, 
  CONCAT(art_person_1.NameLast, ' ', art_person_1.NameFirst, ', ', art_person_1.Position, ' (' + art_person_1.phone + ')', IIf(art_person.ID<>art_person.DeDuplicate_ID, ' (VOIDED)', '')) AS person, 
  art_person.DeDuplicate_ID
FROM art_person 
  INNER JOIN art_person AS art_person_1 ON art_person.DeDuplicate_ID = art_person_1.ID
ORDER BY Person;
GO

TODO: There is no immediate replacement for the FORMAT function used in Access; formatting is not done for now. One solution is to manually split the phone string into its subcomponents and add/concat spaces in between.

used in:

* psm_relocate (fields hdepartment_id_send and hdepartment_id_recv)

Issue: Error at "identifier not converted (2); Query definition contains a Trim and an Iif statement"

Solution: Create a view with this definition

USE [HIVData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[hdepartment_name]
AS 
SELECT TOP 9223372036854775807 WITH TIES   
  code_hdepartment.ID,
  CONCAT(
    LTRIM(RTRIM(
      CONCAT(
	    [hfacility_name], 
	    ' ', 
	    IIf([hservice]=294,[name_healthdept],'')
	  )
    )),
    ' (', 
    concept_hservice.concept_name, 
    '), ',
    concept_district.concept_name
  ) AS SiteName
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 SiteName;

used in:

• art_drug_stocks
• map_regimen_supply
• map_supply_item_cms_code
• supply_item_ID
• psm_dist_batch
• psm_DL_item
• psm_relocate
• psm_relocate_old
• psm_ro_item
• psm_stock_report
• supply_item_set

Issues:

• Warning: "View columns with same name (concept_3.concept_name and concept_4.concept_name)." Every column in MSSQL views needs to have an unique name.
• With automatic migration the resulting view on MSSQL can not be used leading to a "Unable to open view in MSSQL: Msg 245, Level 16, State 1, Line 2; Conversion failed when converting the varchar value ')' to data type smallint."

Solution: Create view with following definition

USE [HIVData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[supply_item_label]
AS 
SELECT TOP 9223372036854775807 WITH TIES
  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 concept_3_name, 
  concept_4.concept_name as concept_4_name
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 supply_item_label;

In general prefer Decimal whenever less precision than 38 digits is needed http://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server.

When comparing data through the Access frontend technology with a direct MSSQL connection (through its internal TDS protocol from a nodejs Javascript client), different representations of numbers occurred. In particular the MSSQL type float tends to return slightly different raw numbers. However when comparing the output of the Access frontend linked to the Access backend and an Access frontend linked to the MSSQL database, both Access frontends behave the same. Therefore no action points arise from this, just documented for reference.

Float representation of 0:

• rounding differences in table obs
• -0.00 instead of 0.00
• -many 'close-to-0' floats like 1.0e-03 (0.001)

Datatype Currency:

• used 3x in pop_map and pop_sex_district
• will be converted to MSSQL datatype Money

Unique index violation for combined index uniq on columns with migration assistant (on combined index hdepartment_id_send, hdepartment_id_recv, supply_item_ID, move_date, expiry_date (optional, nullable)). Optional/nullable index field are treated differently between Access and MSSQL. While MSSQL reports a constraint violation from the second and above records with a NULL value, Access allows them. There are 78 violations with each 2 or 3 rows resulting in 169 problematic records.

While not always desirable from the business logic, the current behaviour needs to be preserved (as there are valid cases of multiple relocations of the same drug at the same time).

For the data migration the constraint in Access should be weakened by removing the expiry_date from the combined index. Once the migration finished, manually modify the created constraint to include the expiry_date again with a conditional check. (Action item: still to be done)

Action item: However some of the conflicting data also points to a data entry error. So it is advisable to extend the frontend to double-check if the user really wants to add another relocation of modify the existing one..

Action item: Validate the currently existing duplicates as some/most of them might be actually unwanted records. To identify them, follow these steps:

• Create a query 'psm_relocate_duplicates1' with this SQL definition:

SELECT min(psm_relocate.id), psm_relocate.supply_item_id, psm_relocate.expiry_date, psm_relocate.move_date, psm_relocate.hdepartment_id_send, psm_relocate.hdepartment_id_recv, Count(*) AS Expr1

FROM psm_relocate

GROUP BY psm_relocate.supply_item_id, psm_relocate.expiry_date, psm_relocate.move_date, psm_relocate.hdepartment_id_send, psm_relocate.hdepartment_id_recv

HAVING (((Count(*))>1))

• Create query 'psm_relocate_duplicates_2':

SELECT psm_relocate.ID, psm_relocate.supply_item_ID, psm_relocate.move_date, psm_relocate.hdepartment_id_send, psm_relocate.hdepartment_id_recv, psm_relocate.expiry_date, psm_relocate.phone_authrecv, psm_relocate.move_units, psm_relocate.move_date, psm_relocate.move_rsn, psm_relocate.receive_packs, psm_relocate.receive_units, psm_relocate.receive_date, psm_relocate.User, psm_relocate.TimeStamp, psm_relocate.UpdateUser, psm_relocate.UpdateTimeStamp

FROM psm_relocate INNER JOIN psm_relocate_duplicates1 ON (psm_relocate.move_date = psm_relocate_duplicates1.move_date) AND (psm_relocate.hdepartment_id_recv = psm_relocate_duplicates1.hdepartment_id_recv) AND (psm_relocate.hdepartment_id_send = psm_relocate_duplicates1.hdepartment_id_send) AND (psm_relocate.supply_item_ID = psm_relocate_duplicates1.supply_item_id)

ORDER BY psm_relocate.supply_item_ID, psm_relocate.move_date, psm_relocate.hdepartment_id_send, psm_relocate.hdepartment_id_recv, psm_relocate.expiry_date;

Tables in Access with an AutoNumber field and the column name ID (maybe even as only part of its name) will be automatically treated as a PK/index field in Access (according to some source on the web; maybe additional research useful). During the migration to MSSQL these fields are automatically treated as Primary Keys and do not require additional attention.

However some tables do not fit into the above category. They are still migrate-able, but according to some web sources maybe readonly through Access frontends. In my tests with the Access 2007 and MSSQL 2014 this wasn't the case, but it still seems advisable to revise the PK/index configs

• map_user: Field user unique & not nullable, but no PK, Action Item: not used, table to be deleted
• map_supply_item_cms_code: Field cms_code unique & not nullable, but not PK, Action Item: intermediate table for import, no need to migrate, potentially keep it as a local table in backend-frontend-access-db
• NMCP_DL20_export, NMCP_DL21_export: No index at all, Action Item: intermediate table for import, no need to migrate, potentially keep it in a backend-frontend-access-db
• NMCP_DL_26_export, NMCP_DL30_export: Non-unique index, Action Item: intermediate table for import, no need to migrate, potentially keep it in a backend-frontend-access-db
• population: Uniq with year, district, rururb, sex age5, Action Item: also moving to the backend/frontend db, not to migrate
• pop_sex_district_hiv, Action Item: introduce new field ID as PK

• 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;

Due to different locking and concurrency behaviour, some tables (depending on the used columns) need an additional readonly column as a 'rowversion'. This is used by Access to detect and potentially cancel concurrent edits.

• Relevant for Access Frontends for concurrency as for some columns it can't detect whether its local dataset is still the most recent one on the server; apparently Access automatically detects such a column in linked tables and includes it in its UPDATE and DELETE statements.
• Useful to add a TimeStamp column as a default to every MSSQL table.
• MS recommends to switch to datatype rowversion, but the SSMS doesn't support it. Appears as if rowversion and timestamp are fully synonymic.
• Should not/never be updated manually, e.g. via INSERT/UPDATE statements or (partial) data imports.
• http://stackoverflow.com/a/12943961
• https://support.office.com/en-us/article/Move-Access-data-to-a-SQL-Server-database-by-using-the-Upsizing-Wizard-5d74c0df-c8cd-4867-8d07-e6e759d72924?CorrelationId=6463e63a-3477-4526-8b62-5ae4f6c4b788&ui=en-US&rs=en-US&ad=US&ocmsassetID=HA010275537

46 entries

SELECT art_clinic_obs.ID, art_clinic_obs.start_time, art_clinic_obs.end_time
FROM art_clinic_obs where start_time > end_time;

Validation rule for implement_date: Between Now()-182 And Now()+182) using current date as validation. Leading to more and more validation violations over time.

2000+ violations, Assumption: Ok for now.

SELECT art_coh_reg_target.implement_date, art_coh_reg_target.*
FROM art_coh_reg_target
WHERE ((Not ((art_coh_reg_target.implement_date) Between Now()-182 And Now()+182)));

12 violations, Assumption: ok

Between DateAdd("yyyy",-15,Now()) And DateAdd("yyyy",-70,Now())

SELECT htc_person.birth_date, htc_person.*
FROM htc_person
WHERE ((Not ((htc_person.birth_date) Between DateAdd("yyyy",-15,Now()) And DateAdd("yyyy",-70,Now()))));

33 violations, Assumption: ok

Between DateAdd("m",-1,Now()) And DateAdd("m",4,Now())

SELECT psm_dist_round.dist_date, psm_dist_round.*
FROM psm_dist_round
WHERE ((Not ((psm_dist_round.dist_date) Between DateAdd("m",-1,Now()) And DateAdd("m",4,Now()))));

Per default project setting the SMAA converts the Access Date/Time into the SQL type datetime2. In MSSQL datetime2 was introduced to be SQL-compliant and increases both precision (below 1/3 second) and time range (for dates below year 1751 or so). However a simple addition of dates with '+' doesn't work anymore (most likely also a Non-Standard-SQL expression). Instead the dateadd function needs to be used. But as it looks the Access SQL parser doesn't (always?) translates the expressions.

SMAA - Default Project Settings - Type Mappings - date: Change from datetime2 to datetime.

DoCmd.RunSQL ("delete from art_drug_stocks where art_drug_stocks.expiry_date_min < #01/01/2000#") DoCmd.RunSQL ("delete from code_hdepartment where code_hdepartment.service_start < #01/01/1900#")

[[Section: Working with SQL Server datetime data ranges][https://blogs.msdn.microsoft.com/ssma/2011/03/06/access-to-sql-server-migration-understanding-data-type-conversions/]] http://stackoverflow.com/questions/31963130/sql-server-migration-assistant-for-access-date-data-type

Access can use “time-only datetime fields” where the date part is hidden with an Input mask. Still Access stores a full timestamp including the date part, but defaults the date fraction automatically to '12/30/1899' (default minimum date value in Access?). Validation Rules in Access for these fields do not need to specify this 'default' date fraction and the automatic migration also works, but constraints within MSSQL need to explicitly include this date. Like: (([start_time]>='12/30/1899 6:30:0' AND [start_time]<='12/30/1899 18:0:0' OR [start_time] IS NULL)) Time-only fields are only actively used in table art_clinic_obs for the fields start_time and end_time and defined for table htc_site_obs which doesn't have any records.

• psm_dist_item: Some very low numbers with min of -53992676 for ZCH
• psm_dist_item: Some very high numbers with max of 54001108 for Pirimiti Health Centre

Action Item: to be followed up with PSM (not just to 50.000.000, but also the ones in the range of million).