Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL CREATE TABLE DATE issue MS Access

Discussion in 'Information Technology' started by Greg McLennan, Jul 28, 2006.

  1. Greg McLennan

    Greg McLennan
    Expand Collapse
    Guest

    I am writing some Visual basic code that needs to create a M$ Access DB
    with a table that has a DATE ROW with dd/mm/yyyy in the Format as you
    would see in the general tab of the table-view.

    e.g sql = "CREATE TABLE DailyTbale ([R_Date] DATE(dd/mm/yyyy)"

    That type of thing. ??
    Any Takers ??
     
  2. Loading...


  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    There's no way to specify the format in SQL. You can do it using DAO. You
    can probably do it using ADOX, if you prefer, though I have not tested that.
    But there is rarely any reason to do so.

    Dates in JET databases are always stored as floating point numbers, the
    format is a purely visual property that does not change the value that is
    actually stored. That's why you can't define it in SQL - the developers of
    SQL were not concerned with visual appearance.

    --
    Brendan Reynolds
    Access MVP

    "Greg McLennan" <wecreate@iinet.net.au> wrote in message
    news:44a90bc2$0$2006$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
    >I am writing some Visual basic code that needs to create a M$ Access DB
    >with a table that has a DATE ROW with dd/mm/yyyy in the Format as you would
    >see in the general tab of the table-view.
    >
    > e.g sql = "CREATE TABLE DailyTbale ([R_Date] DATE(dd/mm/yyyy)"
    >
    > That type of thing. ??
    > Any Takers ??
    >
     
  4. Greg McLennan

    Greg McLennan
    Expand Collapse
    Guest

    Would you have a code example in ADOX, My project is in VB .net v2. And
    the part where I create a new blank database is using ADOX, although
    funny enough I use ADO for the rest of the programming.
    Cheers


    Brendan Reynolds wrote:
    > There's no way to specify the format in SQL. You can do it using DAO. You
    > can probably do it using ADOX, if you prefer, though I have not tested that.
    > But there is rarely any reason to do so.
    >
    > Dates in JET databases are always stored as floating point numbers, the
    > format is a purely visual property that does not change the value that is
    > actually stored. That's why you can't define it in SQL - the developers of
    > SQL were not concerned with visual appearance.
    >
     
  5. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Sorry, no. I tried a Google search, but I'm afraid the result casts some
    doubt on whether this can actually be done using ADOX after all. For
    example, the post at this URL ...

    http://p2p.wrox.com/topic.asp?TOPIC_ID=38840

    .... lists the JET extended properties available via ADOX, and the Format
    property is not one of them. And the article at this URL ...

    http://www.groupacg.com/AtblTip.htm#FORMAT

    .... specifically says it can't be done using ADO. There's an example at the
    same URL of how to do it using DAO.

    Do remember, though, that the Format property is of no use to you unless
    someone is actually going to be looking at that table directly in Access.
    Setting the property will not change the behaviour of your VB.NET program in
    any way.

    --
    Brendan Reynolds
    Access MVP

    "Greg McLennan" <wecreate@iinet.net.au> wrote in message
    news:44a91b84$0$2018$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
    > Would you have a code example in ADOX, My project is in VB .net v2. And
    > the part where I create a new blank database is using ADOX, although funny
    > enough I use ADO for the rest of the programming.
    > Cheers
    >
    >
    > Brendan Reynolds wrote:
    >> There's no way to specify the format in SQL. You can do it using DAO. You
    >> can probably do it using ADOX, if you prefer, though I have not tested
    >> that. But there is rarely any reason to do so.
    >>
    >> Dates in JET databases are always stored as floating point numbers, the
    >> format is a purely visual property that does not change the value that is
    >> actually stored. That's why you can't define it in SQL - the developers
    >> of SQL were not concerned with visual appearance.
    >>
     
  6. Greg McLennan

    Greg McLennan
    Expand Collapse
    Guest

    Brendan Reynolds wrote:
    > Sorry, no. I tried a Google search, but I'm afraid the result casts some
    > doubt on whether this can actually be done using ADOX after all. For
    > example, the post at this URL ...
    >
    > http://p2p.wrox.com/topic.asp?TOPIC_ID=38840
    >
    > ... lists the JET extended properties available via ADOX, and the Format
    > property is not one of them. And the article at this URL ...
    >
    > http://www.groupacg.com/AtblTip.htm#FORMAT
    >
    > ... specifically says it can't be done using ADO. There's an example at the
    > same URL of how to do it using DAO.
    >
    > Do remember, though, that the Format property is of no use to you unless
    > someone is actually going to be looking at that table directly in Access.
    > Setting the property will not change the behaviour of your VB.NET program in
    > any way.
    >

    Thanks for the info. I will look into it today further
     

Share This Page