Using new data types in 2008
1 / 35

Using New Data Types in 2008 - PowerPoint PPT Presentation

  • Uploaded on

Using New Data Types in 2008. Andrew Couch UK Access User Group ASC associates Flow of Presentation. Data types and products Share Point : Access & SQL Server SQL Server : Access & Visual Studio SQL Server New Data Types

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Using New Data Types in 2008' - eliora

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Using new data types in 2008

Using New Data Types in 2008

Andrew Couch

UK Access User Group

ASC associates

Flow of presentation
Flow of Presentation

  • Data types and products

  • Share Point : Access & SQL Server

  • SQL Server : Access & Visual Studio

  • SQL Server New Data Types

  • Updating Data & Multi-user Issues

  • Summary

Why are we interested in data types
Why are we interested in Data Types?

  • What we do with data

    • Exchange

      • Converting and exchanging data between products

    • Applications that present data

      • Linking applications to back-end data sources

    • Applications that maintain data

      • updating data with applications linked to back-end data source

Which products will we consider
Which Products will we consider?

  • Products

    • Access

      • Application and data source

    • SQL Server

      • data source and application components, also report services

    • Share Point

      • Application and data source (SQL Server)

    • Visual Studio

      • Application

What data types do the products support
What Data Types do the Products support?

SQL Server (2008)

Access (2007)

Visual Studio (2005/8)


Multi-value fields

Share Point (2003)see next slide

n prefix for Unicode

Share point under the hood
Share Point under the hood

Data is held in the AllUserData table which has a generic set of fields,


Access to share point data types moss
Access to Share PointData Types; MOSS

Access 2007 share point
Access (2007) & Share Point

  • Attachments (share point lists)

  • Multi-value fields (share point lists)

Share point a data object model wss
Share Point a Data/Object Model; WSS


Access to sql server data type mappings
Access to SQL Server Data Type Mappings

  • Yes/No (boolean) : bit

    • Access True -1, SQL Server True 1; also SQL Server boolean is tri-state 0, 1 and NULL

  • Text : varchar or nvarchar or char or nchar

  • Date/Time : datetime

  • Currency : money

  • Autonumber : int (identity property)

  • OLE Object : image, varbinary, filestream

  • Memo : text or varchar(max)

  • Number : various mappings

Yes no without a default translated to sql server
Yes/No (without a default)Translated To SQL Server

Visual studio images and ole still a problem with varbinary max
Visual Studio, Images and OLE still a problem with Varbinary(max)

some people would suggest that image data should not directly held in the

database and instead a text link is used to point to an external image file

which works around this issue

Mini summary
Mini-summary Varbinary(max)

  • Access to SQL Server

    • MEMO to VARCHAR(max) or TEXT, probably TEXT safer bet but VARCHAR(max) more flexible


  • Visual Studio

    • VARCHAR(max) a better choice

    • OLE/IMAGE data doesn’t work for bound controls

Sql server 2005
SQL Server (2005) Varbinary(max)

  • XML data type

    • office requires program code using new SQL Native Client drivers to manipulate this

  • varchar(max), nvarchar(max)

    • advantages for programmers as can use as a variable (not possible with older text data type)

  • varbinary(max)

Date time data
Date & Time Data Varbinary(max)

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

    • DATE, TIME,DATETIME2, DATETIMEOFFSET range 0001-01-01 00:00:00:0000000 to 9999-12-31 23:59:59:9999999

Searching Varbinary(max)

  • some limitations where fields are strings

Sql server 2008
SQL Server (2008) Varbinary(max)

  • Filestream

    • Unstructured large object data

  • DATE and TIME

    • Dates from 01/01/0001 to 31/12/9999

    • Times accurate to 00:00:00:0000000


    • New combined date and time data types with higher accuracy, Combines above, offset type includes time-zone data


    • Optimised for data hierarchies (special functions)


    • .NET-based spatial data

    • First deals with round-earth co-ordinates, second uses a flat-earth style of co-ordinates

    • Special functions

Geography Varbinary(max)

  • It comes as no surprise that this renders in a binary type format and you can’t edit it

  • Stored procedures and pass through queries to the rescue!

With a little magic
With a little Magic Varbinary(max)

Geography methods
Geography Methods Varbinary(max)

Timestamps multiple users
Timestamps & Multiple Users Varbinary(max)


data when





row versioning



Visual Studio does not utilise timestamps on bound controls the new LINQ will support timestamp concurrency

Access row versioning timestamps
Access Row Versioning & Timestamps Varbinary(max)

Row Versioning


UPDATE "dbo"."authors"

SET "au_fname"=?

WHERE "au_id" = ? AND "au_lname" = ? AND "au_fname" = ?

AND "phone" = ? AND "address" = ? AND "city" = ? AND "state" = ? AND "zip" = ? AND "contract" = ?



UPDATE "dbo"."authors"

SET "au_fname"=?

WHERE "au_id" = ? AND "ts" = ?

Timestamps improve efficiency and remove potential problems

with rounding errors in numerical data

Example visual studio update handling null values
Example Visual Studio Update handling Null Values Varbinary(max)

UpdateCommand = "UPDATE [Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID AND [CompanyName] = @original_CompanyName AND COALESCE([ContactName],'') = COALESCE(@original_ContactName,'') AND COALESCE([ContactTitle],'') = COALESCE(@original_ContactTitle,'') AND COALESCE([Address],'') = COALESCE(@original_Address,'') AND COALESCE([City],'') = COALESCE(@original_City,'') AND COALESCE([Region],'') = COALESCE(@original_Region,'') AND COALESCE([PostalCode],'') = COALESCE(@original_PostalCode,'') AND COALESCE([Country],'') = COALESCE(@original_Country,'') AND COALESCE([Phone],'') = COALESCE(@original_Phone,'') AND COALESCE([Fax],'') = COALESCE(@original_Fax,'')"

Summary Varbinary(max)

  • We have a diverse set of data types in the different products

  • Need to test data types for product compatibility, don’t forget different versions and drivers can have an impact

  • Access and Share Point very good compatibility

  • Access and SQL Server good compatibility

  • Share Point data easily exported and linked MOSS but WSS more difficult

  • Please, Please can I have a nice tree presentation for XML data, then I may be tempted to use it!!!!!!

  • SQL Server 2008 new data types hold great promise, and it will possible to do a lot with then in Office 2007

Asc associates
ASC Associates Varbinary(max)

  • Software consultancy specialising in Access, SQL Server and .net applications

  • Migration Upsizing SQL Tool

    • MUST

      • upsizes Access databases to SQL Server

    • MUST+SQL

      • converts Access queries to views and stored procedures

    • MUST+Web

      • converts Access forms to web forms in Visual Studio projects