Option Explicit
'******************************************
'* UpgradeDB subroutine generated by *
'* Compare'EM on 4/25/2005 *
'****************************************** OLD
'* C:\Documents and Settings\mike.LIANLI\ *
'* Desktop\compare\sandbox\empty.mdb *
'****************************************** NEW
'* C:\Documents and Settings\mike.LIANLI\ *
'* Desktop\compare\sandbox\Northwind.mdb *
'******************************************
'* Compare'EM version 0.9c (LITE) *
'* Copyright © 2005, Mike Noel *
'******************************************
Private Sub UpgradeDB()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim rel As DAO.Relation
'********************************************
' GIVE SERIOUS THOUGHT! - is next line OK ??
' 'db' defines the database to which changes
' will be applied. Do you really want that
' to be the same as the one where this code
' will run??
'
Set db = CurrentDb
'
'********************************************
' Create Categories table
set tdf = db.CreateTableDef ("Categories")
set fld = tdf.CreateField("CategoryID", dbLong)
setpro fld, "Attributes", dbLong, 17
tdf.Fields.Append fld
db.TableDefs.Append tdf
setpro fld, "Caption", dbText, "Category ID"
setpro fld, "Description", dbText, "Number automatically assigned to a new category."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 0
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("CategoryName", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Category Name"
setpro fld, "Description", dbText, "Name of food category."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 1
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("Description", dbMemo, 0)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 2
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Picture", dbLongBinary)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "A picture representing the food category."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 3
setpro fld, "Required", dbBoolean, False
' Create Customers table
set tdf = db.CreateTableDef ("Customers")
set fld = tdf.CreateField("CustomerID", dbText, 5)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
db.TableDefs.Append tdf
setpro fld, "Caption", dbText, "Customer ID"
setpro fld, "Description", dbText, "Unique five-character code based on customer name."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 0
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("CompanyName", dbText, 40)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Company Name"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 1
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("ContactName", dbText, 30)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Contact Name"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 2
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ContactTitle", dbText, 30)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Contact Title"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 3
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Address", dbText, 60)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Street or post-office box."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 4
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("City", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 5
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Region", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "State or province."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 6
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("PostalCode", dbText, 10)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Postal Code"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 7
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Country", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 8
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Phone", dbText, 24)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Phone number includes country code or area code."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 9
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Fax", dbText, 24)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Phone number includes country code or area code."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 10
setpro fld, "Required", dbBoolean, False
' Create Employees table
set tdf = db.CreateTableDef ("Employees")
set fld = tdf.CreateField("EmployeeID", dbLong)
setpro fld, "Attributes", dbLong, 17
tdf.Fields.Append fld
db.TableDefs.Append tdf
setpro fld, "Caption", dbText, "Employee ID"
setpro fld, "Description", dbText, "Number automatically assigned to new employee."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 0
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("LastName", dbText, 20)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Last Name"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 1
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("FirstName", dbText, 10)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "First Name"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 2
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("Title", dbText, 30)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Employee's title."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 3
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("TitleOfCourtesy", dbText, 25)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Title Of Courtesy"
setpro fld, "Description", dbText, "Title used in salutations."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 4
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("BirthDate", dbDate)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Birth Date"
setpro fld, "Format", dbText, "dd-mmm-yyyy"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 5
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("HireDate", dbDate)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Hire Date"
setpro fld, "Format", dbText, "dd-mmm-yyyy"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 6
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Address", dbText, 60)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Street or post-office box."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 7
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("City", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 8
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Region", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "State or province."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 9
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("PostalCode", dbText, 10)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Postal Code"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 10
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Country", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 11
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("HomePhone", dbText, 24)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Home Phone"
setpro fld, "Description", dbText, "Phone number includes country code or area code."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 12
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Extension", dbText, 4)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Internal telephone extension number."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 13
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Photo", dbText, 255)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Picture of employee."
setpro fld, "AllowZeroLength", dbBoolean, True
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 14
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Notes", dbMemo, 0)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "General information about employee's background."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 15
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ReportsTo", dbLong)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Reports To"
setpro fld, "Description", dbText, "Employee's supervisor."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 16
setpro fld, "Required", dbBoolean, False
' Create Order Details table
set tdf = db.CreateTableDef ("Order Details")
set fld = tdf.CreateField("OrderID", dbLong)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
db.TableDefs.Append tdf
setpro fld, "Caption", dbText, "Order ID"
setpro fld, "Description", dbText, "Same as Order ID in Orders table."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 0
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ProductID", dbLong)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Product"
setpro fld, "Description", dbText, "Same as Product ID in Products table."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 1
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("UnitPrice", dbCurrency)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Unit Price"
setpro fld, "Format", dbText, "$#,##0.00;($#,##0.00)"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "0"
setpro fld, "OrdinalPosition", dbLong, 2
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("Quantity", dbInteger)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Format", dbText, "General Number"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "1"
setpro fld, "OrdinalPosition", dbLong, 3
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("Discount", dbSingle)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Format", dbText, "Percent"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "0"
setpro fld, "OrdinalPosition", dbLong, 4
setpro fld, "Required", dbBoolean, True
' Create Orders table
set tdf = db.CreateTableDef ("Orders")
set fld = tdf.CreateField("OrderID", dbLong)
setpro fld, "Attributes", dbLong, 17
tdf.Fields.Append fld
db.TableDefs.Append tdf
setpro fld, "Caption", dbText, "Order ID"
setpro fld, "Description", dbText, "Unique order number."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 0
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("CustomerID", dbText, 5)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Customer"
setpro fld, "Description", dbText, "Same entry as in Customers table."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 1
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("EmployeeID", dbLong)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Employee"
setpro fld, "Description", dbText, "Same entry as in Employees table."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 2
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("OrderDate", dbDate)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Order Date"
setpro fld, "Format", dbText, "dd-mmm-yyyy"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 3
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("RequiredDate", dbDate)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Required Date"
setpro fld, "Format", dbText, "dd-mmm-yyyy"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 4
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ShippedDate", dbDate)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Shipped Date"
setpro fld, "Format", dbText, "dd-mmm-yyyy"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 5
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ShipVia", dbLong)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Ship Via"
setpro fld, "Description", dbText, "Same as Shipper ID in Shippers table."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 6
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Freight", dbCurrency)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Format", dbText, "$#,##0.00;($#,##0.00)"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "0"
setpro fld, "OrdinalPosition", dbLong, 7
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ShipName", dbText, 40)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Ship Name"
setpro fld, "Description", dbText, "Name of person or company to receive the shipment."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 8
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ShipAddress", dbText, 60)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Ship Address"
setpro fld, "Description", dbText, "Street address only -- no post-office box allowed."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 9
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ShipCity", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Ship City"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 10
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ShipRegion", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Ship Region"
setpro fld, "Description", dbText, "State or province."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 11
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ShipPostalCode", dbText, 10)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Ship Postal Code"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 12
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ShipCountry", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Ship Country"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 13
setpro fld, "Required", dbBoolean, False
' Create Products table
set tdf = db.CreateTableDef ("Products")
set fld = tdf.CreateField("ProductID", dbLong)
setpro fld, "Attributes", dbLong, 17
tdf.Fields.Append fld
db.TableDefs.Append tdf
setpro fld, "Caption", dbText, "Product ID"
setpro fld, "Description", dbText, "Number automatically assigned to new product."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 0
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ProductName", dbText, 40)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Product Name"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 1
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("SupplierID", dbLong)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Supplier"
setpro fld, "Description", dbText, "Same entry as in Suppliers table."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 2
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("CategoryID", dbLong)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Category"
setpro fld, "Description", dbText, "Same entry as in Categories table."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 3
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("QuantityPerUnit", dbText, 20)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Quantity Per Unit"
setpro fld, "Description", dbText, "(e.g., 24-count case, 1-liter bottle)."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 4
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("UnitPrice", dbCurrency)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Unit Price"
setpro fld, "Format", dbText, "$#,##0.00;($#,##0.00)"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "0"
setpro fld, "OrdinalPosition", dbLong, 5
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("UnitsInStock", dbInteger)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Units In Stock"
setpro fld, "Format", dbText, "General Number"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "0"
setpro fld, "OrdinalPosition", dbLong, 6
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("UnitsOnOrder", dbInteger)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Units On Order"
setpro fld, "Format", dbText, "General Number"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "0"
setpro fld, "OrdinalPosition", dbLong, 7
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ReorderLevel", dbInteger)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Reorder Level"
setpro fld, "Description", dbText, "Minimum units to maintain in stock."
setpro fld, "Format", dbText, "General Number"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "0"
setpro fld, "OrdinalPosition", dbLong, 8
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Discontinued", dbBoolean)
setpro fld, "Attributes", dbLong, 1
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Yes means item is no longer available."
setpro fld, "Format", dbText, "Yes/No"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, "=No"
setpro fld, "OrdinalPosition", dbLong, 9
setpro fld, "Required", dbBoolean, False
' Create Shippers table
set tdf = db.CreateTableDef ("Shippers")
set fld = tdf.CreateField("ShipperID", dbLong)
setpro fld, "Attributes", dbLong, 17
tdf.Fields.Append fld
db.TableDefs.Append tdf
setpro fld, "Caption", dbText, "Shipper ID"
setpro fld, "Description", dbText, "Number automatically assigned to new shipper."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 0
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("CompanyName", dbText, 40)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Company Name"
setpro fld, "Description", dbText, "Name of shipping company."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 1
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("Phone", dbText, 24)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Phone number includes country code or area code."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 2
setpro fld, "Required", dbBoolean, False
' Create Suppliers table
set tdf = db.CreateTableDef ("Suppliers")
set fld = tdf.CreateField("SupplierID", dbLong)
setpro fld, "Attributes", dbLong, 17
tdf.Fields.Append fld
db.TableDefs.Append tdf
setpro fld, "Caption", dbText, "Supplier ID"
setpro fld, "Description", dbText, "Number automatically assigned to new supplier."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 1
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("CompanyName", dbText, 40)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Company Name"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 2
setpro fld, "Required", dbBoolean, True
set fld = tdf.CreateField("ContactName", dbText, 30)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Contact Name"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 3
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("ContactTitle", dbText, 30)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Contact Title"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 4
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Address", dbText, 60)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Street or post-office box."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 5
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("City", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 6
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Region", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "State or province."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 7
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("PostalCode", dbText, 10)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Postal Code"
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 8
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Country", dbText, 15)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 9
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Phone", dbText, 24)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Phone number includes country code or area code."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 10
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("Fax", dbText, 24)
setpro fld, "Attributes", dbLong, 2
tdf.Fields.Append fld
setpro fld, "Description", dbText, "Phone number includes country code or area code."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 11
setpro fld, "Required", dbBoolean, False
set fld = tdf.CreateField("HomePage", dbMemo, 0)
setpro fld, "Attributes", dbLong, 32770
tdf.Fields.Append fld
setpro fld, "Caption", dbText, "Home Page"
setpro fld, "Description", dbText, "Supplier's home page on World Wide Web."
setpro fld, "AllowZeroLength", dbBoolean, False
setpro fld, "DefaultValue", dbText, ""
setpro fld, "OrdinalPosition", dbLong, 12
setpro fld, "Required", dbBoolean, False
' create index CategoryName of table Categories
set tdf = db.TableDefs ("Categories")
set idx = tdf.CreateIndex("CategoryName")
set fld = idx.CreateField("CategoryName")
idx.Fields.Append fld
idx.Unique = True
tdf.Indexes.Append idx
' create index PrimaryKey of table Categories
set tdf = db.TableDefs ("Categories")
set idx = tdf.CreateIndex("PrimaryKey")
set fld = idx.CreateField("CategoryID")
idx.Fields.Append fld
idx.Primary = True
idx.Unique = True
idx.Required = True
tdf.Indexes.Append idx
' create index City of table Customers
set tdf = db.TableDefs ("Customers")
set idx = tdf.CreateIndex("City")
set fld = idx.CreateField("City")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index CompanyName of table Customers
set tdf = db.TableDefs ("Customers")
set idx = tdf.CreateIndex("CompanyName")
set fld = idx.CreateField("CompanyName")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PostalCode of table Customers
set tdf = db.TableDefs ("Customers")
set idx = tdf.CreateIndex("PostalCode")
set fld = idx.CreateField("PostalCode")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PrimaryKey of table Customers
set tdf = db.TableDefs ("Customers")
set idx = tdf.CreateIndex("PrimaryKey")
set fld = idx.CreateField("CustomerID")
idx.Fields.Append fld
idx.Primary = True
idx.Unique = True
idx.Required = True
tdf.Indexes.Append idx
' create index Region of table Customers
set tdf = db.TableDefs ("Customers")
set idx = tdf.CreateIndex("Region")
set fld = idx.CreateField("Region")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index LastName of table Employees
set tdf = db.TableDefs ("Employees")
set idx = tdf.CreateIndex("LastName")
set fld = idx.CreateField("LastName")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PostalCode of table Employees
set tdf = db.TableDefs ("Employees")
set idx = tdf.CreateIndex("PostalCode")
set fld = idx.CreateField("PostalCode")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PrimaryKey of table Employees
set tdf = db.TableDefs ("Employees")
set idx = tdf.CreateIndex("PrimaryKey")
set fld = idx.CreateField("EmployeeID")
idx.Fields.Append fld
idx.Primary = True
idx.Unique = True
idx.Required = True
tdf.Indexes.Append idx
' create index OrderID of table Order Details
set tdf = db.TableDefs ("Order Details")
set idx = tdf.CreateIndex("OrderID")
set fld = idx.CreateField("OrderID")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PrimaryKey of table Order Details
set tdf = db.TableDefs ("Order Details")
set idx = tdf.CreateIndex("PrimaryKey")
set fld = idx.CreateField("OrderID")
idx.Fields.Append fld
set fld = idx.CreateField("ProductID")
idx.Fields.Append fld
idx.Primary = True
idx.Unique = True
idx.Required = True
tdf.Indexes.Append idx
' create index ProductID of table Order Details
set tdf = db.TableDefs ("Order Details")
set idx = tdf.CreateIndex("ProductID")
set fld = idx.CreateField("ProductID")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index CustomerID of table Orders
set tdf = db.TableDefs ("Orders")
set idx = tdf.CreateIndex("CustomerID")
set fld = idx.CreateField("CustomerID")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index EmployeeID of table Orders
set tdf = db.TableDefs ("Orders")
set idx = tdf.CreateIndex("EmployeeID")
set fld = idx.CreateField("EmployeeID")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index OrderDate of table Orders
set tdf = db.TableDefs ("Orders")
set idx = tdf.CreateIndex("OrderDate")
set fld = idx.CreateField("OrderDate")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PrimaryKey of table Orders
set tdf = db.TableDefs ("Orders")
set idx = tdf.CreateIndex("PrimaryKey")
set fld = idx.CreateField("OrderID")
idx.Fields.Append fld
idx.Primary = True
idx.Unique = True
idx.Required = True
tdf.Indexes.Append idx
' create index ShippedDate of table Orders
set tdf = db.TableDefs ("Orders")
set idx = tdf.CreateIndex("ShippedDate")
set fld = idx.CreateField("ShippedDate")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index ShipPostalCode of table Orders
set tdf = db.TableDefs ("Orders")
set idx = tdf.CreateIndex("ShipPostalCode")
set fld = idx.CreateField("ShipPostalCode")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index CategoryID of table Products
set tdf = db.TableDefs ("Products")
set idx = tdf.CreateIndex("CategoryID")
set fld = idx.CreateField("CategoryID")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PrimaryKey of table Products
set tdf = db.TableDefs ("Products")
set idx = tdf.CreateIndex("PrimaryKey")
set fld = idx.CreateField("ProductID")
idx.Fields.Append fld
idx.Primary = True
idx.Unique = True
idx.Required = True
tdf.Indexes.Append idx
' create index ProductName of table Products
set tdf = db.TableDefs ("Products")
set idx = tdf.CreateIndex("ProductName")
set fld = idx.CreateField("ProductName")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index SupplierID of table Products
set tdf = db.TableDefs ("Products")
set idx = tdf.CreateIndex("SupplierID")
set fld = idx.CreateField("SupplierID")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PrimaryKey of table Shippers
set tdf = db.TableDefs ("Shippers")
set idx = tdf.CreateIndex("PrimaryKey")
set fld = idx.CreateField("ShipperID")
idx.Fields.Append fld
idx.Primary = True
idx.Unique = True
idx.Required = True
tdf.Indexes.Append idx
' create index CompanyName of table Suppliers
set tdf = db.TableDefs ("Suppliers")
set idx = tdf.CreateIndex("CompanyName")
set fld = idx.CreateField("CompanyName")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PostalCode of table Suppliers
set tdf = db.TableDefs ("Suppliers")
set idx = tdf.CreateIndex("PostalCode")
set fld = idx.CreateField("PostalCode")
idx.Fields.Append fld
tdf.Indexes.Append idx
' create index PrimaryKey of table Suppliers
set tdf = db.TableDefs ("Suppliers")
set idx = tdf.CreateIndex("PrimaryKey")
set fld = idx.CreateField("SupplierID")
idx.Fields.Append fld
idx.Primary = True
idx.Unique = True
idx.Required = True
tdf.Indexes.Append idx
' create relation between tables Categories and Products
set rel = db.CreateRelation("CategoriesProducts")
rel.Table = "Categories"
rel.ForeignTable = "Products"
set fld = rel.CreateField("CategoryID")
fld.ForeignName = "CategoryID"
rel.Fields.Append fld
rel.Attributes = 0
db.Relations.Append rel
' create relation between tables Customers and Orders
set rel = db.CreateRelation("CustomersOrders")
rel.Table = "Customers"
rel.ForeignTable = "Orders"
set fld = rel.CreateField("CustomerID")
fld.ForeignName = "CustomerID"
rel.Fields.Append fld
rel.Attributes = 256
db.Relations.Append rel
' create relation between tables Employees and Orders
set rel = db.CreateRelation("EmployeesOrders")
rel.Table = "Employees"
rel.ForeignTable = "Orders"
set fld = rel.CreateField("EmployeeID")
fld.ForeignName = "EmployeeID"
rel.Fields.Append fld
rel.Attributes = 0
db.Relations.Append rel
' create relation between tables Orders and Order Details
set rel = db.CreateRelation("OrdersOrder Details")
rel.Table = "Orders"
rel.ForeignTable = "Order Details"
set fld = rel.CreateField("OrderID")
fld.ForeignName = "OrderID"
rel.Fields.Append fld
rel.Attributes = 4096
db.Relations.Append rel
' create relation between tables Products and Order Details
set rel = db.CreateRelation("ProductsOrder Details")
rel.Table = "Products"
rel.ForeignTable = "Order Details"
set fld = rel.CreateField("ProductID")
fld.ForeignName = "ProductID"
rel.Fields.Append fld
rel.Attributes = 0
db.Relations.Append rel
' create relation between tables Shippers and Orders
set rel = db.CreateRelation("ShippersOrders")
rel.Table = "Shippers"
rel.ForeignTable = "Orders"
set fld = rel.CreateField("ShipperID")
fld.ForeignName = "ShipVia"
rel.Fields.Append fld
rel.Attributes = 0
db.Relations.Append rel
' create relation between tables Suppliers and Products
set rel = db.CreateRelation("SuppliersProducts")
rel.Table = "Suppliers"
rel.ForeignTable = "Products"
set fld = rel.CreateField("SupplierID")
fld.ForeignName = "SupplierID"
rel.Fields.Append fld
rel.Attributes = 0
db.Relations.Append rel
End Sub
'******************************************
'* SetPro subroutine supporting VBA code *
'* generated by Compare'EM *
'******************************************
'* Compare'EM version 0.9c (LITE) *
'* Copyright © 2005, Mike Noel *
'******************************************
Private Sub SetPro(o As Object, s As String, t As DataTypeEnum, v As Variant)
On Error GoTo Problems
o.Properties(s) = v
Exit Sub
Problems:
If Err = 3270 Then
o.Properties.Append o.CreateProperty(s, t, v)
Resume ProblemsX
End If
On Error GoTo 0
Resume
ProblemsX:
End Sub