0

Is there a known failure mode of a MS Access 2000 database, when data is refused (or silently discarded, which is worse) to be inserted into a table containing a Date/Time type of field with default value of =Now() ?

The date/time field in question is not indexed or required;

But when an INSERT query is sent to the database, it looks like the =Now() function fails - and the data is not written to the table (however another auto-increment field lookup is executed, because, when later on =Now() succeeds, there is a gap in auto-increments, equal to the number of times the query was run)

e.g. i see in the table

ID | Data | Timestamp
5  | foo  | 11/15/2016 17:15:00
1  | foo  | 11/15/2016 17:11:00

when an INSERT INTO TheTable ([Data]) VALUES (foo) is ran every minute and the problem happens on run 2, 3 and 4. Eventually after some time, it succeeds (as shown with ID=5)

Why do I think it could be the =Now() problem ?

Because same/similar failure happens if computer clock is changed /backwards/ (e.g. during DST adjustment).

But it has recently happened just out of the blue, not being able to write data to that table for a couple of hours, when the DST adjustment has actually happened already.

(the program itself is not being told of the query failure and charges forward as if nothing happened - some debugging effort is pending still)

I looked about SO and wonder if this Table Field Default Property Values Functions Not Working Anymore in Microsoft Access 2010 may have something to do with it? However the program and the database communicate via ODBC Microsoft Access (*.mdb) driver (yes MS Office 2000 files...)

Hope this makes sense, Kind Regards...

Community
  • 1
  • 1
cidjen
  • 1
  • 2

1 Answers1

1

Have never heard of such issue, but why not just adjust your SQL:

INSERT INTO TheTable ([Data], [Timestamp]) VALUES (foo, Now())
Gustav
  • 43,657
  • 6
  • 27
  • 48
  • That I'm going to. – cidjen Nov 16 '16 at 12:08
  • Also maybe create a test/fallback routine that verifies whether the row has been inserted, and tries to insert it again if it can't be retrieved, but using its own way to retrieve current date/time. It's hard to debug this, cause it never happens on the test bed... Anyway, thanks ! – cidjen Nov 16 '16 at 12:17