Access VBA create table and format fields

Show off! <g>

I'd realized 1 a while back, but hadn't bothered updating the sample since I still couldn't get it to work. And while I had a DecimalPlaces property in a sample numeric field I'd created manually, I'd come to the conclusion that it needed to be explicitly added (although the code died before getting to that step.) However, I'm sure you used to be able to create the properties before the field existed in previous versions of Access? Am I mis-remembering?

Jim: To avoid having to know which properties need to be created, vs which can simply be setting, try using code like:

Sub CreateTable()
 
Dim db As DAO.Database
Dim myTable As DAO.TableDef
Dim myField As DAO.Field
 
    Set db = CurrentDb
 
    Set myTable = db.CreateTableDef("TestTable")
    
    With myTable
        .Fields.Append .CreateField("DateD", dbDate)
        .Fields.Append .CreateField("Description", dbText)
        .Fields.Append .CreateField("Num1", dbDouble)
        .Fields.Append .CreateField("Num2", dbDouble)
    End With
   
    db.TableDefs.Append myTable

    Set myField = myTable.Fields("DateD")
    Call SetDAOProperty(myField, "Format", dbText, "Short Date")

    Set myField = myTable.Fields("Num1")
    Call SetDAOProperty(myField, "DecimalPlaces", dbByte, 2)
    Call SetDAOProperty(myField, "Format", dbText, "Standard")

    Application.RefreshDatabaseWindow
    
    Set myField = Nothing
    Set myTable = Nothing
    Set db = Nothing
 
End Sub

Function SetDAOProperty( _
    WhichObject As Object, _
    PropertyName As String, _
    PropertyType As Integer, _
    PropertyValue As Variant _
) As Boolean
On Error GoTo ErrorHandler

Dim prp As DAO.Property

    WhichObject.Properties(PropertyName) = PropertyValue
    WhichObject.Properties.Refresh
    SetDAOProperty = True

Cleanup:
    Set prp = Nothing
    Exit Function

ErrorHandler:
    Select Case Err.Number
        Case 3270 ' "Property not found"
            Set prp = WhichObject.CreateProperty( _
                PropertyName, _
                PropertyType, _
                PropertyValue _
            )
            WhichObject.Properties.Append prp
            WhichObject.Properties.Refresh
            SetDAOProperty = True
        Case Else
            MsgBox Err.Number & ": " & Err.Description
            SetDAOProperty = False
    End Select
    Resume Cleanup
   
End Function

Sub CreateTable()

Dim myTable As TableDef
Dim myField As DAO.Field
Dim myProp As DAO.Property

Set myTable = CurrentDb.CreateTableDef("TestTable")

Set myField = myTable.CreateField("DateD", dbDate)
Set myProp = MyField.CreateProperty("Format", dbText, "Short Date")
myField.Properties.Append myProp
myTable.Fields.Append myField

myTable.Fields.Append myTable.CreateField("Description", dbText)

Set myField = myTable.CreateField("Num1", dbDouble)
Set myProp = myField.CreateProperty("Format", dbText, "Standard")
myField.Properties.Append myProp
myField.Properties("DecimalPlaces") = 2
myTable.Fields.Append myField

myTable.Fields.Append myTable.CreateField("Num2", dbDouble)

CurrentDb.TableDefs.Append myTable
Application.RefreshDatabaseWindow

End Sub

The Format property doesn't exist until you create it. The DecimalPlaces property does exist.

Couple of things:

1. You need to use the same database object for manipulating your tabledef, and make sure that object remains in existence while you do it.  Eadch invocation of CurrentDb returns a new and distinct Database object.  Since TableDef objects don't contain an internal reference to their parent database objects, a usage like this:

    Set myTable = CurrentDb.CreateTableDef("TestTable")

... causes the database object to go out of scope as soon the statement is through executing.

Instead, define a Database object variable and use that throughout the procedure:

    Dim db As DAO.Database

    Set db = CurrentDb

    Set myTable = db.CreateTableDef("TestTable")

    ' ...

    db.TableDefs.Append myTable

2. DecimalPlaces is not an inherent property of Field, so you have to create it as you do the Format property.  The data type of DecimalPlaces should be dbByte.

3. The non-innate properties can't be created until the field has been appended to the tabledef and the tabledef has been appended to the database's TableDefs collection.  Try this code:

'------ start of code ------
Sub CreateTable()
 
    Dim db As DAO.Database
    Dim myTable As DAO.TableDef
    Dim myField As DAO.Field
    Dim myProp As DAO.Property
 
    Set db = CurrentDb
 
    Set myTable = db.CreateTableDef("TestTable")
    
    With myTable
        .Fields.Append .CreateField("DateD", dbDate)
        .Fields.Append .CreateField("Description", dbText)
        .Fields.Append .CreateField("Num1", dbDouble)
        .Fields.Append .CreateField("Num2", dbDouble)
    End With
   
    db.TableDefs.Append myTable

    Set myField = myTable.Fields("DateD")
    Set myProp = myField.CreateProperty("Format", dbText, "Short Date")
    myField.Properties.Append myProp

    Set myField = myTable.Fields("Num1")
    Set myProp = myField.CreateProperty("DecimalPlaces", dbByte, 2)
    myField.Properties.Append myProp
    Set myProp = myField.CreateProperty("Format", dbText, "Standard")
    myField.Properties.Append myProp

    Application.RefreshDatabaseWindow
    
    Set myProp = Nothing
    Set myField = Nothing
    Set myTable = Nothing
    Set db = Nothing
 
End Sub
'------ end of code ------