Access Table ColumnWidth property sometimes works...?

[Medwar19]

Hi all,

I'm creating my Access tables using VBA and SQL.

strSQL = "CREATE TABLE [<tblname>] (<fieldname> AUTOINCREMENT)

docmd.runsql strSQL

I then want set each fields columnwidth property using:

db.TableDefs(<tblname>).Fields(<fldname>).Properties("ColumnWidth") = <width in twips>

However I get a "property not found error". It appears the columnwidth property is missing from the item tree.

However if opening the table in Access, manually adjust the column width by dragging the column bar then I can set the columnwidth in VBA using the string above??

Any ideas how to set/create the columnwidth property in VBA???

Thanks,

Mark.

[Medwar19]

So I figured out how to do this myself:

Public Sub CreateDatabase(Optional InstID As String)
   'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fl As DAO.Field
Dim pr1 As DAO.Property
Dim FieldStr(), FieldType(), FieldWidth() As Variant
Dim FldIndex As Integer

    FieldStr = Array("LogID", "InstID", "LogDateTime", "PC_Err_Codes", "Assay", "RAW")
    FieldType = Array("AUTOINCREMENT", "varchar(30)", "Datetime", "varchar(15)", "varchar(16)", "MEMO")
    FieldWidth = Array(1000, 2200, 2400, 1600, 1000, 25000)
    
    Set db = DBEngine.CreateDatabase(DB_DIR & InstID & ".accdb", dbLangGeneral)
    Set db = OpenDatabase(DB_DIR & InstID & ".accdb")
    'Create default Fields in table
    strSQL = "CREATE TABLE [" & InstID & "] (" & _
             "" & FieldStr(0) & " " & FieldType(0) & ", " & _
             "" & FieldStr(1) & " " & FieldType(1) & ", " & _
             "" & FieldStr(2) & " " & FieldType(2) & ", " & _
             "" & FieldStr(3) & " " & FieldType(3) & ", " & _
             "" & FieldStr(4) & " " & FieldType(4) & ", " & _
             "" & FieldStr(5) & " " & FieldType(5) & ");"
    db.Execute strSQL
        
    'Set the column Widths for each field using VBA
    Set td = db.TableDefs(InstID)
    For FldIndex = 0 To UBound(FieldStr)
        Set fl = td.Fields(FieldStr(FldIndex))
        Set pr1 = fl.CreateProperty("ColumnWidth", DAO.DataTypeEnum.dbInteger, 3)
        fl.Properties.Append pr1
        fl.Properties("ColumnWidth") = FieldWidth(FldIndex)
    Next
    db.Close
End Sub