Базы данных

Практическая работа 31. Программное создание базы данных и работа с ней: создание файла, базы данных, двух таблиц, отношения, заполнение таблиц в диалоговом режиме, создание SQL-запроса и вывод результатов

Задание: создать программным способом:

1. файл базы данных,
2. две таблицы (см. рис. 4.26, рис. 4.27, рис. 4.28), ключевые поля таблиц,
3. отношение,
4. возможность ввода записей таблиц в диалоговом режиме,
5. SQL-запрос для получения информации о данных клиентов (номер, фамилия, разряд, коэффициент тарифной сетки),
6. реализовать вывод результатов запроса.

Рис. 4.26. Таблица 1(Таrif)

Рис. 4.27. Таблица 2 (Kunde)

увеличить изображение
Рис. 4.28. Таблица 2 (Kunde), режим конструктора (имя поля, тип данных)

Пример выполнения практической работы 31.

• Cоздать форму Form1, рис. 4.29 и меню, табл. 4.13,
  

  
  Рис. 4.29. Форма Form1, внешний вид. Создано меню и указана последовательность выполнения задания

  Таблица 4.13. Меню

  File	Table	Record Set	Relation	Query	Exit
  Create	Tab1	Record Set Table1		Create
  				Query
  Open	Tab1	Record Set Table2		Open
  				Query

  Здесь:

  • Form Create (рис. 4.31) - создать файл базы данных,
  • Table Tab1 - создать структуру таблицы 1,
  • Table Tab2 - создать структуру таблицы 2 (рис. 4.28),
  • RecordSet RecordSetTable1 - в диалоговом режиме создать записи таблицы1 (рис. 4.33),
  • RecordSet RecordSetTable2 - в диалоговом режиме создать записи таблицы2 (рис. 4.34),
  • Relation - создать отношение,
  • Query - CreateQuery - создать запрос,
  • Query - OpenQuery - открыть запрос (рис. 4.35),
  • Exit - завершить выполнение проекта,
  • создать Стандартный модуль и 5 форм, рис. 4.30,
    

    
    Рис. 4.30. Окно проекта - составляющие проекта
  • ввести код формы Form1, пояснения даны под комментариями:

    Private Sub mnuCreate_Click() ' создать файл базы данных
    Load FrmCreate
    FrmCreate.Show
    End Sub
    
    Private Sub mnuCreateQuery_Click() ' создать запрос
    Call Query
    End Sub
    
    Private Sub mnuExit_Click() ' завершение работы проекта
    Call DatabaseClose
    Unload Form1
    MsgBox ("Завершение работы проекта!?"), vbCritical + vbOKOnly
    End
    End Sub
    
    Private Sub mnuOpen_Click() ' выбрать файл для его
    ' последующего открытия
    Load FrmOpen
    FrmOpen.Show
    End Sub
    
    Private Sub mnuOpenQuery_Click() ' открыть запрос
    Load FrmQuery
    FrmQuery.Show
    End Sub
    
    Private Sub mnuRecordSetTab1_Click() ' создать записи в
    ' таблице 1
    Load FrmTarif
    FrmTarif.Show
    End Sub
    
    Private Sub mnuRecordSetTab2_Click()' создать записи в
    ' таблице 2
    Load FrmKunde
    FrmKunde.Show
    End Sub
    
    Private Sub mnuRelation_Click() ' создать отношение
    Call Relation
    End Sub
    
    Private Sub mnuTab1_Click() ' создать структуру таблицы 1
    Call Tab1
    End Sub
    
    Private Sub mnuTab2_Click() ' создать структуру таблицы 2
    Call Tab2
    End Sub
  • ввести код модуля, данный ниже; пояснения даны под комментариями:

    (General) (Declarations)
    Public NewDB As Database
    Public NewWS As Workspace
    Public NewTbl As TableDef1
    Public NewTbl As TableDef2
    Public idx1 As Index
    Public idx2 As Index
    Public NewRel As Relation
    Public sqlquery As QueryDef
    Public NewRec As Recordset
    
    Public Sub Tab1() ' создание таблицы 1
    Set NewTbl1 = NewDB.CreateTableDef("Tarif")
    
    Set f1 = NewTbl1.CreateField()
    Set f2 = NewTbl1.CreateField() 
    
    f1.Name = "Razrjad" ' имя поля
    f1.Type = dbInteger ' тип данных
    f2.Name = "Koeffcnt"
    f2.Type = dbSingle
    NewTbl1.Fields.Append f1
    NewTbl1.Fields.Append f2
    
    Set idx1 = NewTbl1.CreateIndex("TarifIndex") ' создание индекса
    Idx1.Primary = True
    Set f1 = idx1.CreateField("Razrjad") ' создание индексного поля
    ' (используется метод объета idx)
    idx1.Fields.Append f1
    NewTbl1.Indexes.Append idx1
    NewDB.TableDefs.Append NewTbl1
    
    MsgBox ("Создана структура таблицы Tarif"), vbInformation +_
    vbOKOnly
    End Sub
    
    Public Sub Tab2() ' создание таблицы 2
    Set NewTbl2 = NewDB.CreateTableDef("Kunde")
    
    ' создание полей таблицы
    Set f3 = NewTbl2.CreateField()
    Set f4 = NewTbl2.CreateField()
    Set f6 = NewTbl2.CreateField()
    
    ' присваивание имeн полям и типов данным
    f3.Name = "Tabnumber"
    f3.Type = dbInteger
    f4.Name = "Nam"
    f4.Type = dbText
    f4.Size = 20
    f4.AllowZeroLength = True
    ' здесь позднее (см. Public Sub Relation) будет создано 
    ' зависимое поле (f5) отношения
    f6.Name = "Razrjad"
    f6.Type = dbInteger
    
    ' добавление полей в таблицу
    NewTbl2.Fields.Append f3
    NewTbl2.Fields.Append f4
    NewTbl2.Fields.Append f6
    
    ' создание индекса
    Set idx2 = NewTbl2.CreateIndex("KundeIndex")
    ' указание того, что главный индекс существует
    idx2.Primary = True
    ' создание индексного поля
    Set f3 = idx2.CreateField("Razrjad")
    Idx2.Fields.Append f3
    NewTbl2.Indexes.Append idx2
    NewDB.TableDefs.Append NewTbl2
    
    MsgBox ("Создана структура таблицы Kunde !"), vbOKOnly +_
    vbInformation
    End Sub
    
    Public Sub Relation() ' создание отношения
    Set NewRel = NewDB.CreateRelation("RelKunde") ' используется
    ' метод Создать отношение объекта База данных
    ' указание главной таблицы в отношении
    NewRel.Table = "Tarif"
    ' указание зависимой таблицы в отношении
    NewRel.ForeignTable = "Kunde"
    
    ' создание зависимого поля в отношении
    Set f5 = NewRel.CreateField("Razrjad")
    ' указание имени зависмого поля, по которому 
    ' строится отношение
    f5.ForeignName = "Razrjad"
    NewRel.Fields.Append f5
    ' добавление отношения в базу данных
    NewDB.Relations.Append NewRel
    
    MsgBox ("Отношение создано!"), vbOKOnly + vbInformation
    End Sub
    
    Public Sub Query() ' создание запроса
    Set sqlquery = NewDB.CreateQueryDef("sql1")
    
    ' указание того, что требуется выбрать в запросе,
    ' из каких таблиц провести выбор и формулировка 
    ' cоответствующих условий 
    
    sqlquery.SQL = "SELECT Kunde.Tabnumber, Kunde.Nam, _
    Kunde.Razrjad, Tarif.Razrjad, Tarif.Koeffcnt _
    FROM Tarif INNER JOIN Kunde ON Tarif.Razrjad = Kunde.Razrjad _
    WHERE Kunde.Tabnumber = 2;"
    MsgBox (" SQL-запрос введeнi!"), vbOKOnly + vbInformation
    End Sub
    
    Public Sub CreateFile() ' создание файла базы данных
    Set NewWS = DBEngine.Workspaces(0)
    Set NewDB = NewWS.CreateDatabase(FrmCreate.txtPath.Text,_ dbLangCyrillic)
    MsgBox (""Файл в формате mdb создан!"), vbInformation
    Unload FrmCreate
    End Sub
    
    Public Sub DatabaseClose() 
    NewWS.Close
    Set NewWC = Nothing
    End Sub
    
    Sub Main() ' начало проекта
    resp = MsgBox("Здравствуйте! Программное создание базы_ данных и работа с ней. Выберите ДА или НЕТ!", vbYesNo, " ")
    If resp = vbYes Then
    MsgBox "Сделайте щелчок на кнопке ОК для продолжения диалога!"
    Load Form1
    Form1.Show
    End If
    End Sub
  • на форме FrmCreate, рис. 4.31, создать требуемые объекты и ввести код, данный ниже:
    

    
    Рис. 4.31. Форма FrmCreate (drvDrive, dirDirect, txtPaht)

    Private Sub Cancel_Click()
    Unload Me
    End Sub
    
    Private Sub Create_Click()
    Call CreateFile
    End Sub
    
    Private Sub dirDirect_Change()
    filFile.Path = dirDirect.Path
    End Sub
    
    Private Sub drvDrive_Change()
    dirDirect.Path = drvDrive.Drive
    End Sub
    
    Private Sub Path_Click() ' вывод пути и имени файла в
    ' поле txtPath 
    If drvDrive.Drive = "a:" Then
    dirDirect.Path = "a:\"
    txtPath.Text = dirDirect.Path + filFile.FileName
    Else
    txtPath.Text = dirDirect.Path + "\" + filFile.FileName
    End If
    End Sub
  • на форме FrmOpen, рис. 4.32, создать требуемые объекты и ввести код, данный ниже:
    

    
    Рис. 4.32. FrmOpen (drvDrive, dirDirect, txtPaht)

  • Private Sub Cancel_Click()
    Unload Me
    End Sub
    
    Private Sub Com1_Click() ' пометить требуемый файл и 
    ' перейти к Form1
    MsgBox ("Файл:" + FrmOpen.txtPath.Text), vbInformation
    FrmOpen.Hide
    End Sub
    
    Private Sub dirDirect_Change()
    filFile.Path = dirDirect.Path
    End Sub
    
    Private Sub drvDrive_Change()
    dirDirect.Path = drvDrive.Drive
    End Sub
    
    Private Sub filFile_Click()
    If Right(dirDirect.Path, 1) = "\" Then
    txtPath = dirDirect.Path + filFile.FileName
    Exit Sub
    Else
    txtPath.Text = dirDirect.Path + "\" + filFile.FileName
    End If
    End Sub
  • на форме FrmTarif, рис. 4.33, создать требуемые объекты и ввести код, данный ниже:

    Private Sub Cancel_Click() '
    Unload Me
    End Sub
    
    Private Sub Com1_Click() ' добавить
    ' открывает буфер для очередной записи в таблице
    datTarif.Recordset.AddNew
    End Sub
    
    Private Sub Com2_Click() ' обновить
    ' сбрасывает содержимое буфера для того, чтобы
    ' изменения были зафиксированы в записи
    datTarif.UpdateRecord
    End Sub
    
    Private Sub Com3_Click() ' выход
    Unload Me
    End Sub
    
    Private Sub Com4_Click() ' удалить
    ' уничтожает запись таблицы
    datTarif.Recordset.Delete
    End Sub
    
    Private Sub Com5_Click() ' восстановить
    ' если изменено содержимое записи, но ещё не обновлено,
    ' то восстановится предыдущее значение поля
    datTarif.UpdateControls
    End Sub
    
    Private Sub Com6_Click() ' вперёд
    datTarif.Recordset.MoveNext
    If datTarif.Recordset.EOF Then
    MsgBox "Конец таблицы!", vbOKOnly + vbInformation, " "
    datTarif.Recordset.MoveLast
    End If
    End Sub
    
    Private Sub Com7_Click() ' назад
    datTarif.Recordset.MovePrevious
    If datTarif.Recordset.BOF Then
    MsgBox "Hачало таблицы!", vbOKOnly + vbInformation, " "
    datTarif.Recordset.MoveFirst
    End If
    End Sub
    
    Private Sub Com8_Click() ' к первой записи
    datTarif.Recordset.MoveFirst
    End Sub
    
    Private Sub Com9_Click() ' к последней записи
    datTarif.Recordset.MoveLast
    End Sub
    
    Private Sub Exit_Click() ' выход
    Unload Me
    End Sub
    
    Private Sub Form_Load()
    Set NewDB = Worspaces(0).OpenDatabase(FrmOpen.txtPath.Text)
    
    ' установка свойств элементов управленя Data и TextBox
    datTarif.DatabaseName = FrmOpen.txtPath.Text
    datTarif.RecordSource = "Tarif"
    
    ' txtRazrjad.DataSource = "datTarif" ' можно испоьзовать окно 
    ' свойств
    ' txtKoeffcnt.DataSource = "datTarif"
    
    txtRazrjad.DataField = "Razrjad"
    txtKoeffcnt.DataField = "Koeffcnt"
    End Sub

    

    
    Рис. 4.33. Форма FrmTarif
  • на форме FrmKunde, рис. 4.34, создать требуемые объекты и ввести код, данный ниже:

    Private Sub Cancel_Click()
    Unload Me
    End Sub
    
    Private Sub Com1_Click() ' добавить
    datKunde.Recordset.AddNew
    End Sub
    
    Private Sub Com2_Click() ' обновить
    datKunde.UpdateRecord
    End Sub
    
    Private Sub Com3_Click() ' выход
    Unload Me
    End Sub
    
    Private Sub Com4_Click() ' удалить
    datKunde.Recordset.Delete
    End Sub
    
    Private Sub Com5_Click() ' восстановить
    datKunde.UpdateControls
    End Sub
    
    Private Sub Com6_Click() ' вперeд (к следующей записи)
    datKunde.Recordset.MoveNext
    If datKunde.Recordset.EOF Then
    MsgBox "Конец таблицы!", vbOKOnly + vbInformation, " "
    datKunde.Recordset.MoveLast
    End If
    End Sub
    
    Private Sub Com7_Click() ' назад (к предыдущей записи)
    datKunde.Recordset.MovePrevious
    If datKunde.Recordset.BOF Then
    MsgBox "Начало таблицы!", vbOKOnly + vbInformation, " "
    datKunde.Recordset.MoveFirst
    End If
    End Sub
    
    Private Sub Com8_Click() ' в начало таблицы
    datKunde.Recordset.MoveFirst
    End Sub
    
    Private Sub Com9_Click() ' в конец таблицы
    datKunde.Recordset.MoveLast
    End Sub
    
    Private Sub Exit_Click() ' выход
    Unload Me
    End Sub
    
    Private Sub Form_Load()
    Set NewDB = Worspaces(0).OpenDatabase(FrmOpen.txtPath.Text)
    
    ' yстановка свойств элементов управления Data иTextBox
    datKunde.DatabaseName = FrmOpen.txtPath.Text
    datKunde.RecordSource = "Kunde"
    
    ' txtTabnumber.DataSource = "datKunde" ' можно
    ' использовать окно свойств
    ' txtNam.DataSource = "datKunde"
    ' txtRazrjad.DataSource = "datKunde"
    
    txtRazrjad.DataField = "Razrjad"
    txtNam.DataField = "Nam"
    txtTabnumber.DataField = "Tabnumber"
    End Sub

    

    
    Рис. 4.34. Форма FrmKunde
  • на форме FrmQuery, рис. 4.35, создать требуемые объекты и ввести код, данный ниже:

    Private Sub Cancel_Click()
    Unload Me
    End Sub
    
    Private Sub Com1_Click() ' к первой записи
    NewRec.MoveFirst
    Call showfields
    End Sub
    
    Private Sub Com2_Click() ' к последней записи
    NewRec.MoveLast
    Call showfields
    End Sub
    
    Private Sub Com3_Click() ' к следующей записи
    NewRec.MoveNext
    ' если достигнут конец таблицы, то выдаeтся 
    ' соответствующее сообщение и проводится переход к 
    ' последней записи
    If NewRec.EOF Then
    MsgBox "Kонец таблицы!", vbOKOnly + vbInformation, " "
    NewRec.MoveLast
    End If
    Call showfields
    End Sub
    
    Private Sub Com4_Click() ' к предыдущей записи таблицы
    NewRec.MovePrevious
    ' если достигнуто начало таблицы, то выдаeтся 
    ' соответствующее сообщение и проводится переход
    ' к первой записи
    If NewRec.BOF Then
    MsgBox "Hачало таблицы!", vbOKOnly + vbInformation, " "
    NewRec.MoveFirst
    End If
    Call showfields
    End Sub
    
    Private Sub Exit_Click()
    NewDB.Close
    Set NewDB = Nothing
    Unload Me
    End Sub
    
    Private Sub Form_Load()
    Set NewWS = DBEngine.Workspaces(0)
    Set NewDB = NewWS.OpenDatabase(FrmOpen.txtPath.Text)
    
    ' указатель на набор данных создаeтся с помощью метода 
    ' Recordset объекта Database. Синтаксис:
    ' Set RecordSet = Database.OpenRecordSet ("имя_таблицы или
    ' SQL-запроса, из которых берут данные", dbName), здесь 
    ' dbName - константа, которая определяет тип размещения
    ' данных. В основном используется dbname = dbOpenDynaset
    ' - размещение данных в виде динамического набора, 
    ' открывающего полный доступ к таблицам: перемещение, 
    ' индексирование, редактировние и т.д.
    Set NewRec = NewDB.OpenRecordset("sql1", dbOpenDynaset)
    Call showfields
    End Sub
    
    Private Sub showfields()
    ' используемый синтаксис: NewRec(name), где NewRec - 
    ' указатель на объект Recordset, name - имя поля
    txtTabnumber.Text = NewRec("Tabnumber")
    txtName.Text = NewRec("Nam")
    txtRazrjad.Text = NewRec("Tarif.Razrjad")
    txtKoeffcnt.Text = NewRec("Koeffcnt")
    End Sub

    

    
    Рис. 4.35. Форма FrmQuery
• стартовать проект, рис. 4.36. Последовательность действий пользователя дана на форме Form1 (рис. 4.29). На рис. 4.37 даны результаты запроса (см. также рис. 4.35 - форма FrmQuery ). На рис. 4.38 запрос представлен на бланке запроса, на рис. 4.39 запрос представлен в SQL -форме. Эти данные взяты из файла базы данных (см. регистр Запрос базы данных),
• остановить выполнение проекта (рис. 4.38). Использовать кнопку Exit формы FrmQuery (рис. 4.35) и пункт меню Exit первой формы, рис. 4.29
  

  
  Рис. 4.36. Старт проекта
  

  
  Рис. 4.37. Результаты запроса
  

  
  увеличить изображение
  Рис. 4.38. Запрос на бланке запроса
  

  
  Рис. 4.39. Запрос в SQL-форме
  

  
  Рис. 4.40. Завершение выполнения проекта