Cannot seem to find the right format to provide to a MS Access DB for the Date/Time Field.
Table Name: holiday
holiday field names in order: record(type: int), holiday(type: text), include(type: yes/no), dateValue(type ate/Time).
If I remove the dateValue field from the table the SQL insert statement works fine so this reduces the problem to strictly how I am providing information for the dateValue field.
//====================================================================== int recNum=1; boolean include=true; String name="New Years Day Observed"; java.sql.Date temp=new java.sql.Date(new java.util.Date().getTime());
String s1="Insert into holiday values ("+recNum+", '"+name+"',"+include+", #"+ temp+"#)"; //=======================================================================
I then pass the s1 as the sql to my DB Accessor class which handles the JDBC-ODBC function of preparing the statement, connection, etc.
I have tried single quotes, 'temp', single pound, #temp#, and double pound, ##temp##, to no avail.
I am currently using Java 1.5, a JDBC-ODBC bridge, and MS Access 2000 (Ver 9.0).
I hope I have provided the pertinent information and eagerly await your help. I have wrestled with this since last Thursday and have exhausted all resources I am aware of.
For some reason, this was preventing the update to the dB from showing. Once I did this, everything worked fine.
Do you have any ideas as to why keeping the connection open preventing me from seeing the rows added to the dB? Or is this a quirk associated with Acccess? I am guessing that the table was locked due to the open connection and therefore not accepting new inserts....?
Again, thanks tons for your quick response.
Hope you have a great day!
author & internet detective
Some drivers don't do the database commit until you close the connection. I don't enough about Access to say whether this is the case here. But it is a possible explanation for why you weren't seeing the updates.
subject: How to insert date into MS Access Date/Time?