I have an Access database from which I run an update query on a SQL Server 2008 database table. This query always worked fine in SQL Server 2005 but now having migrated the database to SQL Server 2008 on a new Windows 2008 server, despite the database running under 2005 mode, the Access query fails when updating a datetime column. We always use the European dd/mm/yy format. The date is taken from an Access textbox with a Shortdate format (dd/mm/yy).
With a date of 1st Sept the date is stored as "2010-01-09.00:00:00:000" which is incorrect. With a date of 31st August I get an error: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value".
I have checked the regional settings in Windows 2008 which are set to Short date= dd/mm/yyyy as I had it on our old server.
The query is as shown in the code snippet. Hope you can help.
Dim DateStarted As Date
DateStarted = Format(Me.txtDateStarted, "dd/mm/yy")
(I have also tried DateStarted = Date)
strSQL = "INSERT INTO [SchDetails] (SchID, SchName, DateStarted, Paid, Dict) " & _
"VALUES (" & schID & " , '" & SchName & "', '" & DateStarted & "', " & Paid & ", 'UK')"