Очень часто приходится писать программы работающие с БД , решение этой задачи напрашивается само по себе – создаешь Базу Данных в Access, а затем пишешь программу-оболочку. Но что делать, если заказчику необходимо, к примеру иметь отдельные БД для каждого календарного года, или же ежемесячные (ежеквартальные), но что б обрабатывались они по единому принципу? Ведь не будете же вы каждый месяц или год бегать к нему и создавать в Access все новые и новые БД, тем более если вы работаете на заказ в 'оффшоре', а создавать их заранее, как то несолидно и граничит с абсурдом. А выход из этой ситуации есть – это VB.

   Используя команды VB можно написать программу, которая создает БД или разработать программу, которая сможет генерировать новые БД по ходу ее выполнения. В этой статье я попытаюсь рассказать о способах и методах программного создания Баз Данных и их управления.

    Создание Базы Данных

   Visual Basic содержит два объекта доступа к данным BDEngine и Workspace. Для создания БД применяется метод CreateDatabase, который является частью объекта Workspace. Рабочая область (Workspace) создается при каждом доступе к Jet-машине. При необходимости можно открывать другие рабочие области. Если при работе с функциями не указывается ID-номер рабочей области, то подразумевается рабочая область по умолчанию. При помощи оператора Dim (или Public для модуля) описываем переменную NewDB, как объект БД, создаем рабочую область и с помощью метода CreateDatabase создаем Базу Данных.

    Примечание. Установите в Refereces… вашего проекта ссылку на соответствующую библиотеку Microsoft DAO 3.51 (или 3.6) Object Library. [ 3.51 – при наличии Access 97, 3.6 при наличии Access 2000 ]

'Объявляем необходимые переменные

Dim NewDB As Database

Dim NewWs As WorkSpace

Dim strDBPath As String

'Устанавливаем путь к создаваемой БД с указанием ее имени (MyDB.mdb) StrDBPath = “C:\ExamplDB\MyDB.mdb”

‘Создаем рабочее пространство

Set NewWs = DBEngine.Workspaces(0)

'Создаем Базу Данных

Set NewDB = NewWs.CreateDatabase (strDBPath, dbLangGeneral)

    Совет. Для получения имени файла БД и пути к ней можно использовать диалоговое окно Open, эл. управления CommonDialog.

    Константа dbLangGeneral является обязательным параметром метода CreateDatabase . Она описывает язык и кодовую страницу для американских компьютеров. Для русской версии употребляют dbLangCyrillic. У метода CreateDatabase имеется еще один параметр, этот аргумент позволяет создавать БД для Jet-машины версий 1.0; 1,1; 2,5; 3.0; 3,5 и 3.6 и шифровать БД. Для вызова этих опций нужно просуммировать константы типа long, integer и включить результат, как последний аргумент функции.

Dim NewDB As Database

Dim NewWs As WorkSpace

Dim lngDBOpts As Long

Dim strDBPath As String

StrDBPath = “C:\ExamplDB\MyDB.mdb”

'Устанавливаем опции

LngDBOpts = dbVersion30 + dbEncrypt

‘Создаем рабочее пространство

Set NewWs = DBEngine.Workspaces(0)

'Создаем Базу Данных

Set NewDB = NewWs.CreateDatabase (strDBPath, dbLangCyrillic, lngDBOpts)

‘После создания БД, закрываем ее и рабочее пространство

NewDB.Close

NewWs.Close

'Уничтожаем объекты

Set NewDB = Nothing

Set NewWs = Nothing

    После выполнения этого кода у вас создается БД, можете посмотреть. Но не удивляйтесь если она пуста, т.к нам еще только предстоит заполнить ее таблицами. Внимание. При использовании метода CreateDatabase , если создаваемый файл (*.mdb) уже существует, возникает перехватываемая ошибка - 3204 – 'Database alredy exists'. Необходимо включить код для ее перехвата.

    И так, мы создали БД. Но созданный файл – пуст и с ним ничего нельзя делать пока не будут созданы таблицы.

  Создание Таблиц

    Для создания новой таблицы неоходимо определить новый объект TableDef (Table Definition – описание таблицы). Объект TableDef содержит всю необходимую информацию для определения таблицы БД. Он описывает в БД тип информации, хранимой в таблице и некоторые факультативные свойства этой таблицы. Объект TableDef содержит 3 коллекции, 5 методов и 10 свойств. Свойства объекта TableDef устанавливаются при создании таблицы и их значения различаются для собственных таблиц Microsoft Jet и присоединеных таблиц. Следующие операторы показывают, как создать объект TableDef и определить имя таблицы:

'Объявлеям необходимые переменные

Dim NewDB As Database

Dim NewWs As WorkSpace

Dim NewTbl As TableDef 

Dim strDBPath As String 

'Устанавливаем путь к создаваемой БД с указанием ее имени (MyDB.mdb)

StrDBPath = “C:\ExamplDB\MyDB.mdb”

‘Создаем рабочее пространство

Set NewWs = DBEngine.Workspaces(0)

'Создаем Базу Данных 

Set NewDB = NewWs.CreateDatabase (strDBPath, dbLangGeneral)

‘Создаем таблицу

Set NewTbl = NewDB.CreateTableDef (Name) 

    Свойство таблицы Name является одним из свойств объекта TableDef, и обычно это свойство единственное, которое необходимо для создания таблицы БД Access.

    При подключении к БД внешней таблицы можно использовать некоторые другие свойства (Attributes, Connect, SourceTableName и т.д – рассматривать мы их не будем). Эти свойства можно указывать как последовательные параметры 
метода CreateTableDef. Можно также определять и другие свойства, давая им некоторые значения. Эти операторы должны следовать за методом CreateTableDef. После выполнения вышеприведенного кода вы создадите таблицу, но это не все ( в принципе она у вас не создастся), необходимо описать хотябы одно поле, для того чтобы действительно таблица появилась в БД. Как описывать и создавать поля мы рассмотрим в другой статье, а сейчас без объяснений, кратко опишем одно поле и наконец создадим таблицу.

'Объявляем переменную для поля

Dim Fl As Field

'Создаем поле с его атрибутами

Set Fl = NewTbl.CreateField (“MyField”, dbText, 20)

    Теперь нампредстоит финальная часть, т.е. присоединение созданной таблицы к БД. Это делается с помощью метода Append объекта TableDef. Но сначала нам необходимо добавить в нашу таблицу поле (!), а затем саму таблицу к БД.

'Добавляем поле к таблице 

NewTbl.Fields.Append Fl

'Добавляем таблицу к БД 

NewDb.TableDefs.Append NewTbl 

    Внимание. При добавлении таблицы к БД, если создаваемая таблица уже существует, возникает перехватываемая ошибка - 3010 – 'Table ''Имя введенной таблицы'' alredy exists'. Так же, если имя таблицы пусто или содержит недопустимые символы возникает перехватываемая ошибка – 3125. Необходимо включить код для их перехвата.

    Удаление Таблиц. Удаление таблицы из БД осуществляется с помощью метода Delete объекта TableDef. NewDb.TableDefs.Delete “MyTable”

    Внимание. Метод Delete нужно использовать с большой осторожностью. При удалении таблицы все индексы и – самое главное – данные также удаляются.

    При удалении таблицы, если таблица уже удалена, возникает перехватываемая ошибка - 3265, так же, если имя таблицы пусто или содержит недопустимые символы возникает перехватываемая ошибка – 3125.

    После того как для новой таблицы определен объект TableDef необходимо описать объекты полей. Таблица может иметь одно или множество полей.

Создание полей

Описание полей

    Поля таблицы описываются с помощью объекта Field, который содержит всю информацию о поле таблицы данных. Для каждого поля следует указать имя и тип. В зависимости типа поля описание других свойств может быть обязательным или нет. Существуют 17 свойств объекта Field. С их помощью можно определять размер и тип поля, и так же является это поле 'родным' Microsoft Jet или это присоединенное поле внешней базы. Visual Basic распознает 14 различных типов полей данных.

Таблица N1 Тип полей данных 

    Многие из типов полей данных понятны и поэтому чуть подробнее опишу некоторые из оставшихся.

Поля типа LONGBINARY предназначены для хранения изображений и OLE – объектов;

Поле BOOLEAN чаще всего применяется для для хранения результатов выбора в связанных флажках, где –1 – True, а 0 – False. НО будьте осторожны при вводе информации в эти поля, т.к. любое отличное от 0 числи будет преобразовываться в –1;

Поле типа BYTE воспринимает только числа от 0 – 255, любое другое значение вызовет ошибку 524;

Тип поля COUNTER является автоматически наращиваемым и очень часто 
применяется в качестве уникального первичного ключа. Оно предназначено только для чтения;

Поле типа GUID используется для хранения 128 – разрядного числа специального типа;

    Теперь после небольшого обзора настало время создать и описать поля. Поля создаются с помощью метода CreateField. Имеются два метода создания полей.

Первый метод

    С помощью метода CreateField определяем имя поля, тип и размер.

‘Объявляем переменные

Dim F1 As Field, F2 As Field, F3 As Field

'Устанавливаем имя, тип и размер поля

Set F1 = NewTbl.CreateField (“FirstName”, dbText, 20)

Set F2 = NewTbl.CreateField (“LastName”, dbText, 20)

Set F3 = NewTbl.CreateField (“Age”, dbInteger)

    После того как поля включаемые в таблицу описаны, при помощи метода Append объекта TableDef, эти поля добавляются к таблице.

NewTbl.Fields.Append F1

NewTbl.Fields.Append F2

NewTbl.Fields.Append F3

Второй метод

    Можно вызвать метод CreateField без всяких параметров, а имя, тип и размер установить потом при помощи оператора присваивания. 

‘Объявляем переменную

Dim F1 As Field 

'Создаем поле

Set F1 = NewTbl.CreateField ()

‘Свойства поля устанавливаем явно

F1.Name = “FirstName”

F1.Type = dbText

F1.Size = 20

    После того как поле включаемое в таблицу описано, при помощи метода Append объекта TableDef, оно добавляется к таблице.

NewTbl.Fields.Append F1

Установка факультативных свойств полей

    Кроме имени поля, типа хранимых в нем данных и размера поля, можно определить еще некоторые свойства поля влияющие на его работу. Первым из этих свойств является Attributes. При создании полей используются два его основных значения. Первое – автоинкремент – задает приращение значения поля БД всякий раз, когда добавляется новая запись. С помощью данного свойства можно организовать счетчик записей, чтобы обеспечить уникальное значение этого поля. Тогда поле с автоинкрементом можно использовать, как поле первичного ключа (Primary Key).

Примечание. Автоинкремент можно установить только для полей с типом данных Long.

‘Объявляем переменную

Dim F1 As Field

'Создаем поле

Set F1 = NewTbl.CreateField (“ID”, dbLong)

‘Устанавливаем свойства автоинкремента

F1.Attributies = dbAuotoIncrField

‘Добавляем к таблице

NewTbl.Fields.Append F1

    Другое факультативное значение – updatable позволяет разрешать или запрещать изменение поля. Обычно эта установка не используется при начальном создании поля.

    Другие константы, которые можно использовать вместе со свойством Attributes приведены ниже. Этому свойству можно задавать сложные значения, для чего следует объединить значения отдельных констант и присвоить свойству их суммы.

    Кроме свойств Attributes для отдельных полей можно определять некоторые другие свойства. Для изменения значений этих свойств, также как и для свойства Attributes, используются операторы присваивания; эти значения нельзя установить при вызове метода CreateField. 

Свойство Комментарии 

AllowZeroLenght - Определяет, может ли поле Text или Memo иметь нулевую длину. Значение True – разрешает ввод пустых строк.

DefaulValue - Позволяет задавать значение поля по умолчанию

Required - Определяет, обязательно ли поле должно иметь значение

ValidationRule - Устанавливает условие, которому должно отвечать поле для того, чтобы запись можно было обновить

ValidationText - Определяет сообщение об ошибке, выводимое в том случае, когда нарушается правило проверки достоверности поля(ValidationRule)

‘Объявляем переменные

Dim F3 As Field

'Устанавливаем имя, тип и размер поля

Set F3 = NewTbl.CreateField (“Age”, dbInteger)

'Устанавливаем факультативные свойства

F3.ValidationRule = “Age” > 0

F3.ValidationText = “Возраст должен быть больше 0”

‘Добавляем к таблице 

NewTbl.Fields.Append F3

Удаление полей

    Для удаления поля используется метод Delete.

‘Удаляем из таблицы NewTbl поле Age

NewTbl.Fields.Delete “Age”

или

‘Удаляем из БД NewDB, таблицы с имене Name поле Age

 NewDB.TableDefs(Name).Fields.Delete “Age”

    И так вы создали БД, затем таблицу (или таблицы), описали и присоединили поля к таблице и кажется БД готова. Но нет! БД еще немного сыровата. Есть еще один ключевой момент – индексы. Индексами или индексными полями, как их иногда называют, являются знакомые вам ключевые поля. Необходимо отметить, что почти во всех БД индексы используются только для ускорения обработки больших файлов и выполняют небольшую роль в поддержке отношений между таблицами. В используемых Visual Basic БД (mdb-файлы), ключевые поля используют и для установки ограничения целостности, и для ускорения поиска и для выборки информации.

Создание индексов

    Способ создания индексов тесно связан с созданием самой таблицы. Для каждого индекса нужно определить имя, описать поля, включаемые в индекс, и указать, будет ли данный индекс первичным и допускаются ли повторяющиеся значения в полях образующих индексный ключ.

Создание первичного ключа (PrimaryKey)

    Главной задачей первичного ключа является поддержка внутренней целостности таблицы и поэтому в ней не может быть две одинаковой записи. Обычно первичный ключ используют в полях, где содержатся номера телефонов, счета фактур и т.д. Для создания индекса нужно выполнить следующие действия (считаем, что БД и Таблица уже созданы, поля описаны и добавлены к таблице):

    При помощи метода CreateIndex объекта TableDef, создаем объект Index; Устанавливаем свойства индекса; Добавляем индекс к таблице; Здесь приводится код создания индекса: 

Dim Idx1 As Index

‘Создаем объект индекс

Set Idx1 = NewTbl.CreateIndex("PrimaryKey")

'Устанавливаем его свойства

Idx1.Name = "PrimaryKey"

Idx1.Primary = True

‘Свойство Fields должно быть таким же, как имя поля котороe становится индексным

Idx1.Fields = "ID" 

'Прибавляем индекс к таблице

NewTbl.Indexes.Append Idx1

    После выполнения все этих процедур можно добавить таблицу к БД. 

Примечание. При создании первичного индекса в программу необходимо включить методы, обеспечивающие для всех добавляемых записей уникальные, не пустые значения полей или обработчик ошибок, т.к. при появлении записи с повторяющимся или пустым значением приведет к ошибке. В таблице может быть только один первичный индекс.

Создание внешнего ключа

    Главной задачей внешнего ключа является определение и поддержка отношений между таблицами в БД. Поэтому внешние ключи не являются уникальными для той таблицы в которой они находятся. Внешние ключи (или не первичные индексы) так же могут обеспечивать уникальность полей, для этого используется свойство Unique. Установив его в True вы гарантируете введение уникальных (т.е. не повторяющихся ) значений.

    В объектах полей имеется только одно свойство, которое относится к созданию индексов – это свойство определяет порядок сортировки по возрастанию или по убыванию. По умолчанию устанавливается порядок по возрастанию, если необходимо сортировать по убыванию, то свойству Attribues поля, присваивают константу dbDesiending.

    Код для создания вторичных индексов совершенно одинаков с кодом создания первичных индексов:

Dim Idx1 As Index

Dim Idx2 As Index

‘Создаем объект индекс

Set Idx1 = NewTbl.CreateIndex("PrimaryKey")

Set Idx2 = NewTbl.CreateIndex("SecondaryKey")

'Устанавливаем свойства Idx1

Idx1.Name = "PrimaryKey"

Idx1.Primary = True

‘Свойство Fields должно быть таким же, как имя поля котороe становится индексным

Idx1.Fields = "ID" 

'Устанавливаем свойства Idx2

Idx2.Name = "SecondaryKey"

Idx2.Primary = False ‘а можно вообще не устанавливать

‘Свойство Fields должно быть таким же, как имя поля котороe становится индексным

Idx2.Fields = "Phone"

Idx2.Unique= True

'Прибавляем индекс к таблице

NewTbl.Indexes.Append Idx1

NewTbl.Indexes.Append Idx2

Примечание Одна таблица может иметь не более 32 индексов.

Удаление индекса

Для удаления индекса достаточно вызвать метод Delete объекта TableDef. Код для удаления индекса PrimaryKey из таблицы (к примеру Table1).

NewDB.TableDefs("Table1").Indexes.Delete "PrimaryKey"

Примечание Нельзя удалить индекс используемей в отношении. Для удаления такого индекса нужно сначала разорвать их, а потом удалить индекс.

    Наконец БД создана. У вас имеются полноценные таблицы, с которыми вы можете спокойно работать, но для получения полной информации вам иногда придется в каждую таблицу заносить одну и туже информацию. При этом таблица (или таблицы), начнут 'пухнуть' и в конечном итоге ваша БД станет неповоротливым монстром. Для того, что бы избежать этого необходимо свести идентичные данные в отдельные таблицы, а что бы они (данные) не потеряли смысл, связать их с теми таблицами из которых они были изъяты. Таким образом, связывая таблицы друг с другом мы создаем осмысленные отношения между таблицами.

Создание Отношений

    Устанавливая отношения между таблицами, можно создать легко поддерживаемые, гибкие базы данных. Таблицы связываются в Jet – машине посредством объекта Relation, хранящегося в БД. Объект Relation содержит информацию о том, какие таблицы связаны и какая таблица является главной, а какая подчиненной. Отношения между таблицами устанавливаются с помощью индексных полей (создание индексов описано в предыдущей статье). Мы рассмотрим два вида отношений, которые можно создать между таблицами.

Отношения – 'один – к – одному'

    Отношения 'один – к – одному' используются для связи единичной записи главной таблицы с единичной записью другой таблицы. Для создания отношения необходимо описать переменную объекта Relation:

Dim MyRelation As Relation

Затем создаем сам объект Relation, его создаем с помощью метода 

CreateRelation объекта Database:

Set MyRelation=NewDB.CreateRelation ("Name"),

- где Name - строковая переменная являющаяся уникальным именем для вновь создаваемого обекта Relation

После создания объекта Relation определяем его свойства:

Определяем главную таблицу 

MyRelation.Table = "Name",

- где Name – имя главной таблицы;

Определяем подчиненную таблицу 

MyRelation.ForeignTable = "Name",

- где Name – имя подчиненной таблицы;

Устанавливаем атрибуты 

MyRelation. Attributes = dbRelationUnique,

- где dbRelationUnique – устанавливает отношение - 'один – к – одному', другие атрибуты объекта Relation, в этой статье мы рассматривать не будем.

    Теперь нам необходимо определить поля которые будут использоваться в отношении. При помощи метода CreateField объекта Relation создаем в первичной таблице поле отношения. Для этого сначала описываем переменную объекта Field.

Dim RelField As Field

А теперь создаем поле.

Set RelField = MyRelation.CreateField ("Name"),

- где Name – имя поля в первичной таблице по которому будет создано отношение.

Установим свойствовнешнего поля объекта Field.

RelField.ForeignName = "Name",

- где Name – имя поля в подчиненной таблице по которому будет создано отношение. В завершении добавляем поле отношения к оъекту Relation, а затем сам объект к Базе Данных.

MyRelation.Fields.Append RelField

NewDB.Realtions.Append MyRelation

Отношение 'один – ко – многим'

    Отношение 'один – ко – многим' используется для связывания единичной записи главной таблицы с несколькими записями в другой таблице. Создание отношения 'один – ко – многим' идентичен созданию отношению 'один – к – одному', толко в описании атрибута MyRelation необходимо поставить '0'.

MyRelation. Attributes = 0

Удаление Отношений

    Для удаления отношения можно воспользоваться методом Delete объекта БД. 

NewDB.Relations.Delete "Name",

- где Name - строковая переменная являющаяся уникальным именем созданного объекта Relation.

Взять пример к этой статье можно здесь.