Tip of the Month (February 2008)

Dealing with the "Datetime field overflow (null)" error

If you have a Visual CE form with an edit control connected to date/time column, but the edit control is only displaying the time part of the date/time (not the date), you may encounter the error "[Microsoft][ODBC Microsoft Access Driver]Datetime field overflow (null)". This is caused by a bug in the Microsoft Access ODBC driver. One way to deal with this is described in question V3-J on http://www.syware.com/faq. This tip of the month describes an alternative way around this problem.

First some background information...

The Windows operating system (both handheld and desktop) stores date/times in an 8 byte integer. The value is the number of 100 nano-second intervals since January 1, 1601 (so 0 means midnight 1/1/1601, 1 means 100 nano-seconds after midnight 1/1/1601, 2 means 200 nano-seconds after midnight 1/1/1601, etc.)

Say you have a form with an edit control connected to a date/time field and that edit control is only displaying the time (not the date). When we store a time, we need to set the date part to something. We set it to the most logical thing you would think of: zero (1/1/1601).

Well, in the year 1753, the Pope (the guy in Rome) changed the calendar and removed 10 days in order to make the calendar match the solar year. And, as a consequence, Windows has a difficult time with dates before 1753. In particular, the Access ODBC driver has problems with dates before 1753...and this causes the driver to report the error, "[Microsoft][ODBC Microsft Access Driver]Datetime field overflow(null)"

Interesting enough, this was not a problem in Access 97 ODBC driver. The problem started when Microsoft introduced Access 2000. We reported this to Microsoft (case SRX000121600742) and it took months for them to acknowledge the problem...then it took more months for them to say they would fix it (we had to make a "business case" for them)...then it took months for them to make a patch...then it took months to get permission to distribute the patch. And they promised that in the next major release of Access they would fix it for real. When Access 2003 came out, it was not fixed and when we asked why, they replied it was too hard to fix.

This whole problem only manifests itself with Visual CE forms that have an edit control connected to date/time columns where the control only shows the time part.

More information: Access uses 12/30/1899 as the "no date" date. If you go into Access and type in the data/time of "12/30/1899 7:34", Access will "hide" the date part and just show the time (7:34).

So, we could get around the problem by using 12/30/1899 instead of 1/1/1601. But we can't just start using 12/30/1899. We have many, many users that already have tables full of 1/1/1601's. If we just started using 12/30/1899, and the user compared the old time to the new time, it would be almost 300 years off.

So, there are two "official" solutions to this problem:

1. Don't use time-only edit controls
2. Put in the patch (in question V3-j on http://www.syware.com/faq).

There is one "unofficial" solution also: There is a file called WCEODBC.INI in the windows folder of your desktop machine. Using a text editor (such as notepad), change

     [VICESYNC]

to

     [VICESYNC]
     ConvertAccessNullDates=1

When you make this change to the INI file, every time our synchronizer reads a 12/30/1899 date from the desktop, it will convert it to 1/1/1601; and everytime our synchronizer tries to write a 1/1/1601 date to the desktop, it will write 12/30/1899 instead. This has been tested and it works reliably, but it has the 300 year comparison problem mentioned above (which is not an issue if your database is empty at the time you make the above change to the WCEODBC.INI file).