MS Access database insert succeeds only if date is entered after 12th of every month

There was an issue of entering a record in a MS access table having date field. The issue comes only if date is less than 12th of month. For all other date, insertion was working fine. Also insertion was successful if month and day parts of a date are same.

The issue was related to how MS Access database stores date internally. By default it stores date in US/English format (i.e. mmddyy). We had used ADO for updating database. The system date was converted to variant date before insertion. This way we did not had any control on the formatting of the date.

Instead we changed the query to specify date using date formatter (#mm/dd/yyyy#) while performing insert or update operations. This method ensured that the date is always specified in mmddyyyy format no matter what is the locale of application or OS.