Dim tTable As TableDef Dim fField As Field Dim dbDatabase As Database Dim sDatabaseName As String sDatabaseName = App.Path & "\my.mdb" Set dbDatabase = CreateDatabase(sDatabaseName, dbLangGeneral, dbEncrypt) Set tTable = dbDatabase.CreateTableDef("MyTable") Set fField = tTable.CreateField("Name", dbText, 20) tTable.Fields.Append fField dbDatabase.TableDefs.Append tTable
我用的是DAO,这是一个软件自动生成的代码,非常标准Sub CreateDB() Dim WS As Workspace Dim DB As Database Set WS = DBEngine.Workspaces(0) Set DB = WS.CreateDatabase(ValidateDir(App.Path) & DBName, dbLangChineseSimplified & DBPassWord, dbEncrypt) CreateTD_用户表 DB '其他表 '... '... DB.Close End SubSub CreateTD_用户表(DB As Database) Dim TD As TableDef Dim FLD As Field Dim IDX As Index Set TD = DB.CreateTableDef("用户表") TD.Attributes = 0 TD.Connect = "" TD.SourceTableName = "" TD.ValidationRule = "" TD.ValidationText = "" ' Field 用户ID Set FLD = TD.CreateField("用户ID", 4, 4) FLD.Attributes = 17 FLD.DefaultValue = "" FLD.OrdinalPosition = 1 FLD.Required = False FLD.ValidationRule = "" FLD.ValidationText = "" TD.Fields.Append FLD ' Field 用户名 Set FLD = TD.CreateField("用户名", 10, 50) FLD.AllowZeroLength = False FLD.Attributes = 2 FLD.DefaultValue = "" FLD.OrdinalPosition = 2 FLD.Required = False FLD.ValidationRule = "" FLD.ValidationText = "" TD.Fields.Append FLD ' Field 密码 Set FLD = TD.CreateField("密码", 10, 50) FLD.AllowZeroLength = False FLD.Attributes = 2 FLD.DefaultValue = "" FLD.OrdinalPosition = 3 FLD.Required = False FLD.ValidationRule = "" FLD.ValidationText = "" TD.Fields.Append FLD ' Field 超级用户 Set FLD = TD.CreateField("超级用户", 1, 1) FLD.Attributes = 1 FLD.DefaultValue = "" FLD.OrdinalPosition = 4 FLD.Required = False FLD.ValidationRule = "" FLD.ValidationText = "" TD.Fields.Append FLD ' Index PrimaryKey Set IDX = TD.CreateIndex("PrimaryKey") IDX.Clustered = False IDX.Primary = True IDX.Unique = True IDX.Required = True IDX.IgnoreNulls = False ' Field 用户ID Set FLD = IDX.CreateField("用户ID") FLD.Attributes = 0 IDX.Fields.Append FLD TD.Indexes.Append IDX ' Index 用户ID Set IDX = TD.CreateIndex("用户ID") IDX.Clustered = False IDX.Primary = False IDX.Unique = True IDX.Required = False IDX.IgnoreNulls = False ' Field 用户ID Set FLD = IDX.CreateField("用户ID") FLD.Attributes = 0 IDX.Fields.Append FLD TD.Indexes.Append IDX ' Index 用户名 Set IDX = TD.CreateIndex("用户名") IDX.Clustered = False IDX.Primary = False IDX.Unique = True IDX.Required = False IDX.IgnoreNulls = False ' Field 用户名 Set FLD = IDX.CreateField("用户名") FLD.Attributes = 0 IDX.Fields.Append FLD TD.Indexes.Append IDX DB.TableDefs.Append TD End Sub
要建立数据库,首先要provider的支持,例如sql2000中你要有足够的权限(Access中一般都行)。然后使用ADOX来建立数据库。ADOX即ADO的扩展库,支持数据库定义和数据库安全管理的,用他才能建立数据库(直接用ado不能建库,顶多建表)。要在引用对话框中引用进来。详细的使用请参考MDAC SDK,在微软MSDN可以下到,英文全称:Microsoft® Data Access Components (MDAC) SDK ,adox全称:ADO Extensions for Data Definition Language and Security (ADOX)
'如果你有脚本文件,则可以按如下过程建立Private Sub CreateDataBase(cnDataBase as connect,sqlFile As String) Dim strSql As String, strTmp As String Open sqlFile For Input As #1 strSql = "" Do While Not EOF(1) Line Input #1, strTmp If UCase(strTmp) = "GO" Then cnDataBase.Execute strSql strSql = "" Else strSql = strSql & strTmp & vbCrLf End If Loop If strSql <> "" Then cnDataBase.Execute strSql Close #1 End Sub
Dim str As String
str = "CREATE DATABASE Sales " & _
"ON (NAME = Sales_dat," & _
"FILENAME = 'D:\saledat.mdf'," & _
"SIZE = 1MB,MAXSIZE = 5MB,FILEGROWTH = 10% )" & _
"LOG ON ( NAME = 'Sales_log'," & _
"FILENAME = 'D:\salelog.ldf'," & _
"SIZE = 1MB,MAXSIZE = 5MB,FILEGROWTH = 5MB )"
Set cn = New Connection
With cn
.ConnectionString = "Provider=SQLOLEDB.1;Data Source=pmserver;User ID=sa;Password=sa"
.Open
.Execute str
End With
Set cn = Nothing
Dim fField As Field
Dim dbDatabase As Database
Dim sDatabaseName As String sDatabaseName = App.Path & "\my.mdb"
Set dbDatabase = CreateDatabase(sDatabaseName, dbLangGeneral, dbEncrypt)
Set tTable = dbDatabase.CreateTableDef("MyTable")
Set fField = tTable.CreateField("Name", dbText, 20) tTable.Fields.Append fField
dbDatabase.TableDefs.Append tTable
Dim WS As Workspace
Dim DB As Database Set WS = DBEngine.Workspaces(0)
Set DB = WS.CreateDatabase(ValidateDir(App.Path) & DBName, dbLangChineseSimplified & DBPassWord, dbEncrypt) CreateTD_用户表 DB
'其他表
'...
'...
DB.Close
End SubSub CreateTD_用户表(DB As Database)
Dim TD As TableDef
Dim FLD As Field
Dim IDX As Index Set TD = DB.CreateTableDef("用户表")
TD.Attributes = 0
TD.Connect = ""
TD.SourceTableName = ""
TD.ValidationRule = ""
TD.ValidationText = ""
' Field 用户ID
Set FLD = TD.CreateField("用户ID", 4, 4)
FLD.Attributes = 17
FLD.DefaultValue = ""
FLD.OrdinalPosition = 1
FLD.Required = False
FLD.ValidationRule = ""
FLD.ValidationText = ""
TD.Fields.Append FLD
' Field 用户名
Set FLD = TD.CreateField("用户名", 10, 50)
FLD.AllowZeroLength = False
FLD.Attributes = 2
FLD.DefaultValue = ""
FLD.OrdinalPosition = 2
FLD.Required = False
FLD.ValidationRule = ""
FLD.ValidationText = ""
TD.Fields.Append FLD
' Field 密码
Set FLD = TD.CreateField("密码", 10, 50)
FLD.AllowZeroLength = False
FLD.Attributes = 2
FLD.DefaultValue = ""
FLD.OrdinalPosition = 3
FLD.Required = False
FLD.ValidationRule = ""
FLD.ValidationText = ""
TD.Fields.Append FLD
' Field 超级用户
Set FLD = TD.CreateField("超级用户", 1, 1)
FLD.Attributes = 1
FLD.DefaultValue = ""
FLD.OrdinalPosition = 4
FLD.Required = False
FLD.ValidationRule = ""
FLD.ValidationText = ""
TD.Fields.Append FLD
' Index PrimaryKey
Set IDX = TD.CreateIndex("PrimaryKey")
IDX.Clustered = False
IDX.Primary = True
IDX.Unique = True
IDX.Required = True
IDX.IgnoreNulls = False
' Field 用户ID
Set FLD = IDX.CreateField("用户ID")
FLD.Attributes = 0
IDX.Fields.Append FLD
TD.Indexes.Append IDX
' Index 用户ID
Set IDX = TD.CreateIndex("用户ID")
IDX.Clustered = False
IDX.Primary = False
IDX.Unique = True
IDX.Required = False
IDX.IgnoreNulls = False
' Field 用户ID
Set FLD = IDX.CreateField("用户ID")
FLD.Attributes = 0
IDX.Fields.Append FLD
TD.Indexes.Append IDX
' Index 用户名
Set IDX = TD.CreateIndex("用户名")
IDX.Clustered = False
IDX.Primary = False
IDX.Unique = True
IDX.Required = False
IDX.IgnoreNulls = False
' Field 用户名
Set FLD = IDX.CreateField("用户名")
FLD.Attributes = 0
IDX.Fields.Append FLD
TD.Indexes.Append IDX
DB.TableDefs.Append TD
End Sub
Dim strSql As String, strTmp As String
Open sqlFile For Input As #1
strSql = ""
Do While Not EOF(1)
Line Input #1, strTmp
If UCase(strTmp) = "GO" Then
cnDataBase.Execute strSql
strSql = ""
Else
strSql = strSql & strTmp & vbCrLf
End If
Loop
If strSql <> "" Then cnDataBase.Execute strSql
Close #1
End Sub