SQL CREATE TABLE DATE issue MS Access

[Greg McLennan]

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 ??

 

[Brendan Reynolds]

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.

 

[Greg McLennan]

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]

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.

 

[Greg McLennan]

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