Текст sql-скрипта по созданию базы данных в MS Access

Dim ERwinWorkspace As Workspace
Dim ERwinDatabase As Database
Dim ERwinTableDef As TableDef
Dim ERwinQueryDef As QueryDef
Dim ERwinIndex As Index
Dim ERwinField As Field
Dim ERwinRelation As Relation
Set ERwinWorkspace = DBEngine.WorkSpaces(0)
Set ERwinDatabase = ERwinWorkspace.OpenDatabase("C:\Users\v31-10455\Desktop\1.mdb")

 

' CREATE TABLE Äîëæíîñòè
Set ERwinTableDef = ERwinDatabase.CreateTableDef("Äîëæíîñòè")

Set ERwinField = ERwinTableDef.CreateField("Êîä äîëæíîñòè", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Äîëæíîñòü", DB_TEXT, 20)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

Set ERwinField = ERwinTableDef.Fields("Êîä äîëæíîñòè")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Êîä äîëæíîñòè:")

Set ERwinField = ERwinTableDef.Fields("Äîëæíîñòü")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Äîëæíîñòü:")

 

' CREATE INDEX XPKÄîëæíîñòè

Set ERwinTableDef = ERwinDatabase.TableDefs("Äîëæíîñòè")

Set ERwinIndex = ERwinTableDef.CreateIndex("XPKÄîëæíîñòè")

Set ERwinField = ERwinIndex.CreateField("Êîä äîëæíîñòè")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

' CREATE TABLE Çàêàç

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Çàêàç")

Set ERwinField = ERwinTableDef.CreateField("Íîìåð çàêàçà", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Îïèñàíèå", DB_TEXT, 50)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Òàáåëüíûé íîìåð", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Íîìåð êëèåíòà", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Äàòà ïîëó÷åíèÿ", DB_DATETIME)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Äàòà âûïîëíåíèÿ", DB_DATETIME)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

Set ERwinField = ERwinTableDef.Fields("Íîìåð çàêàçà")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Íîìåð çàêàçà:")

Set ERwinField = ERwinTableDef.Fields("Îïèñàíèå")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Îïèñàíèå:")

Set ERwinField = ERwinTableDef.Fields("Òàáåëüíûé íîìåð")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Òàáåëüíûé íîìåð:")

Set ERwinField = ERwinTableDef.Fields("Íîìåð êëèåíòà")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Íîìåð êëèåíòà:")

Set ERwinField = ERwinTableDef.Fields("Äàòà ïîëó÷åíèÿ")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Äàòà ïîëó÷åíèÿ:")

Set ERwinField = ERwinTableDef.Fields("Äàòà âûïîëíåíèÿ")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Äàòà âûïîëíåíèÿ:")

 

' CREATE INDEX XPKÇàêàç

Set ERwinTableDef = ERwinDatabase.TableDefs("Çàêàç")

Set ERwinIndex = ERwinTableDef.CreateIndex("XPKÇàêàç")

Set ERwinField = ERwinIndex.CreateField("Íîìåð çàêàçà")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

' CREATE TABLE Êëèåíòû

Set ERwinTableDe = ERwinDatabase.CreateTableDef("Êëèåíòû")

Set ERwinField = ERwinTableDef.CreateField("Íîìåð êëèåíòà", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Ôèî", DB_TEXT, 20)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Àäðåñ", DB_TEXT, 20)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Òåëåôîí", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

Set ERwinField = ERwinTableDef.Fields("Íîìåð êëèåíòà")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Íîìåð êëèåíòà:")

Set ERwinField = ERwinTableDef.Fields("Ôèî")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Ôèî:")

Set ERwinField = ERwinTableDef.Fields("Àäðåñ")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Àäðåñ:")

Set ERwinField = ERwinTableDef.Fields("Òåëåôîí")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Òåëåôîí:")

 

' CREATE INDEX XPKÊëèåíòû

Set ERwinTableDef = ERwinDatabase.TableDefs("Êëèåíòû")

Set ERwinIndex = ERwinTableDef.CreateIndex("XPKÊëèåíòû")

Set ERwinField = ERwinIndex.CreateField("Íîìåð êëèåíòà")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

' CREATE TABLE Êîìïëåêòóþùèå

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Êîìïëåêòóþùèå")

Set ERwinField = ERwinTableDef.CreateField("Àðòèêóë", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Íàèìåíîâàíèå êîìïòåêòóþùåé", DB_TEXT, 20)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Öåíà", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

Set ERwinField = ERwinTableDef.Fields("Àðòèêóë")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Àðòèêóë:")

Set ERwinField = ERwinTableDef.Fields("Íàèìåíîâàíèå êîìïòåêòóþùåé")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Íàèìåíîâàíèå êîìïòåêòóþùåé:")

Set ERwinField = ERwinTableDef.Fields("Öåíà")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Öåíà:")

 

' CREATE INDEX XPKÊîìïëåêòóþùèå

Set ERwinTableDef = ERwinDatabase.TableDefs("Êîìïëåêòóþùèå")

Set ERwinIndex = ERwinTableDef.CreateIndex("XPKÊîìïëåêòóþùèå")

Set ERwinField = ERwinIndex.CreateField("Àðòèêóë")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

' CREATE TABLE Êîìïëåêòóþùèå â çàêàçå

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Êîìïëåêòóþùèå â çàêàçå")

Set ERwinField = ERwinTableDef.CreateField("Íîìåð çàêàçà", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Àðòèêóë", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Êîëè÷åñòâî", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

Set ERwinField = ERwinTableDef.Fields("Íîìåð çàêàçà")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Íîìåð çàêàçà:")

Set ERwinField = ERwinTableDef.Fields("Àðòèêóë")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Àðòèêóë:")

Set ERwinField = ERwinTableDef.Fields("Êîëè÷åñòâî")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Êîëè÷åñòâî:")

 

' CREATE INDEX XPKÊîìïëåêòóþùèå â çàêàçå

Set ERwinTableDef = ERwinDatabase.TableDefs("Êîìïëåêòóþùèå â çàêàçå")

Set ERwinIndex = ERwinTableDef.CreateIndex("XPKÊîìïëåêòóþùèå â çàêàçå")

Set ERwinField = ERwinIndex.CreateField("Íîìåð çàêàçà")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("Àðòèêóë")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

' CREATE TABLE Ñîòðóäíèêè

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Ñîòðóäíèêè")

Set ERwinField = ERwinTableDef.CreateField("Òàáåëüíûé íîìåð", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Ôèî", DB_TEXT, 20)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Àäðåñ", DB_TEXT, 20)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Êîä äîëæíîñòè", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Òåëåôîí", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

Set ERwinField = ERwinTableDef.Fields("Òàáåëüíûé íîìåð")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Òàáåëüíûé íîìåð:")

Set ERwinField = ERwinTableDef.Fields("Ôèî")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Ôèî:")

Set ERwinField = ERwinTableDef.Fields("Àäðåñ")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Àäðåñ:")

Set ERwinField = ERwinTableDef.Fields("Êîä äîëæíîñòè")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Êîä äîëæíîñòè:")

Set ERwinField = ERwinTableDef.Fields("Òåëåôîí")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Òåëåôîí:")

 

' CREATE INDEX XPKÑîòðóäíèêè

Set ERwinTableDef = ERwinDatabase.TableDefs("Ñîòðóäíèêè")

Set ERwinIndex = ERwinTableDef.CreateIndex("XPKÑîòðóäíèêè")

Set ERwinField = ERwinIndex.CreateField("Òàáåëüíûé íîìåð")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

' CREATE TABLE Óñëóãè

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Óñëóãè")

Set ERwinField = ERwinTableDef.CreateField("Êîä óñëóãè", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Íàçâàíèå óñëóãè", DB_TEXT, 20)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Öåíà", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

Set ERwinField = ERwinTableDef.Fields("Êîä óñëóãè")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Êîä óñëóãè:")

Set ERwinField = ERwinTableDef.Fields("Íàçâàíèå óñëóãè")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Íàçâàíèå óñëóãè:")

Set ERwinField = ERwinTableDef.Fields("Öåíà")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Öåíà:")

 

' CREATE INDEX XPKÓñëóãè

Set ERwinTableDef = ERwinDatabase.TableDefs("Óñëóãè")

Set ERwinIndex = ERwinTableDef.CreateIndex("XPKÓñëóãè")

Set ERwinField = ERwinIndex.CreateField("Êîä óñëóãè")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

' CREATE TABLE Óñëóãè â çàêàçå

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Óñëóãè â çàêàçå")

Set ERwinField = ERwinTableDef.CreateField("Íîìåð çàêàçà", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Êîä óñëóãè", DB_LONG)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("òàáåëüíûé íîìåð", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Ïðèìå÷àíèå", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Òàáåëüíûé íîìåð", DB_LONG)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

Set ERwinField = ERwinTableDef.Fields("Íîìåð çàêàçà")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Íîìåð çàêàçà:")

Set ERwinField = ERwinTableDef.Fields("Êîä óñëóãè")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Êîä óñëóãè:")

Set ERwinField = ERwinTableDef.Fields("òàáåëüíûé íîìåð")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "òàáåëüíûé íîìåð:")

Set ERwinField = ERwinTableDef.Fields("Ïðèìå÷àíèå")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Ïðèìå÷àíèå:")

Set ERwinField = ERwinTableDef.Fields("Òàáåëüíûé íîìåð")

Call SetFieldProp(ERwinField, "Caption", DB_TEXT, "Òàáåëüíûé íîìåð:")

 

' CREATE INDEX XPKÓñëóãè â çàêàçå

Set ERwinTableDef = ERwinDatabase.TableDefs("Óñëóãè â çàêàçå")

Set ERwinIndex = ERwinTableDef.CreateIndex("XPKÓñëóãè â çàêàçå")

Set ERwinField = ERwinIndex.CreateField("Íîìåð çàêàçà")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("Êîä óñëóãè")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

' CREATE RELATIONSHIP R/3

Set ERwinRelation = ERwinDatabase.CreateRelation("R/3", "Êëèåíòû", "Çàêàç")

Set ERwinField = ERwinRelation.CreateField("Íîìåð êëèåíòà")

ERwinField.ForeignName = Íîìåð êëèåíòà

ERwinRelation.Fields.Append ERwinField

ERwinDatabase.Relations.Append ERwinRelation

 

 

' CREATE RELATIONSHIP R/5

Set ERwinRelation = ERwinDatabase.CreateRelation("R/5", "Ñîòðóäíèêè", "Çàêàç")

Set ERwinField = ERwinRelation.CreateField("Òàáåëüíûé íîìåð")

ERwinField.ForeignName = Òàáåëüíûé íîìåð

ERwinRelation.Fields.Append ERwinField

ERwinDatabase.Relations.Append ERwinRelation

 

' CREATE RELATIONSHIP R/17

Set ERwinRelation = ERwinDatabase.CreateRelation("R/17", "Çàêàç", "Êîìïëåêòóþùèå âçàêàçå")

Set ERwinField = ERwinRelation.CreateField("Íîìåð çàêàçà")

ERwinField.ForeignName = Íîìåð çàêàçà

ERwinRelation.Fields.Append ERwinField

ERwinDatabase.Relations.Append ERwinRelation

 

' CREATE RELATIONSHIP R/18

Set ERwinRelation = ERwinDatabase.CreateRelation("R/18", "Êîìïëåêòóþùèå", "Êîìïëåêòóþùèå â çàêàçå")

Set ERwinField = ERwinRelation.CreateField("Àðòèêóë")

ERwinField.ForeignName = Àðòèêóë

ERwinRelation.Fields.Append ERwinField

ERwinDatabase.Relations.Append ERwinRelation

 

' CREATE RELATIONSHIP R/2

Set ERwinRelation = ERwinDatabase.CreateRelation("R/2", "Äîëæíîñòè", "Ñîòðóäíèêè")

Set ERwinField = ERwinRelation.CreateField("Êîä äîëæíîñòè")

ERwinField.ForeignName = Êîä äîëæíîñòè

ERwinRelation.Fields.Append ERwinField

ERwinDatabase.Relations.Append ERwinRelation

 

' CREATE RELATIONSHIP R/14

Set ERwinRelation = ERwinDatabase.CreateRelation("R/14", "Óñëóãè", "Óñëóãè â çàêàçå")

Set ERwinField = ERwinRelation.CreateField("Êîä óñëóãè")

ERwinField.ForeignName = Êîä óñëóãè

ERwinRelation.Fields.Append ERwinField

ERwinDatabase.Relations.Append ERwinRelation

 

' CREATE RELATIONSHIP R/15

Set ERwinRelation = ERwinDatabase.CreateRelation("R/15", "Ñîòðóäíèêè", "Óñëóãè â çàêàçå")

Set ERwinField = ERwinRelation.CreateField("Òàáåëüíûé íîìåð")

ERwinField.ForeignName = Òàáåëüíûé íîìåð

ERwinRelation.Fields.Append ERwinField

ERwinDatabase.Relations.Append ERwinRelation

 

' CREATE RELATIONSHIP R/16

Set ERwinRelation = ERwinDatabase.CreateRelation("R/16", "Çàêàç", "Óñëóãè â çàêàçå")

Set ERwinField = ERwinRelation.CreateField("Íîìåð çàêàçà")

ERwinField.ForeignName = Íîìåð çàêàçà

ERwinRelation.Fields.Append ERwinField

ERwinDatabase.Relations.Append ERwinRelation

ERwinDatabase.Close

ERwinWorkspace.Close

' Terminating Access Basic DAO Session...

Schema Generation Complete

252 queries succeeded