查看ADOX内容下面一些代码起抛砖引玉作用(使用ADOX创建各种对象): Option ExplicitDim catWorkshop As ADOX.Catalog Dim tblCompanyInfo As ADOX.Table Dim tblStock As ADOX.Table Dim tblProdGroups As ADOX.Table Dim tblTaxCodes As ADOX.Table Dim idxIndex As ADOX.Index Dim kyForeign As ADOX.KeyDim strConString As StringPublic Function CreateDatabase(DBPath As String) As BooleanCreateDatabase = TruestrConString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _ & DBPath & "\Workshop.mdb"On Error GoTo ErrHandler' Create the database.Set catWorkshop = New ADOX.Catalog catWorkshop.Create strConString catWorkshop.ActiveConnection = strConString' Create the Company Info table.Set tblCompanyInfo = New ADOX.Table tblCompanyInfo.Name = "Company Info" tblCompanyInfo.Columns.Append "TradingName", adVarWChar, 50 tblCompanyInfo.Columns.Append "CompanyName", adVarWChar, 50 tblCompanyInfo.Columns.Append "Address1", adVarWChar, 40 tblCompanyInfo.Columns.Append "Address2", adVarWChar, 40 tblCompanyInfo.Columns.Append "Suburb", adVarWChar, 40 tblCompanyInfo.Columns.Append "State", adVarWChar, 3 tblCompanyInfo.Columns.Append "Postcode", adVarWChar, 4 tblCompanyInfo.Columns.Append "Phone1", adVarWChar, 20 tblCompanyInfo.Columns.Append "Fax", adVarWChar, 20 tblCompanyInfo.Columns.Append "Mobile", adVarWChar, 20 tblCompanyInfo.Columns.Append "ACN", adVarWChar, 20 tblCompanyInfo.Columns.Append "ABN", adVarWChar, 20 catWorkshop.Tables.Append tblCompanyInfo' Create indexes.Set idxIndex = New ADOX.Index With idxIndex .Name = "TradingName" .Columns.Append "TradingName" .IndexNulls = adIndexNullsIgnore .PrimaryKey = True .Unique = True End With tblCompanyInfo.Indexes.Append idxIndexSet idxIndex = Nothing Set tblCompanyInfo = Nothing' Create the Stock table.Set tblStock = New ADOX.Table tblStock.Name = "Stock" tblStock.Columns.Append "StockID", adDouble tblStock.Columns.Append "PartNo", adVarWChar, 20 tblStock.Columns.Append "Description", adVarWChar, 30 tblStock.Columns.Append "ProductGroup", adVarWChar, 10 tblStock.Columns.Append "UnitOfSale", adVarWChar, 6 tblStock.Columns.Append "TaxCode", adVarWChar, 3 tblStock.Columns.Append "LastCost", adCurrency tblStock.Columns.Append "SellPrice", adCurrency tblStock.Columns.Append "Quantity", adDouble tblStock.Columns.Append "ChangeDesc", adBinary catWorkshop.Tables.Append tblStock' Create indexes.Set idxIndex = New ADOX.Index With idxIndex .Name = "StockID" .Columns.Append "StockID" .IndexNulls = adIndexNullsIgnore .PrimaryKey = True .Unique = True End With tblStock.Indexes.Append idxIndexSet idxIndex = New ADOX.Index With idxIndex .Name = "PartNo" .Columns.Append "PartNo" .IndexNulls = adIndexNullsIgnore .PrimaryKey = False .Unique = False End With tblStock.Indexes.Append idxIndexSet idxIndex = New ADOX.Index With idxIndex .Name = "Description" .Columns.Append "Description" .IndexNulls = adIndexNullsIgnore .PrimaryKey = False .Unique = False End With tblStock.Indexes.Append idxIndexSet idxIndex = Nothing Set tblStock = Nothing' Create the Product Groups table.Set tblProdGroups = New ADOX.Table tblProdGroups.Name = "Product Groups" tblProdGroups.Columns.Append "Code", adVarWChar, 10 tblProdGroups.Columns.Append "Description", adVarWChar, 20 catWorkshop.Tables.Append tblProdGroups' Create indexes.Set idxIndex = New ADOX.Index With idxIndex .Name = "ProductGroup" .Columns.Append "Code" .IndexNulls = adIndexNullsIgnore .PrimaryKey = True .Unique = True End With tblProdGroups.Indexes.Append idxIndexSet idxIndex = New ADOX.Index With idxIndex .Name = "Description" .Columns.Append "Description" .IndexNulls = adIndexNullsIgnore .PrimaryKey = False .Unique = False End With tblProdGroups.Indexes.Append idxIndexSet idxIndex = Nothing Set tblProdGroups = Nothing' Create the Tax Codes table.Set tblTaxCodes = New ADOX.Table tblTaxCodes.Name = "Tax Codes" tblTaxCodes.Columns.Append "Code", adVarWChar, 3 tblTaxCodes.Columns.Append "Description", adVarWChar, 20 tblTaxCodes.Columns.Append "TaxRate", adSingle catWorkshop.Tables.Append tblTaxCodes' Create indexes.Set idxIndex = New ADOX.Index With idxIndex .Name = "TaxCode" .Columns.Append "Code" .IndexNulls = adIndexNullsIgnore .PrimaryKey = True .Unique = True End With tblTaxCodes.Indexes.Append idxIndexSet idxIndex = Nothing Set tblTaxCodes = Nothing' Create RelationshipsSet kyForeign = New ADOX.Key kyForeign.Name = "Product GroupsStock" kyForeign.Type = adKeyForeign kyForeign.RelatedTable = "Product Groups" kyForeign.Columns.Append "ProductGroup" kyForeign.Columns("ProductGroup").RelatedColumn = "Code" kyForeign.UpdateRule = adRICascade catWorkshop.Tables("Stock").Keys.Append kyForeignSet catWorkshop = Nothing Exit FunctionErrHandler: MsgBox "Error: " & Err.Number & vbCr & Err.DescriptionCreateDatabase = FalseEnd Function
这是创建关系的函数 Public Function CreateRelationship() As Boolean ' Create a relationship between tblCustomers ' and tblCustomerItems. ' The relation will have cascading deletes ' enabled.
Dim cat As ADOX.Catalog Dim fk As ADOX.key Dim tbl As ADOX.Table Dim keys As ADOX.keys
On Error GoTo HandleErrors
' Get the catalog for the current database Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection
' Get the keys collection for the many-side table Set tbl = cat.Tables("tblCustomerItems") Set keys = tbl.keys
' Create the foreign key Set fk = New ADOX.key fk.Name = "CustomerItemsCustomers" fk.Type = adKeyForeign fk.RelatedTable = "tblCustomers"
' Specify cascading deletes fk.DeleteRule = adRICascade ' Append a column to the key fk.Columns.Append "CustomerID" ' Set the related column name fk.Columns("CustomerID").RelatedColumn = _ "CustomerID" ' Append the key object to save it tbl.keys.Append fk CreateRelationship = True
ExitHere: Set fk = Nothing Set keys = Nothing Set tbl = Nothing Set cat = Nothing Exit Function
HandleErrors: Select Case Err.Number Case adhcErrObjectExists ' If the relationship exists, ' just delete it and try again tbl.keys.Delete fk.Name Resume Case Else MsgBox "Error: " & Err.Description & _ " (" & Err.Number & ")" CreateRelationship = False Resume ExitHere End Select End Function
Option ExplicitDim catWorkshop As ADOX.Catalog
Dim tblCompanyInfo As ADOX.Table
Dim tblStock As ADOX.Table
Dim tblProdGroups As ADOX.Table
Dim tblTaxCodes As ADOX.Table
Dim idxIndex As ADOX.Index
Dim kyForeign As ADOX.KeyDim strConString As StringPublic Function CreateDatabase(DBPath As String) As BooleanCreateDatabase = TruestrConString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& DBPath & "\Workshop.mdb"On Error GoTo ErrHandler' Create the database.Set catWorkshop = New ADOX.Catalog
catWorkshop.Create strConString
catWorkshop.ActiveConnection = strConString' Create the Company Info table.Set tblCompanyInfo = New ADOX.Table
tblCompanyInfo.Name = "Company Info"
tblCompanyInfo.Columns.Append "TradingName", adVarWChar, 50
tblCompanyInfo.Columns.Append "CompanyName", adVarWChar, 50
tblCompanyInfo.Columns.Append "Address1", adVarWChar, 40
tblCompanyInfo.Columns.Append "Address2", adVarWChar, 40
tblCompanyInfo.Columns.Append "Suburb", adVarWChar, 40
tblCompanyInfo.Columns.Append "State", adVarWChar, 3
tblCompanyInfo.Columns.Append "Postcode", adVarWChar, 4
tblCompanyInfo.Columns.Append "Phone1", adVarWChar, 20
tblCompanyInfo.Columns.Append "Fax", adVarWChar, 20
tblCompanyInfo.Columns.Append "Mobile", adVarWChar, 20
tblCompanyInfo.Columns.Append "ACN", adVarWChar, 20
tblCompanyInfo.Columns.Append "ABN", adVarWChar, 20
catWorkshop.Tables.Append tblCompanyInfo' Create indexes.Set idxIndex = New ADOX.Index
With idxIndex
.Name = "TradingName"
.Columns.Append "TradingName"
.IndexNulls = adIndexNullsIgnore
.PrimaryKey = True
.Unique = True
End With
tblCompanyInfo.Indexes.Append idxIndexSet idxIndex = Nothing
Set tblCompanyInfo = Nothing' Create the Stock table.Set tblStock = New ADOX.Table
tblStock.Name = "Stock"
tblStock.Columns.Append "StockID", adDouble
tblStock.Columns.Append "PartNo", adVarWChar, 20
tblStock.Columns.Append "Description", adVarWChar, 30
tblStock.Columns.Append "ProductGroup", adVarWChar, 10
tblStock.Columns.Append "UnitOfSale", adVarWChar, 6
tblStock.Columns.Append "TaxCode", adVarWChar, 3
tblStock.Columns.Append "LastCost", adCurrency
tblStock.Columns.Append "SellPrice", adCurrency
tblStock.Columns.Append "Quantity", adDouble
tblStock.Columns.Append "ChangeDesc", adBinary
catWorkshop.Tables.Append tblStock' Create indexes.Set idxIndex = New ADOX.Index
With idxIndex
.Name = "StockID"
.Columns.Append "StockID"
.IndexNulls = adIndexNullsIgnore
.PrimaryKey = True
.Unique = True
End With
tblStock.Indexes.Append idxIndexSet idxIndex = New ADOX.Index
With idxIndex
.Name = "PartNo"
.Columns.Append "PartNo"
.IndexNulls = adIndexNullsIgnore
.PrimaryKey = False
.Unique = False
End With
tblStock.Indexes.Append idxIndexSet idxIndex = New ADOX.Index
With idxIndex
.Name = "Description"
.Columns.Append "Description"
.IndexNulls = adIndexNullsIgnore
.PrimaryKey = False
.Unique = False
End With
tblStock.Indexes.Append idxIndexSet idxIndex = Nothing
Set tblStock = Nothing' Create the Product Groups table.Set tblProdGroups = New ADOX.Table
tblProdGroups.Name = "Product Groups"
tblProdGroups.Columns.Append "Code", adVarWChar, 10
tblProdGroups.Columns.Append "Description", adVarWChar, 20
catWorkshop.Tables.Append tblProdGroups' Create indexes.Set idxIndex = New ADOX.Index
With idxIndex
.Name = "ProductGroup"
.Columns.Append "Code"
.IndexNulls = adIndexNullsIgnore
.PrimaryKey = True
.Unique = True
End With
tblProdGroups.Indexes.Append idxIndexSet idxIndex = New ADOX.Index
With idxIndex
.Name = "Description"
.Columns.Append "Description"
.IndexNulls = adIndexNullsIgnore
.PrimaryKey = False
.Unique = False
End With
tblProdGroups.Indexes.Append idxIndexSet idxIndex = Nothing
Set tblProdGroups = Nothing' Create the Tax Codes table.Set tblTaxCodes = New ADOX.Table
tblTaxCodes.Name = "Tax Codes"
tblTaxCodes.Columns.Append "Code", adVarWChar, 3
tblTaxCodes.Columns.Append "Description", adVarWChar, 20
tblTaxCodes.Columns.Append "TaxRate", adSingle
catWorkshop.Tables.Append tblTaxCodes' Create indexes.Set idxIndex = New ADOX.Index
With idxIndex
.Name = "TaxCode"
.Columns.Append "Code"
.IndexNulls = adIndexNullsIgnore
.PrimaryKey = True
.Unique = True
End With
tblTaxCodes.Indexes.Append idxIndexSet idxIndex = Nothing
Set tblTaxCodes = Nothing' Create RelationshipsSet kyForeign = New ADOX.Key
kyForeign.Name = "Product GroupsStock"
kyForeign.Type = adKeyForeign
kyForeign.RelatedTable = "Product Groups"
kyForeign.Columns.Append "ProductGroup"
kyForeign.Columns("ProductGroup").RelatedColumn = "Code"
kyForeign.UpdateRule = adRICascade
catWorkshop.Tables("Stock").Keys.Append kyForeignSet catWorkshop = Nothing
Exit FunctionErrHandler:
MsgBox "Error: " & Err.Number & vbCr & Err.DescriptionCreateDatabase = FalseEnd Function
Public Function CreateRelationship() As Boolean
' Create a relationship between tblCustomers
' and tblCustomerItems.
' The relation will have cascading deletes
' enabled.
Dim cat As ADOX.Catalog
Dim fk As ADOX.key
Dim tbl As ADOX.Table
Dim keys As ADOX.keys
On Error GoTo HandleErrors
' Get the catalog for the current database
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
' Get the keys collection for the many-side table
Set tbl = cat.Tables("tblCustomerItems")
Set keys = tbl.keys
' Create the foreign key
Set fk = New ADOX.key
fk.Name = "CustomerItemsCustomers"
fk.Type = adKeyForeign
fk.RelatedTable = "tblCustomers"
' Specify cascading deletes
fk.DeleteRule = adRICascade
' Append a column to the key
fk.Columns.Append "CustomerID"
' Set the related column name
fk.Columns("CustomerID").RelatedColumn = _
"CustomerID"
' Append the key object to save it
tbl.keys.Append fk
CreateRelationship = True
ExitHere:
Set fk = Nothing
Set keys = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
HandleErrors:
Select Case Err.Number
Case adhcErrObjectExists
' If the relationship exists,
' just delete it and try again
tbl.keys.Delete fk.Name
Resume
Case Else
MsgBox "Error: " & Err.Description & _
" (" & Err.Number & ")"
CreateRelationship = False
Resume ExitHere
End Select
End Function