Microsoft Office 365 Version 2009 May Break Your Database Application


Microsoft has reported that they’ve disabled the feature which should then avoid the need to rollback the version. The new feature will be made available in version 2010, planned for end of October 2020. In meanwhile, it will be deactivated, and will remain so until then. There should be no need to change to a different version, version 2009 should no longer link to datetime2 as Date/Time Extended. If you’ve been impacted, try and restart your Access and check that you are up to date with your Office updates and relink tables. It should then be corrected.

When the feature is enabled in Version 2010, it will not impact behavior of any existing databases unless you either explicitly add a Date/Time Extended column to a local table, or go to Tools/Options/Current Database, and check the “Support Date/Time Extended (DateTime2) Data Type for Linked/Imported Tables” option.

In either of those cases, you will get a warning that this will update the database version to handle the new type. You can also add local Date/Time Extended columns, but leave the option unchecked if you want to continue to link to SQL datetime2 as Date/Time.

When linking to a remote table, if the option is not checked, datetime2(0), and datetime2(7) will both link as Date/Time, and if the option is set, then they will both link as Date/Time Extended.

For historical note, the original post below will be left. However, to emphasize, you do not need to perform rollback. If you have issues or questions, let us know!

Original report

In version 2009, Microsoft introduced a new data type, Date/Time Extended which would provide greater compatibility with SQL Server’s datetime2 data type.

Unfortunately, upgrading to version 2009 means that if you have an Access application that links to SQL Server database, and any of the linked tables has a datetime2 field, the next time you relink to the table, the data type will be changed from “Date/Time” to “Date/Time Extended” which can then break the existing code that expects it to work with VBA’s Date data type and Access’ original Date/Time data type. VBA has no way of representing the Access’ new “Date/Time Extended” data type except as a string.

To avoid this problems:

1) Check that you are not on Current channel. We recommend that you be on semi-annual channel.
2) If you are on Current channel, check if you are on build 2008 or earlier. If so, disable your Office updates to avoid updating to build 2009.
3) If you are on Current and build 2009, you can revert to 2008 by running the command prompt as an administrator:

cd %programfiles%\Common Files\Microsoft Shared\ClickToRun
officec2rclient.exe /update user updatetoversion=16.0.13127.20508

To check your current version:
RESOLVED: Microsoft Office 365 Version 2009 May Break Your Database Application

As an alternative to disabling updates, we recommend that you change your channel from “Monthly” to “Semi-Annual” which means you won’t get the buggy updates as soon and hopefully Microsoft will have fixed it before it rolls over into the other channels.

Changing Office 365 channel

To change your channel, this is supposed to work but in my tests, it did not:

cd %programfiles%\Common Files\Microsoft Shared\ClickToRun
OfficeC2RClient.exe /changesetting Channel=SemiAnnual
OfficeC2RClient.exe /update user

If this did not work for you either, an alternative method is to use Office Deployment Tool with a XML file.

1. Download the tool from here.
2. Run the downloaded exe and choose a folder to extract the contents. For example, create a folder “C:\OfficeDeployment” and target it.
3. Create a new text file, and copy the content below:

  <Add OfficeClientEdition="64" Channel="SemiAnnual">
    <Product ID="O365ProPlusRetail" >
      <Language ID="en-us" />

4. Save the text file as “C:\OfficeDeployment\config.xml”
5. Open a command prompt as an administrator
6. Execute the commands:

cd C:\OfficeDeployment
setup.exe /configure config.xml

You should see a dialog indicating it’s installing Office. Wait several minutes for it to finish install and then verify your channel has changed.

We are in touch with Microsoft and will keep you updated.