date (2) [DAO]

option explicit

sub date2()

  dim db     as dao.database
  dim mdb_file_name as string 

  mdb_file_name = "c:\temp\date2.mdb"

  kill_file_if_exists(mdb_file_name)

  set db = dao.createDatabase(mdb_file_name, dbLangGeneral, 0)

  dim table_field as dao.field
  dim table_def   as dao.tableDef

  set table_def = db.createTableDef("table_with_dates_2")

  set table_field = table_def.createField("txt", dbText, 42)
  table_def.Fields.append table_field

  set table_field = table_def.createField("mor", dbText, 42)
  table_def.Fields.append table_field

  set table_field = table_def.createField("dt" , dbDate)
  table_def.Fields.append table_field

  db.tableDefs.append table_def

' dim rs as dao.recordSet
' set rs = db.openRecordSet("table_with_dates", dbOpentable)

  db.execute("insert into table_with_dates_2(txt, dt) values ('yyyy-mm-dd'           , #2009-08-28#)")
  db.execute("insert into table_with_dates_2(txt, dt) values ('yyyy-mm-dd hh24:mi:ss', #2009-08-28 10:11:12#)")

  dim str_now as string

  str_now = format(now(), "yyyy-mm-dd hh:nn:ss")

  db.execute("insert into table_with_dates_2(txt, mor, dt) values ('now() as string', '" & str_now & "', #" & str_now & "#)")

'" rs.addNew
'"    rs("txt") = "now()"
'"    rs("dt" ) =  now()
'" rs.update
'
'  rs.addNew
'     rs("txt") = "A year from now"
'     rs("dt" ) =  dateAdd("yyyy", 1, now())
'  rs.update
'
'  rs.addNew
'     rs("txt") = "Two months from now"
'     rs("dt" ) =  dateAdd("m", 2, now())
'  rs.update
'
'  rs.addNew
'     rs("txt") = "Three days from now"
'     rs("dt" ) =  dateAdd("d", 3, now())
'  rs.update
'
'  rs.addNew
'     rs("txt") = "Four hours from now"
'     rs("dt" ) =  dateAdd("h", 4, now())
'  rs.update
'
'  rs.addNew
'     rs("txt") = "Five minutes from now"
'     rs("dt" ) =  dateAdd("n", 5, now()) ' n, not m!
'  rs.update
'
'  rs.addNew
'     rs("txt") = "Six seconds from now"
'     rs("dt" ) =  dateAdd("s", 6, now()) ' n, not m!
'  rs.update

' db.execute("update table_with_dates set upd = 'more than 4 days away' where dateDiff('d', now(), dt) > 4")

' set rs = nothing
  set db = nothing

end sub

private sub kill_file_if_exists(file_name as string)
  dim fso     as  fileSystemObject 
  set fso   = new fileSystemObject

  if fso.fileExists(file_name) then
     fso.deletefile(file_name)
  end if

end sub