This article looks at the issue of support both in MUST for translation to support new data types in SQL Server 2008, and the support for these data types within Office products.

MUST Support For New Data Types

The good news it that MUST allows you to explicitly change the mapping of any Access data type to any new SQL Server data type. MUST has two built in configurations, one for SQL Server 2000 and the other for SQL Server 2005/2008. But you can easily over-ride the default choices giving ultimate flexibility in mapping data structures. This is shown later in the article.

Top of Page

New Date and Time Data Types In SQL Server 2008

Traditionally Access date (date and time) fields have been mapped to SQL Server DATETIME fields, this works well as long as you don’t need a date before 1753-01-01 as this is the oldest date that used to be held inside SQL server for the DATETIME data type. However, with SQL Server 2008 several new date and time fields have been introduced. The question is can you and should you use them?

Our advice at this point in time would be, if you don’t need to hold dates before 1753-01-01 then stick with the given default mapping to DATETIME.

However, let us assume that you want to store much older dates but don’t need to hold time and you intend to continue using an Access application linked to SQL Server 2008, then you should consider mapping to the new DATE data type.

There is only one restriction that we have identified here, and that is that when adding existing data to a new DATE type column, it must not contain any time component; for example appending 01/01/1800 00:00:00 will work, but 01/01/1800 12:00:00 will not work.

The good news is that Access through a linked table will see the new DATE data type as a DATE data type, and so you will be able to store and upsize dates from 0001-01-01 to 9999-12-31. As far as we have been able to tell this works well.

What about the other data types TIME, DATETIME2 and DATETIMEOFFSET ? If you are using Access then these data types could be a bad choice, because Access will link the data types as TEXT fields (which is not surprising as Access 2007 came before SQL Server 2008). Also be warned that SQL Server 2005 introduced a new XML data types which are still not supported from Office, so this may not change in the near future. If you are intending to use Visual Studio, then you may find better support going forwards for the new additional date related data types. And for the enthusiast there are always ways around dealing with a field that appears to be of TEXT data type.

The example below illustrates this text translation.

You can also see below how when writing a query the new DATE data type operates correctly but the other new data types translate as a string.

Top of Page

How To Change Data Type Mappings In MUST

The following shows you how to override the default translation of data types in MUST. Using the Options and Settings menu you can directly overtype the translation shown below of dates to DATETIME with DATE.

Secondly MUST has a built in validation which automatically corrects the minimum date time which can be translated, this should be changed to the earliest date that is appropriate to your application, as the default represents the earliest date that we feel is reasonable to allow for (bearing in mind that most applications do not hold historical data over 100 years old).

Top of Page

Other Databases and Dates

Below we have summarised common date limitations in the major databases.

• IBM DB2 : DATE, TIME and TIMESTAMP date range 0001-01-01 to 9999-12-31
• Oracle : DATE (includes time), TIMESTAMP, date range January 1 4713 BC to 9999-12-31
• SQL Server (2000/2005): DATETIME, SMALLDATETIME, date range 1753-01-01 to 9999-12-31 for DATETIME, when no date given defaults to 1st January 1900
• Access : DATE/TIME date range 1-1-100 to 9999-12-31,when no date given defaults to 30th December 1899 and hides date
• SQL Server (2008) : additional types are DATE,TIME,DATETIME2,DATETIMEOFFSET range 0001-01-01 00:00:00:0000000 to 9999-12-31 23:59:59:9999999

Top of Page

rowversion

It is also interesting to note that in the above, the term TIMESTAMP in the above can mean a readable date, as opposed to SQL Server, where TIMESTAMP means a binary flag used to manage optimistic write-back, and now Microsoft are moving away from the terminology here and introducing the more standard term of rowversion; although this transition appears incomplete in SQL Server 2008 where rowversion is supported in DDL, but not through the graphical interface of the management studio as a drop-down option, this remains displayed as timestamp.

Top of Page

Geography Data Type

An even more interesting question, is can Access link to and manipulate the new spatial data types such as the geography data type?

The answer is it will link to a table with the new data type, but display the data type in a binary format. And if you try and edit the binary data it will be un-happy about that.

But, because Access supports pass-through queries, you can be rather clever about this. Below we see a stored procedure which returns a new geography data type in a non binary format.

And now if we use a pass through query to return the data we get something sensible.

Top of Page

MUST has the ability to support yet as un-defined data types in achieving as high a level of translation as possible. In terms of Access, because we have support for the new DATE data type in SQL Server applications dealing with historical data can now benefit from this. And because Access support pass-through queries there are always ways to get around some of the limitations of using a linked table with new data types.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz