创建带有访问 VBA 和格式字段的表 [英] Create table with access VBA and format fields
本文介绍了创建带有访问 VBA 和格式字段的表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想使用带有格式化字段的 access vba 创建一个表.创建表格时如何使用标准"格式设置货币字段的格式?
I want to create a table using access vba with formatted fields. How do I format currency field with "standard" format while creating the table?
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
推荐答案
我们使用 DAO 来解决这个问题.
We use DAO for this.
这是一个例子.在底部,一个格式被分配给一个字段:
Here's an example. At the bottom, a Format is assigned to a field:
' 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
这篇关于创建带有访问 VBA 和格式字段的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文