Back to Index

 

How to handle the MS Access DateTime field

MS Access DateTime fields can be strange to work with.   You cannot simply build a SQL statement with a simple date such as '01/01/2004'.  To MS Access this is a string and you will receive a Datatype mismatch error, whereas most other databases would recognize this as a Date and insert or update the column appropriately.   

For MS Access databases, you have to wrap your DateTime fields in a # sign.  For instance, #01/01/2004#.    BlueSky Integration Studio is designed to recognize this and automatically reformat the SQL statement for MS Access Databases!   

In your DateTime columns, double-click on the Join object, then click the code button for your DateTime column mapping and wrap your column in the following code:

For instance, if your column name is a1.MyDate

If a1.MyDate = "" Then

     Return Nothing          ' This will format the SQL statement to set the column to ANSI Standard NULL

Else

     Return "#" + a1.MyDate + "#"

End If

 


© 2003 - 2007 Relational Solutions, Inc. - All rights reserved