Search code examples
sqldatetimems-accesscreate-table

Specify Date/Time Field Format through SQL in MS Access

In MS Access, I am requiring to create automatically tables on demand (about 100 maybe more).

SQL 'CREATE TABLE' command can handle this well, though I need to format a Date/Time Field, through an expression like this:

CREATE TABLE myTable (ID INTEGER, T DATETIME CONSTRAINT CT PRIMARY KEY, X DOUBLE, S CHAR)

The created table Field Properties look like this:

Created Table

But i've not found a way to set the "Format" field property, to be "yyyy-mm-dd hh:nn:ss":

Required Table

Is there a way to specify this, through an SQL Expression, not using MS Access Macros?

Best wishes,

Solution

  • The DDL in Access is useful but pretty limited and I don't think you can define a field's format with it.

    To access all of the properties on a table, you'll need to drop down to VBA, using DAO:

    Public Sub UpdateFormat(tableName As String, fieldName As String, format As String)
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim fd As DAO.Field

    Set db = CurrentDb()
    Set tb = db.TableDefs(tableName)
    Set fd = tb.Fields(fieldName)
    fd.Properties("Format").Value = format
End Sub

    It's just to show you how it works, you should probably add some error checking as it will throw errors if you try to access a field that doesn't support the Format property.

    To use it:

     UpdateFormat "PO", "RateDate", "dd mmm yyyy"