using new data types in 2008 l.
Skip this Video
Download Presentation
Using New Data Types in 2008

Loading in 2 Seconds...

play fullscreen
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' - medwin

Download Now 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 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
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
  • 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)
  • 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
  • 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
  • some limitations where fields are strings
sql server 2008
SQL Server (2008)
  • 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
  • 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!
timestamps multiple users
Timestamps & Multiple Users


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

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

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,'')"

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