Option Compare Database
Option Explicit
' Created 04/06/2020
' Modified 04/06/2020
' Project
' Model
' Company
' Author
' Version
' Database Access
'=======================================================
'=== MS Access database creation method
'===
'=== 1. Create a new database in the MS Access
'=== 2. Create a new module
'=== 3. Copy this SQL script output into the new MS Access 2000 module
'=== 4. Select from main menu "Tools" item "References..." and check the "Microsoft DAO 3.6 Object Library."
'=== 5. Place your mouse cursor somewhere in the main procedure Main()
'=== 6. Run the module code (Click the "Run Sub/UserForm" button or press F5)
'=======================================================
Public dbs As DAO.Database
Public tdf As DAO.TableDef
Public idx As DAO.Index
Public rel As DAO.Relation
Sub Main()
Set dbs = CurrentDb()
On Error GoTo ErrorHandler
Call BeforeScript
Call DropQueries
Call DropTables
Call DropIndexes
Call CreateTables
Call CreatePrimaryKeys
Call CreateIndexes
Call CreateAlterKeys
Call CreateRelations
Call CreateQueries
Call AfterScript
MsgBox "Script successfully processed.", vbInformation
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 3010
MsgBox "Table " & tdf.Name & " already exist!", vbInformation
Err.Clear
Case 3284
MsgBox "Index " & idx.Name & " for table " & tdf.Name & " already exist!", vbInformation
Err.Clear
Case Else
MsgBox Err.Description, vbCritical
End Select
End Sub
' Drop queries
'===============
Sub DropQueries()
End Sub
' Drop indexes
'==============
Sub DropIndexes()
'Drop all non unique indexes
End Sub
' Drop tables
'===============
Public Sub DropTables()
'Drop relations
Call DropRelation("R14")Call DropRelation("R06")Call DropRelation("R13")Call DropRelation("R09")Call DropRelation("R05")Call DropRelation("R11")Call DropRelation("R15")Call DropRelation("R16")Call DropRelation("R17")Call DropRelation("R12")Call DropRelation("R10")Call DropRelation("R03")Call DropRelation("R02")Call DropRelation("R07")Call DropRelation("R04")Call DropRelation("R01")
'Drop tables
Call DropTable("ensayos_postaplicaciones_fotos")Call DropTable("ensayos_postaplicaciones")Call DropTable("ensayos_preaplicaciones_fotos")Call DropTable("ensayos_tratamientos_repeticiones")Call DropTable("ensayos_tratamientos")Call DropTable("tecnicos")Call DropTable("ensayos_preaplicaciones")Call DropTable("unidades")Call DropTable("equipos")Call DropTable("ensayos")Call DropTable("productos")Call DropTable("tipos_aplicaciones")Call DropTable("variedades")Call DropTable("cultivos")Call DropTable("fincas")Call DropTable("empresas")Call DropTable("agentes_nocivos")
End Sub
' Create tables
'===============
Sub CreateTables()
Call CreateTable1 'agentes_nocivos
Call CreateTable2 'empresas
Call CreateTable3 'fincas
Call CreateTable4 'cultivos
Call CreateTable5 'variedades
Call CreateTable6 'tipos_aplicaciones
Call CreateTable7 'productos
Call CreateTable8 'ensayos
Call CreateTable9 'equipos
Call CreateTable10 'unidades
Call CreateTable11 'ensayos_preaplicaciones
Call CreateTable12 'tecnicos
Call CreateTable14 'ensayos_tratamientos
Call CreateTable15 'ensayos_tratamientos_repeticiones
Call CreateTable16 'ensayos_preaplicaciones_fotos
Call CreateTable17 'ensayos_postaplicaciones
Call CreateTable18 'ensayos_postaplicaciones_fotos
End Sub
'=== Create table agentes_nocivos ======
Sub CreateTable1()
Set tdf = dbs.CreateTableDef("agentes_nocivos")
Call AddFieldToTable("id", dbLong, 0, 0, "0", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table empresas ======
Sub CreateTable2()
Set tdf = dbs.CreateTableDef("empresas")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table fincas ======
Sub CreateTable3()
Set tdf = dbs.CreateTableDef("fincas")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table cultivos ======
Sub CreateTable4()
Set tdf = dbs.CreateTableDef("cultivos")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table variedades ======
Sub CreateTable5()
Set tdf = dbs.CreateTableDef("variedades")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table tipos_aplicaciones ======
Sub CreateTable6()
Set tdf = dbs.CreateTableDef("tipos_aplicaciones")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("name", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table productos ======
Sub CreateTable7()
Set tdf = dbs.CreateTableDef("productos")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table ensayos ======
Sub CreateTable8()
Set tdf = dbs.CreateTableDef("ensayos")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("agente_nocivo_id", dbLong, 0, 0, "0", "", "", True, False)Call AddFieldToTable("cultivo_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("variedad_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("empresa_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("finca_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("semana_transplante", dbInteger, 0, 0, "0", "", "", True, False)Call AddFieldToTable("tecnico_id", dbLong, 0, 0, "0", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table equipos ======
Sub CreateTable9()
Set tdf = dbs.CreateTableDef("equipos")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table unidades ======
Sub CreateTable10()
Set tdf = dbs.CreateTableDef("unidades")
Call AddFieldToTable("id", dbLong, 0, 0, "0", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table ensayos_preaplicaciones ======
Sub CreateTable11()
Set tdf = dbs.CreateTableDef("ensayos_preaplicaciones")
Call AddFieldToTable("ensayo_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("fecha_aplicacion", dbDate, 0, 0, "", "", "", True, False)Call AddFieldToTable("hora_aplicacion", dbDate, 0, 0, "", "", "", True, False)Call AddFieldToTable("semana_aplicacion", dbInteger, 0, 0, "0", "", "", True, False)Call AddFieldToTable("caldo_hectarea", dbDouble, 0, 0, "0", "", "", True, False)Call AddFieldToTable("equipo_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("tipo_aplicacion_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("comentarios", dbMemo, 0, 0, "", "", "", False, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table tecnicos ======
Sub CreateTable12()
Set tdf = dbs.CreateTableDef("tecnicos")
Call AddFieldToTable("id", dbLong, 0, 0, "0", "", "", True, False)Call AddFieldToTable("nombre", dbText, 50, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table ensayos_tratamientos ======
Sub CreateTable14()
Set tdf = dbs.CreateTableDef("ensayos_tratamientos")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("producto_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("dosis", dbDouble, 0, 0, "0", "", "", True, False)Call AddFieldToTable("unidad_id", dbLong, 0, 0, "0", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table ensayos_tratamientos_repeticiones ======
Sub CreateTable15()
Set tdf = dbs.CreateTableDef("ensayos_tratamientos_repeticiones")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("producto_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("fecha", dbDate, 0, 0, "", "", "", True, False)Call AddFieldToTable("dosis", dbDouble, 0, 0, "0", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table ensayos_preaplicaciones_fotos ======
Sub CreateTable16()
Set tdf = dbs.CreateTableDef("ensayos_preaplicaciones_fotos")
Call AddFieldToTable("ensayo_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("foto_id", dbLong, 0, 0, "0", "", "", True, False)Call AddFieldToTable("foto", dbText, 255, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table ensayos_postaplicaciones ======
Sub CreateTable17()
Set tdf = dbs.CreateTableDef("ensayos_postaplicaciones")
Call AddFieldToTable("ensayo_id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("fecha", dbDate, 0, 0, "", "", "", True, False)Call AddFieldToTable("hora", dbDate, 0, 0, "", "", "", True, False)Call AddFieldToTable("dias", dbLong, 0, 0, "0", "", "", True, False)Call AddFieldToTable("comentarios", dbMemo, 0, 0, "", "", "", False, False)
dbs.TableDefs.Append tdf
End Sub
'=== Create table ensayos_postaplicaciones_fotos ======
Sub CreateTable18()
Set tdf = dbs.CreateTableDef("ensayos_postaplicaciones_fotos")
Call AddFieldToTable("id", dbLong, 0, 0, "", "", "", True, False)Call AddFieldToTable("foto_id", dbLong, 0, 0, "0", "", "", True, False)Call AddFieldToTable("foto", dbText, 255, 0, "", "", "", True, False)
dbs.TableDefs.Append tdf
End Sub
' Create primary keys
'=====================
Sub CreatePrimaryKeys()
'=== Create primary key for table agentes_nocivos ======
Set tdf = dbs.TableDefs("agentes_nocivos")Set idx = tdf.CreateIndex("pk_agentes_nocivos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table empresas ======
Set tdf = dbs.TableDefs("empresas")Set idx = tdf.CreateIndex("pk_empresas")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table fincas ======
Set tdf = dbs.TableDefs("fincas")Set idx = tdf.CreateIndex("pk_fincas")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table cultivos ======
Set tdf = dbs.TableDefs("cultivos")Set idx = tdf.CreateIndex("pk_cultivos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table variedades ======
Set tdf = dbs.TableDefs("variedades")Set idx = tdf.CreateIndex("pk_variedades")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table tipos_aplicaciones ======
Set tdf = dbs.TableDefs("tipos_aplicaciones")Set idx = tdf.CreateIndex("pk_tipos_aplicaciones")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table productos ======
Set tdf = dbs.TableDefs("productos")Set idx = tdf.CreateIndex("pk_productos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table ensayos ======
Set tdf = dbs.TableDefs("ensayos")Set idx = tdf.CreateIndex("pk_ensayos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table equipos ======
Set tdf = dbs.TableDefs("equipos")Set idx = tdf.CreateIndex("pk_equipos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table unidades ======
Set tdf = dbs.TableDefs("unidades")Set idx = tdf.CreateIndex("pk_unidades")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table ensayos_preaplicaciones ======
Set tdf = dbs.TableDefs("ensayos_preaplicaciones")Set idx = tdf.CreateIndex("pk_ensayos_preaplicaciones")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("ensayo_id", False)
tdf.Indexes.Append idx
'=== Create primary key for table tecnicos ======
Set tdf = dbs.TableDefs("tecnicos")Set idx = tdf.CreateIndex("pk_tecnicos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
'=== Create primary key for table ensayos_tratamientos ======
Set tdf = dbs.TableDefs("ensayos_tratamientos")Set idx = tdf.CreateIndex("pk_ensayos_tratamientos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)Call AddFieldToIndex("producto_id", False)
tdf.Indexes.Append idx
'=== Create primary key for table ensayos_tratamientos_repeticiones ======
Set tdf = dbs.TableDefs("ensayos_tratamientos_repeticiones")Set idx = tdf.CreateIndex("pk_ensayos_tratamientos_repeticiones")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)Call AddFieldToIndex("producto_id", False)Call AddFieldToIndex("fecha", False)
tdf.Indexes.Append idx
'=== Create primary key for table ensayos_preaplicaciones_fotos ======
Set tdf = dbs.TableDefs("ensayos_preaplicaciones_fotos")Set idx = tdf.CreateIndex("pk_ensayos_preaplicaciones_fotos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("ensayo_id", False)Call AddFieldToIndex("foto_id", False)
tdf.Indexes.Append idx
'=== Create primary key for table ensayos_postaplicaciones ======
Set tdf = dbs.TableDefs("ensayos_postaplicaciones")Set idx = tdf.CreateIndex("pk_ensayos_postaplicaciones")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("ensayo_id", False)
tdf.Indexes.Append idx
'=== Create primary key for table ensayos_postaplicaciones_fotos ======
Set tdf = dbs.TableDefs("ensayos_postaplicaciones_fotos")Set idx = tdf.CreateIndex("pk_ensayos_postaplicaciones_fotos")
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex("id", False)
tdf.Indexes.Append idx
End Sub
' Create indexes
'================
Sub CreateIndexes()
End Sub
' Create alter keys (unique indexes in MS ACCESS)
'================================================
Sub CreateAlterKeys()
End Sub
' Create relations
'==================
Sub CreateRelations()
'=== Create relations between parent table agentes_nocivos and child table ensayos ======
Set rel = dbs.CreateRelation("R01")
rel.Table = "agentes_nocivos"
rel.ForeignTable = "ensayos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "agente_nocivo_id")
dbs.Relations.Append rel
'=== Create relations between parent table empresas and child table ensayos ======
Set rel = dbs.CreateRelation("R04")
rel.Table = "empresas"
rel.ForeignTable = "ensayos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "empresa_id")
dbs.Relations.Append rel
'=== Create relations between parent table fincas and child table ensayos ======
Set rel = dbs.CreateRelation("R07")
rel.Table = "fincas"
rel.ForeignTable = "ensayos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "finca_id")
dbs.Relations.Append rel
'=== Create relations between parent table cultivos and child table ensayos ======
Set rel = dbs.CreateRelation("R02")
rel.Table = "cultivos"
rel.ForeignTable = "ensayos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "cultivo_id")
dbs.Relations.Append rel
'=== Create relations between parent table variedades and child table ensayos ======
Set rel = dbs.CreateRelation("R03")
rel.Table = "variedades"
rel.ForeignTable = "ensayos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "variedad_id")
dbs.Relations.Append rel
'=== Create relations between parent table tipos_aplicaciones and child table ensayos_preaplicaciones ======
Set rel = dbs.CreateRelation("R10")
rel.Table = "tipos_aplicaciones"
rel.ForeignTable = "ensayos_preaplicaciones"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "tipo_aplicacion_id")
dbs.Relations.Append rel
'=== Create relations between parent table productos and child table ensayos_tratamientos ======
Set rel = dbs.CreateRelation("R12")
rel.Table = "productos"
rel.ForeignTable = "ensayos_tratamientos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "producto_id")
dbs.Relations.Append rel
'=== Create relations between parent table ensayos and child table ensayos_preaplicaciones ======
Set rel = dbs.CreateRelation("R05")
rel.Table = "ensayos"
rel.ForeignTable = "ensayos_preaplicaciones"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "ensayo_id")
dbs.Relations.Append rel
'=== Create relations between parent table ensayos and child table ensayos_tratamientos ======
Set rel = dbs.CreateRelation("R11")
rel.Table = "ensayos"
rel.ForeignTable = "ensayos_tratamientos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "id")
dbs.Relations.Append rel
'=== Create relations between parent table ensayos and child table ensayos_preaplicaciones_fotos ======
Set rel = dbs.CreateRelation("R15")
rel.Table = "ensayos"
rel.ForeignTable = "ensayos_preaplicaciones_fotos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "ensayo_id")
dbs.Relations.Append rel
'=== Create relations between parent table ensayos and child table ensayos_postaplicaciones ======
Set rel = dbs.CreateRelation("R16")
rel.Table = "ensayos"
rel.ForeignTable = "ensayos_postaplicaciones"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "ensayo_id")
dbs.Relations.Append rel
'=== Create relations between parent table ensayos and child table ensayos_postaplicaciones_fotos ======
Set rel = dbs.CreateRelation("R17")
rel.Table = "ensayos"
rel.ForeignTable = "ensayos_postaplicaciones_fotos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "id")
dbs.Relations.Append rel
'=== Create relations between parent table equipos and child table ensayos_preaplicaciones ======
Set rel = dbs.CreateRelation("R09")
rel.Table = "equipos"
rel.ForeignTable = "ensayos_preaplicaciones"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "equipo_id")
dbs.Relations.Append rel
'=== Create relations between parent table unidades and child table ensayos_tratamientos ======
Set rel = dbs.CreateRelation("R13")
rel.Table = "unidades"
rel.ForeignTable = "ensayos_tratamientos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "unidad_id")
dbs.Relations.Append rel
'=== Create relations between parent table tecnicos and child table ensayos ======
Set rel = dbs.CreateRelation("R06")
rel.Table = "tecnicos"
rel.ForeignTable = "ensayos"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "tecnico_id")
dbs.Relations.Append rel
'=== Create relations between parent table ensayos_tratamientos and child table ensayos_tratamientos_repeticiones ======
Set rel = dbs.CreateRelation("R14")
rel.Table = "ensayos_tratamientos"
rel.ForeignTable = "ensayos_tratamientos_repeticiones"
rel.Attributes = dbRelationUpdateCascade + 0
Call AddFieldToRelation("id", "id")Call AddFieldToRelation("producto_id", "producto_id")
dbs.Relations.Append rel
End Sub
' Create queries
'================
Sub CreateQueries()
Dim qdf As QueryDef
End Sub
' Drop queries
'==============
Sub DropQuery(QueryName As String)
Dim qdf As QueryDef
Set qdf = Nothing
On Error Resume Next
Set qdf = dbs.QueryDefs(QueryName)
On Error GoTo 0
If Not qdf Is Nothing Then dbs.QueryDefs.Delete (QueryName)
End Sub
' Drop relation
'===============
Sub DropRelation(RelName As String)
Set rel = Nothing
On Error Resume Next
Set rel = dbs.Relations(RelName)
On Error GoTo 0
If Not rel Is Nothing Then dbs.Relations.Delete (RelName)
End Sub
' Drop table
'============
Sub DropTable(TableName As String)
Set tdf = Nothing
On Error Resume Next
Set tdf = dbs.TableDefs(TableName)
On Error GoTo 0
If Not tdf Is Nothing Then dbs.TableDefs.Delete (TableName)
End Sub
' Drop index
'============
Sub DropIndex(TableName As String, IndexName As String)
Set tdf = Nothing
Set idx = Nothing
On Error Resume Next
Set tdf = dbs.TableDefs(TableName)
Set idx = tdf.Indexes(IndexName)
On Error GoTo 0
If (Not tdf Is Nothing) And (Not idx Is Nothing) Then tdf.Indexes.Delete (IndexName)
End Sub
' Add fields to table
'=====================
Sub AddFieldToTable(FieldName As String, DataType As String, SizeCol As Integer, Attributes As Long, DefaultValue As Variant, ValText As String, ValRule As String, NotN As Boolean, ZeroLength As Boolean)
Dim fld As DAO.Field
Set fld = tdf.CreateField(FieldName, DataType)
If SizeCol <> 0 Then fld.Size = SizeCol
If Attributes <> 0 Then fld.Attributes = Attributes
fld.Required = NotN
If DataType = dbText Or DataType = dbMemo Then fld.AllowZeroLength = ZeroLength
fld.DefaultValue = DefaultValue
fld.ValidationRule = ValRule
fld.ValidationText = ValText
tdf.Fields.Append fld
End Sub
' Add properties to table
'=========================
Sub AddPropertyToTable(PropertyName As String, Value As Variant, DataType As String)
Dim prp As DAO.Property
Set prp = tdf.CreateProperty(PropertyName, DataType, Value)
tdf.Properties.Append prp
End Sub
' Add properties to field
'=========================
Sub AddPropertyToField(FieldName As String, PropertyName As String, Value As Variant, DataType As String)
Dim prp As DAO.Property
Dim fld As DAO.Field
Set fld = tdf.Fields(FieldName)
Set prp = fld.CreateProperty(PropertyName, DataType, Value)
fld.Properties.Append prp
End Sub
' Add fields to index
'=====================
Sub AddFieldToIndex(FieldName As String, Descending As Boolean)
Dim fld As DAO.Field
Set fld = idx.CreateField(FieldName)
If Descending = True Then fld.Attributes = dbDescending
idx.Fields.Append fld
End Sub
' Add fields to relation
'========================
Sub AddFieldToRelation(PKField As String, FKField As String)
Dim fld As DAO.Field
Set fld = rel.CreateField(PKField)
fld.ForeignName = FKField
rel.Fields.Append fld
End Sub
' Create before script
'=====================
Sub BeforeScript()
End Sub
' Create after script
'====================
Sub AfterScript()
End Sub
