ADOX 是 ActiveX Data Objects Extensions for Data Definition Language and Security，它是ADO的一个扩展，专门用于数据定义语言（DDL）和安全性。ADOX 提供了一组对象，允许用户通过编程方式创建、修改或删除数据库对象，如表、视图、用户、组等。在使用ADOX时，确保已经安装了相应的数据提供者，并且具有对数据库进行操作的权限。ADOX通常用于更高级的数据库管理和安全性任务。
CreateObject(“ADOX.Key”)
ADOX.Key对象用于访问数据库的主键、外键、唯一键
可以从Keys对象集合中提取或枚举Key对象
ADOX.Key对象有6个属性0个方法0个事件
（一）属性：
Columns 键的字段(只读)。类型：Columns对象
DeleteRule 外键在关联字段删除后的处理方式。类型：Long。缺省值adRINone
Name 键名。类型：String
RelatedTable 外键的关联表。类型：String
Type 键的类型。类型：Long。缺省值adKeyPrimary
UpdateRule 外键在关联字段修改后的处理方式。类型：Long。缺省值adRINone
（二）说明：
Type的取值范围，参见下面示例中的KeyTypeEnum
DeleteRule和UpdateRule的取值范围，参见下面示例中的RuleEnum
Access数据库中
DeleteRule 相当于JQL中的 ON DELETE 关键字，不支持adRISetDefault
UpdateRule 相当于JQL中的 ON UPDATE 关键字，不支持adRISetDefault和adRISetNull
已在Keys对象集合中的Key对象，所有属性均为只读
（三）示例：
‘示例：Key对象，Access数据库
Option Explicit
Dim oFSO, oConnection, oCatalog, oKey, oKeys, oColumn, oRecordset, s, i
Const FILE = “D:\1\1.mdb” ‘数据库文件，要求目标文件夹已存在
‘KeyTypeEnum
Const adKeyPrimary = 1 ‘主键(默认值)
Const adKeyForeign = 2 ‘外键
Const adKeyUnique = 3 ‘唯一键
‘RuleEnum
Const adRICascade = 1 ‘外键随关联字段一起改变或一起删除
Const adRINone = 0 ‘关联字段改变或删除时，外键无任何操作(默认值)
Const adRISetDefault = 3 ‘关联字段改变或删除时，外键设为字段的默认值
Const adRISetNull = 2 ‘关联字段改变或删除时，外键值设为Null
Call RunAs32() ‘使vbs运行在32位应用程序模式
s = “20240614” ‘数据库密码
‘连接字符串
s = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & FILE & “;Jet OLEDB:Database Password=” & s
‘删除数据库文件并新建，防止已有数据库文件导致示例出错
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
If oFSO.FileExists(FILE) Then oFSO.DeleteFile FILE, True
CreateObject(“ADOX.Catalog”).Create s
Set oConnection = CreateObject(“ADODB.Connection”)
oConnection.Open s
oConnection.Execute “CREATE TABLE 表1(字段1 INT, 字段2 INT, 字段3 INT, ” & _
“CONSTRAINT p1 PRIMARY KEY (字段1, 字段2), ” & _
“CONSTRAINT u1 UNIQUE (字段3))”
oConnection.Execute “INSERT INTO 表1 VALUES(10, 20, 30)”
oConnection.Execute “INSERT INTO 表1 VALUES(11, 21, 31)”
oConnection.Execute “INSERT INTO 表1 VALUES(12, 22, 32)”
oConnection.Execute “CREATE TABLE 表2(字段4 INT, 字段5 INT, 字段6 INT)”
Set oCatalog = CreateObject(“ADOX.Catalog”)
Set oCatalog.ActiveConnection = oConnection
Set oKeys = oCatalog.Tables.Item(“表2”).Keys
Set oKey = CreateObject(“ADOX.Key”)
oKey.Name = “主键”
oKey.Type = adKeyPrimary
oKey.Columns.Append “字段4”
oKeys.Append oKey
Set oKey = CreateObject(“ADOX.Key”)
oKey.Name = “外键1”
oKey.DeleteRule = adRISetNull
oKey.UpdateRule = adRICascade
oKey.Type = adKeyForeign
oKey.RelatedTable = “表1”
Set oColumn = CreateObject(“ADOX.Column”)
oColumn.Name = “字段5”
oColumn.RelatedColumn = “字段1”
oKey.Columns.Append oColumn
Set oColumn = CreateObject(“ADOX.Column”)
oColumn.Name = “字段6”
oColumn.RelatedColumn = “字段2”
oKey.Columns.Append oColumn
oKeys.Append oKey
Set oKey = oCatalog.Tables.Item(“表2”).Keys.Item(“外键1”)
s = “Name” & vbTab & vbTab & oKey.Name & vbLf & _
“Columns” & vbTab & vbTab & JoinColumns(oKey.Columns) & vbLf & _
“Type” & vbTab & vbTab & KeyTypeEnum(oKey.Type) & vbLf & _
“RelatedTable” & vbTab & oKey.RelatedTable & vbLf & _
“DeleteRule” & vbTab & RuleEnum(oKey.DeleteRule) & vbLf & _
“UpdateRule” & vbTab & RuleEnum(oKey.UpdateRule)
MsgBox s
oConnection.Execute “INSERT INTO 表2 VALUES(1, 10, 20)”
oConnection.Execute “INSERT INTO 表2 VALUES(2, 10, 20)”
oConnection.Execute “INSERT INTO 表2 VALUES(3, 11, 21)”
Set oRecordset = CreateObject(“Adodb.Recordset”)
oRecordset.Open “SELECT * FROM 表2”, oConnection
s = “”
For i = 0 To oRecordset.Fields.Count – 1
s = s & oRecordset(i).Name & vbTab
Next
s = Left(s, Len(s) – 1) & vbLf & vbLf
Do Until oRecordset.EOF = True
For i = 0 To oRecordset.Fields.Count – 1
s = s & oRecordset(i).Value & vbTab
Next
s = Left(s, Len(s) – 1) & vbLf
oRecordset.MoveNext()
Loop
oRecordset.Close()
MsgBox Left(s, Len(s) – 1)
oConnection.Execute “UPDATE 表1 SET 字段1 = 33, 字段2 = 44 WHERE 字段1 = 10”
oRecordset.Open “SELECT * FROM 表2”, oConnection
s = “关联字段改变后” & vbLf & vbLf
For i = 0 To oRecordset.Fields.Count – 1
s = s & oRecordset(i).Name & vbTab
Next
s = Left(s, Len(s) – 1) & vbLf & vbLf
Do Until oRecordset.EOF = True
For i = 0 To oRecordset.Fields.Count – 1
s = s & oRecordset(i).Value & vbTab
Next
s = Left(s, Len(s) – 1) & vbLf
oRecordset.MoveNext()
Loop
oRecordset.Close()
MsgBox Left(s, Len(s) – 1)
oConnection.Execute “DELETE FROM 表1 WHERE 字段1 = 33”
oRecordset.Open “SELECT * FROM 表2”, oConnection
s = “关联字段删除后” & vbLf & vbLf
For i = 0 To oRecordset.Fields.Count – 1
s = s & oRecordset(i).Name & vbTab
Next
s = Left(s, Len(s) – 1) & vbLf & vbLf
Do Until oRecordset.EOF = True
For i = 0 To oRecordset.Fields.Count – 1
s = s & oRecordset(i).Value & vbTab
Next
s = Left(s, Len(s) – 1) & vbLf
oRecordset.MoveNext()
Loop
oRecordset.Close()
MsgBox Left(s, Len(s) – 1)
oConnection.Close()
MsgBox “ok”, vbSystemModal
Sub RunAs32()
Dim oWshShell, oFSO, s, i
Set oWshShell = CreateObject(“WScript.Shell”)
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
s = oWshShell.ExpandEnvironmentStrings(“%windir%\SysWOW64\WScript.exe”)
If oFSO.FileExists(s) And LCase(WScript.FullName) <> LCase(s) Then
s = s & ” “”” & WScript.ScriptFullName & “”” “
For Each i In WScript.Arguments
If InStr(i, ” “) > 0 Then i = “””” & i & “”””
s = s & i & ” “
Next
oWshShell.Run Left(s, Len(s) – 1)
WScript.Quit()
End If
End Sub
Function JoinColumns(ByVal Columns)
Dim oColumn, s
s = “”
For Each oColumn In Columns
s = s & oColumn.Name & ” “
Next
JoinColumns = Left(s, Len(s) – 1)
End Function
Function RuleEnum(ByVal Rule)
Dim arr1, arr2, i
arr1 = Array(1, 0, 3, 2)
arr2 = Array(“adRICascade”, “adRINone”, “adRISetDefault”, “adRISetNull”)
For i = 0 To UBound(arr1)
If Rule = arr1(i) Then RuleEnum = arr2(i) : Exit Function
Next
End Function
Function KeyTypeEnum(ByVal KeyType)
Dim arr1, arr2, i
arr1 = Array(1, 2, 3)
arr2 = Array(“adKeyPrimary”, “adKeyForeign”, “adKeyUnique”)
For i = 0 To UBound(arr1)
If KeyType = arr1(i) Then KeyTypeEnum = arr2(i) : Exit Function
Next
End Function
Views: 60
