引用 Microsoft ADO Ext. 2.X for DDL and Security如下代码显示如何通过 Create 方法创建新的 Jet 数据库。Sub CreateDatabase() Dim cat As New ADOX.Catalog cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"End Sub
Dim oCat As ADOX.Catalog oCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\new.mdb;" & _ "Jet OLEDB:Engine Type=5;" Engine Type = 4 创建 Access 97 database in 3.5 format(指的Jet 3.5) Engine Type = 5 创建 Access 2000 database in 4.0 format (default)Note: Access 97 (3.5 format) will not be able to open up an Access 2000 (4.0 format) database. However, Access 2000 will be able to open up an Access 97 or 2000 database.
在创建数据库时加上一句 Engine Type = 5 就可以了~
附加菜Option Explicit Private tbl As ADOX.Table Private cat As ADOX.Catalog 'the actual database Private idx As ADOX.Index Private Pkey As ADOX.Key Public Sub CreateAdox(strCatalogName As String, _ strTableNameOne As String, _ strTableNameTwo As String) Set cat = New ADOX.Catalog
On Error Goto MyError
'* This creates the actual database. cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ App.Path & "\" & strCatalogName & ".mdb" '默认Engine Type = 5 所以没写 Set tbl = New ADOX.Table
With tbl .Name = strTableNameOne Set .ParentCatalog = cat .Columns.Append "MyPrimaryKey", adInteger 'long data Type .Columns("MyPrimaryKey").Properties("AutoIncrement") = True 'auto number .Columns.Append "MyIntegerData", adSmallInt 'Integer data Type .Columns.Append "MyStringData", adVarWChar, 25 'string size of 25 End With cat.Tables.Append tbl 'add the table To the database
Set Pkey = New ADOX.Key 'create new key object With Pkey .Name = "MyPrimaryKey" .Type = adKeyPrimary .Columns.Append "MyPrimaryKey" End With tbl.Keys.Append Pkey Set Pkey = Nothing Set idx = New ADOX.Index With idx .Unique = False 'duplicates allowed .Name = "MyIntegerData" .Columns.Append "MyIntegerData" End With tbl.Indexes.Append idx Set idx = Nothing
Set idx = New ADOX.Index With idx .Unique = True 'NO duplicates allowed .Name = "MyStringData" .Columns.Append "MyStringData" End With tbl.Indexes.Append idx Set idx = Nothing Set tbl = Nothing
'* Create a detail Table with a memo Fie ' ld, and foreign key Set tbl = New ADOX.Table With tbl .Name = strTableNameTwo Set .ParentCatalog = cat .Columns.Append "MyPrimaryKey", adInteger 'Long data Type .Columns.Append "MyMemoData", adLongVarWChar 'Memo data Type End With cat.Tables.Append tbl
Set Pkey = New ADOX.Key With Pkey 'set relationship .Name = "MyPrimaryKey" .Type = adKeyForeign .RelatedTable = strTableNameOne .Columns.Append "MyPrimaryKey" .Columns("MyPrimaryKey").RelatedColumn = "MyPrimaryKey" .UpdateRule = adRICascade 'Enforce Referential Integrity End With tbl.Keys.Append Pkey
Set tbl = Nothing Set Pkey = Nothing Set cat = Nothing
Exit Sub
MyError: Debug.Print Err.Number & Space$(1) & Err.Description End Sub
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"End Sub
oCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\new.mdb;" & _
"Jet OLEDB:Engine Type=5;" Engine Type = 4 创建 Access 97 database in 3.5 format(指的Jet 3.5)
Engine Type = 5 创建 Access 2000 database in 4.0 format (default)Note: Access 97 (3.5 format) will not be able to open up an Access 2000 (4.0 format) database. However, Access 2000 will be able to open up an Access 97 or 2000 database.
Private tbl As ADOX.Table
Private cat As ADOX.Catalog 'the actual database
Private idx As ADOX.Index
Private Pkey As ADOX.Key
Public Sub CreateAdox(strCatalogName As String, _
strTableNameOne As String, _
strTableNameTwo As String)
Set cat = New ADOX.Catalog
On Error Goto MyError
'* This creates the actual database.
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\" & strCatalogName & ".mdb"
'默认Engine Type = 5 所以没写
Set tbl = New ADOX.Table
With tbl
.Name = strTableNameOne
Set .ParentCatalog = cat
.Columns.Append "MyPrimaryKey", adInteger 'long data Type
.Columns("MyPrimaryKey").Properties("AutoIncrement") = True 'auto number
.Columns.Append "MyIntegerData", adSmallInt 'Integer data Type
.Columns.Append "MyStringData", adVarWChar, 25 'string size of 25
End With
cat.Tables.Append tbl 'add the table To the database
Set Pkey = New ADOX.Key 'create new key object
With Pkey
.Name = "MyPrimaryKey"
.Type = adKeyPrimary
.Columns.Append "MyPrimaryKey"
End With
tbl.Keys.Append Pkey
Set Pkey = Nothing
Set idx = New ADOX.Index
With idx
.Unique = False 'duplicates allowed
.Name = "MyIntegerData"
.Columns.Append "MyIntegerData"
End With
tbl.Indexes.Append idx
Set idx = Nothing
Set idx = New ADOX.Index
With idx
.Unique = True 'NO duplicates allowed
.Name = "MyStringData"
.Columns.Append "MyStringData"
End With
tbl.Indexes.Append idx
Set idx = Nothing
Set tbl = Nothing
'* Create a detail Table with a memo Fie
' ld, and foreign key
Set tbl = New ADOX.Table
With tbl
.Name = strTableNameTwo
Set .ParentCatalog = cat
.Columns.Append "MyPrimaryKey", adInteger 'Long data Type
.Columns.Append "MyMemoData", adLongVarWChar 'Memo data Type
End With
cat.Tables.Append tbl
Set Pkey = New ADOX.Key
With Pkey 'set relationship
.Name = "MyPrimaryKey"
.Type = adKeyForeign
.RelatedTable = strTableNameOne
.Columns.Append "MyPrimaryKey"
.Columns("MyPrimaryKey").RelatedColumn = "MyPrimaryKey"
.UpdateRule = adRICascade 'Enforce Referential Integrity
End With
tbl.Keys.Append Pkey
Set tbl = Nothing
Set Pkey = Nothing
Set cat = Nothing
Exit Sub
MyError:
Debug.Print Err.Number & Space$(1) & Err.Description
End Sub