vba - 创建具有访问 VBA 和格式字段的表

Question

我想使用带有格式化字段的 access vba 创建一个表。创建表格时如何使用“标准”格式格式化货币字段？

Private Sub CreateTable_Click()

DoCmd.RunSQL "CREATE TABLE Test( " & _
          "[id] AUTOINCREMENT PRIMARY KEY, " & _
          "[transaction_date] DATE, " & _
          "[reference] TEXT(255)," & _
          "[details] TEXT(255)," & _
          "[debit] CURRENCY," & _
          "[credit] INT);"

End Sub

标签： vbams-access

Answer 1

我们为此使用DAO 。

这是一个例子。在底部，将格式分配给字段：

' Creates the local tables and indexes from scratch if missing.
' Returns True if success, False if not.
'
' 2017-11-14. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function CreateLocalDataTable( _
    ByVal TableName As String) _
    As Boolean

    Dim Database    As DAO.Database
    Dim Table       As DAO.TableDef
    Dim Field       As DAO.Field
    Dim Index       As DAO.Index
    Dim Property    As DAO.Property

    Dim Result      As Boolean

    Set Database = CurrentDb

    For Each Table In Database.TableDefs
        If Table.Name = TableName Then
            ' Table exists. Exit.
            Result = True
            Exit For
        End If
    Next
    If Table Is Nothing Then
        ' Create table.
        Select Case TableName
            Case "Country"
                Set Table = Database.CreateTableDef(StrConv(TableName, vbProperCase))
                    Set Field = Table.CreateField("Code", dbText, 2)
                    Field.AllowZeroLength = False
                    Field.Required = True
                Table.Fields.Append Field
                    Set Field = Table.CreateField("Name", dbText, 45)
                    Field.AllowZeroLength = False
                    Field.Required = True
                Table.Fields.Append Field
                    Set Index = Table.CreateIndex("PrimaryKey")
                        Set Field = Index.CreateField("Code")
                        Index.Fields.Append Field
                        Index.Primary = True
                Table.Indexes.Append Index
            Case "Zone"
                Set Table = Database.CreateTableDef(StrConv(TableName, vbProperCase))
                    Set Field = Table.CreateField("ZoneId", dbInteger)
                    Field.Required = True
                Table.Fields.Append Field
                ' Don't create an index on CountryCode as this will
                ' be created when creating referential integrity.
                    Set Field = Table.CreateField("Name", dbText, 35)
                    Field.AllowZeroLength = False
                    Field.Required = True
                Table.Fields.Append Field
                    Set Index = Table.CreateIndex("PrimaryKey")
                        Set Field = Index.CreateField("ZoneId")
                        Index.Fields.Append Field
                        Index.Primary = True
                Table.Indexes.Append Index
                    Set Index = Table.CreateIndex("CountryCode")
                        Set Field = Index.CreateField("CountryCode")
                        Index.Fields.Append Field
                Table.Indexes.Append Index
                    Set Index = Table.CreateIndex("Name")
                        Set Field = Index.CreateField("Name")
                        Index.Fields.Append Field
                Table.Indexes.Append Index
            Case "Timezone"
                Set Table = Database.CreateTableDef(StrConv(TableName, vbProperCase))
                    Set Field = Table.CreateField("ZoneId", dbInteger)
                    Field.Required = True
                Table.Fields.Append Field
                    Set Field = Table.CreateField("Abbreviation", dbText, 6)
                    Field.AllowZeroLength = False
                    Field.Required = True
                Table.Fields.Append Field
                    Set Field = Table.CreateField("TimeStart", dbCurrency)
                    Field.Required = True
                Table.Fields.Append Field
                    Set Field = Table.CreateField("Offset", dbLong)
                    Field.Required = True
                    Field.DefaultValue = "0"
                Table.Fields.Append Field
                    Set Field = Table.CreateField("Dst", dbBoolean)
                    Field.DefaultValue = "False"
                Table.Fields.Append Field
                ' Don't create an index on ZoneId as this will
                ' be created when creating referential integrity.
                    Set Index = Table.CreateIndex("Abbreviation")
                        Set Field = Index.CreateField("Abbreviation")
                        Index.Fields.Append Field
                Table.Indexes.Append Index
                    Set Index = Table.CreateIndex("TimeStart")
                        Set Field = Index.CreateField("TimeStart")
                        Index.Fields.Append Field
                Table.Indexes.Append Index
        End Select
        If Not Table Is Nothing Then
            ' Append table.
            Database.TableDefs.Append Table

            ' Append properties.
            Select Case TableName
                Case "Timezone"
                    Set Field = Table.Fields("TimeStart")
                        Set Property = Field.CreateProperty("Format", dbText)
                        Property.Value = "0"
                    Field.Properties.Append Property
            End Select

            Result = (Err.Number = ErrorNone)
        End If
    End If

    CreateLocalDataTable = Result

End Function