Создание и заполнение базы данных MS Access

ADOX в ACCESS

Автор osmor

17.11.2006 г.

Использование ADOX для работы с файлами MS ACCESS

C приходом ADO на смену DAO, многие программисты ACCESS пришли в замешательство, а как без DAO создавать таблицы? как получить доступ у запросам?

Эта статья отвечает на эти вопросы.

Вся информация, изложенная в данной статье, найдена в MSDN.

Полное название ADOX — Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security.

Из названия видно, что эта библиотека является расширением ADO для Data Definition Language (DDL - языка определения данных), который позволяет получить доступ к объектам БД и безопасности.

Мы не будем останавливаться на общих вопросах использования ADOX, а сразу перейдем к тому, что нам дает использование ADOX непосредственно в ACCESS.

И так, ADOX позволяет получить информацию о таблицах их полях, индексах и ключах, сохраненных запросах, пользователях и группах, а так же создавать, изменять и удалять эти объекты БД ACCESS.

ADOX НЕ позволяет получить информацию о формах, отчетах, макросах, страницах доступа к данным и модулях БД ACCESS.

Объектная модель ADOX представляет собой следующую иерархию объектов и коллекций:

Подробную информацию о каждом объекте можно найти в MSDN:

(

http://msdn.microsoft.com/libr... a54e20.asp

)

Мы же сразу перейдем к практике.

Чтобы начать работу с ADOX нужно, прежде всего, получить объект Catalog, в MSA сделать это можно двумя способами:

1. Установить ссылку на библиотеку "Microsoft ADO Ext. 2.8 for DDL and Security" (так называемое раннее связывание), для этого:

при редактировании модуля нужно открыть меню "Tools-References"

найти в списке подключаемых библиотек "Microsoft ADO Ext. 2.8 for DDL and Security"

поставить "галку" напротив этой библиотеки

нажать кнопку "ОК "

Далее для получения объекта Catalog используем следующую конструкцию:

Visual BasicВыделить код

1
2

Dim adoxCat As ADOX.Catalog
Set adoxCat = New ADOX.Catalog

или просто :

2. Создание объекта Catalog с использованием процедуры CreateObject. В этом случае, никаких предварительных действий, для использования ADOX, совершать не нужно. Просто в том месте процедуры, где нужно получить Catalog пишем:

Visual BasicВыделить код

1
2

Dim adoxCat As Object
Set adoxCat = CreateObject("ADOX.Catalog")

Начало положено, теперь нужно объяснить созданному объекту, с какой именно базой мы собираемся работать. Для этой цели служит свойство ActiveConnection, ему нужно присвоить объект ADODB.Connection или строку подключения.

Например, для работы с объектами текущей БД можно написать:

Visual BasicВыделить код

| | |
| --- | --- |
| 1 | Set adoxCat.ActiveConnection = CurrentProject.Connection |

или

Visual BasicВыделить код

| | |
| --- | --- |
| 1 | adoxCat.ActiveConnection = CurrentProject.Connection |

или

Visual BasicВыделить код

| | |
| --- | --- |
| 1 | adoxCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.FullName |

Ну, вот собственно и все, самое сложное уже позади. Теперь используя свойства и методы полученного ADOX.Catalog, который подключен к нужной нам базе, будем работать с объектами БД ACCESS.

Далее приведен код для решения нескольких конкретных задач

(код проверки наличия запроса или таблицы, а так же обработчики ошибок умышленно не включены для упрощения кода, в реальных приложениях все это необходимо добавить):

Visual BasicВыделить код

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184

Option Compare Database
Option Explicit
 
--------------------------------------------------------------------------------
 
Public Sub ADOXListTables()
'список всех таблиц БД с указанием типа таблицы
Dim adoxCat As ADOX.Catalog
Dim adoxTbl As ADOX.Table
Dim strType As String
Set adoxCat = New ADOX.Catalog
 
'получаем ссылку на текущую БД
adoxCat.ActiveConnection = CurrentProject.Connection
' проверяем свойство TYPE у всех объектов Table
   For Each adoxTbl In adoxCat.Tables
     Select Case adoxTbl.Type
      Case "LINK" ' связанная таблица JET
        strType = " - связанная таблица JET"
      Case "TABLE" ' обычная таблица
        strType = " - простая таблица"
      Case "ACCESS TABLE" 'системная таблица ACCESS
        strType = "- системная таблица MSA"
      Case "SYSTEM TABLE" 'системная таблица JET
        strType = " - системная таблица JET"
      Case "PASS-THROUGH" 'Связанная таблица ODBC
        strType = " - Связанная таблица ODBC"
      Case Else ' какая-то другая
        strType = " - какая-то другая таблица"
    End Select
      Debug.Print adoxTbl.Name; strType
   Next
Set adoxCat = Nothing
Set adoxTbl = Nothing
End Sub
 
--------------------------------------------------------------------------------
 
Public Sub ADOXCreateLinkTable(strFileName As String, _
                                strTblName As String, _
                                Optional strLinkTblName As String = "")
'создание связанной таблицы MS ACCESS
'strFileName - путь к БД ACCESS, в которой находится таблица
'strTblName - имя таблицы в исходной БД
'strLinkTblName - имя, под которым будет создана связанная таблица
'если strLinkTblName опущено, то создается таблица с именем strFileName
Dim adoxCat As ADOX.Catalog
Dim adoxTbl As New ADOX.Table
Dim strType As String
Set adoxCat = New ADOX.Catalog
'получаем ссылку на текущую БД
adoxCat.ActiveConnection = CurrentProject.Connection
If strLinkTblName = "" Then strLinkTblName = strTblName
With adoxTbl
    .ParentCatalog = adoxCat
    .Name = strLinkTblName 'имя создаваемой таблицы
    'путь к файлу
    .Properties("Jet OLEDB:Link Datasource").Value = strFileName
    'имя таблицы в исходном файле
    .Properties("Jet OLEDB:Remote Table Name").Value = strTblName
    .Properties("Jet OLEDB:Create Link").Value = True
End With
adoxCat.Tables.Append adoxTbl 'добавляем таблицу в БД
Set adoxCat = Nothing
Set adoxTbl = Nothing
 
End Sub
 
--------------------------------------------------------------------------------
 
Public Sub ADOXRefreshLinks(strFileName As String)
'Обновление связей всех связанных таблиц MS ACCESS
'strFileName - путь к БД ACCESS, в которой находятся таблицы
Dim adoxCat As ADOX.Catalog
Dim adoxTbl As ADOX.Table
 
Set adoxCat = New ADOX.Catalog
adoxCat.ActiveConnection = CurrentProject.Connection
 
For Each adoxTbl In adoxCat.Tables
' если таблица, связанная таблица JET
    If adoxTbl.Type = "LINK" Then
        adoxTbl.Properties("Jet OLEDB:Link Datasource") = strFileName
    End If
Next
Set adoxCat = Nothing
Set adoxTbl = Nothing
End Sub
 
--------------------------------------------------------------------------------
 
Public Sub ADOXCreateTable()
'создание новой таблицы tblOrders с тремя полями:
'idOrder - последовательный счетчик, ключевое поле таблицы
'NumOrder - текстовое, 12 символов, с условием на значение и сообщением об ошибке
'dtOrder - дата, значение по умолчанию равно Date()
'проверка на существование таблицы и обработчик ошибок умышленно не включены для упрощения кода
Dim adoxCat As ADOX.Catalog
Dim adoxTbl As New ADOX.Table
Dim adoxCol As ADOX.Column
Set adoxCat = New ADOX.Catalog
'получаем ссылку на текущую БД
   adoxCat.ActiveConnection = CurrentProject.Connection
 
 With adoxTbl
    .Name = "tblOrders" 'имя таблицы
    ' создаем новый объект Column (он же поле таблицы)
    Set adoxCol = New ADOX.Column
    With adoxCol
     .ParentCatalog = adoxCat 'ссылка на каталог
     .Name = "id" 'имя поля
     .Type = adInteger 'тип поля
     .Properties("AutoIncrement").Value = True ' будет счетчик
    End With
    .Columns.Append adoxCol, adInteger ' добавляем поле в коллекцию
    'добавляем новый индекс
    .Keys.Append "PrimaryKey", adKeyPrimary, "id"
 
    'аналогично для всех полей таблицы
    ' поле № заказа
    Set adoxCol = New ADOX.Column
    With adoxCol
     .ParentCatalog = adoxCat
     .Name = "NumOrder"
     .Type = adVarWChar  'тип поля
     .DefinedSize = 12
     .Properties("Description").Value = "№ заказа"
     'условие на значение
     .Properties("Jet OLEDB:Column Validation Rule").Value = "<>'' and not is null"
     'сообщение о нарушении условия на значение
     .Properties("Jet OLEDB:Column Validation Text").Value = "Укажите номер заказа"
    End With
    .Columns.Append adoxCol, adVarWChar
 
    ' поле дата заказа
    Set adoxCol = New ADOX.Column
    With adoxCol
     .ParentCatalog = adoxCat
     .Name = "dtOrder"
     .Type = adDate 'тип поля
     .Properties("Description").Value = "Дата заказа"
     .Properties("Default").Value = "date()" ' значение по умолчанию
    End With
    .Columns.Append adoxCol, adDate
 
 
  'добавляем таблицу в БД
   adoxCat.Tables.Append adoxTbl
 
 End With
   Set adoxCat = Nothing
   Set adoxTbl = Nothing
   Set adoxCol = Nothing
 
End Sub
 
--------------------------------------------------------------------------------
Public Sub ADOXListIndexTbl(strTblName As String)
'список индексов таблицы + некоторые свойства индекса +
'список полей индекса
'strTblName - имя таблицы в БД
Dim adoxCat As ADOX.Catalog
Dim adoxTbl As ADOX.Table
Dim adoxInx As ADOX.Index
Dim adoxCol As ADOX.Column
Set adoxCat = New ADOX.Catalog
adoxCat.ActiveConnection = CurrentProject.Connection
 
Set adoxTbl = adoxCat.Tables(strTblName)
For Each adoxInx In adoxTbl.Indexes
    With adoxInx
        Debug.Print "_________________________________"
        Debug.Print "Индекс - "; .Name
        Debug.Print "Первичный - "; .PrimaryKey
        Debug.Print "Уникальный - "; .Unique
        Debug.Print "Поля индекса : ";
        For Each adoxCol In .Columns
           Debug.Print adoxCol.Name; "; ";
        Next
        Debug.Print
    End With
Next
 
End Sub

--------------------------------------------------------------------------------

Прежде чем рассмотреть код для работы с запросами небольшое замечание.

Access не разделяет запросы на View и Procedure, но ADOX разделяет. Теоретически простые Select запросы без параметров должны попадать в коллекцию Views, а все остальные запросы (в том числе и Select c параметрами) в коллекцию Procedures.

На практике, так происходит только с теми запросами, которые Вы создали "руками" в среде MS ACCESS в текущем файле.

ВСЕ запросы, которые Вы импортируете из других баз или создадите программно, попадут в коллекцию Procedures.

http://support.microsoft.com/d... -us;252888

Кроме того, есть проблемы при создании запросов в ACCESS 2000:

http://support.microsoft.com/d... -us;246213

Visual BasicВыделить код

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

Public Sub ADOXListQuerys()
'список запросов в базе
Dim adoxCat As ADOX.Catalog
Dim i As Integer
Set adoxCat = New ADOX.Catalog
'получаем ссылку на текущую БД
adoxCat.ActiveConnection = CurrentProject.Connection
With adoxCat
    Debug.Print "запросы в коллекции Views"
    If .Views.Count = 0 Then
        Debug.Print "Нет запросов в коллекции View"
    Else
       For i = 0 To .Views.Count - 1
          Debug.Print .Views(i).Name
        Next
      End If
    Debug.Print "Запросы в коллекции Procedures"
    If .Procedures.Count = 0 Then
        Debug.Print "Нет запросов в коллекции Procedures"
    Else
       For i = 0 To .Procedures.Count - 1
          Debug.Print .Procedures(i).Name
        Next
    End If
End With
End Sub
 
 
--------------------------------------------------------------------------------
 
Public Sub ADOXcreateQuery()
'создание нового запроса
Dim adoxCat As ADOX.Catalog
Dim qdf As ADODB.Command
Set adoxCat = New ADOX.Catalog
adoxCat.ActiveConnection = CurrentProject.Connection
'новый объект Command
Set qdf = New ADODB.Command
'текст запроса
qdf.CommandText = "select  from tblOrders"
'добавляем новый запрос в базу
adoxCat.Views.Append "Новый запрос", qdf
End Sub

Вот и все, как говорится, пишите письма.