Public Function CreateMDB(ByVal FileName As String) Dim cat As New ADOX.Catalog '不用cat用另外一个名字也可以 Dim conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim pstr As String '定义该变量是为了后面的书写方便 Dim tbl As New Table pstr = "Provider=Microsoft.Jet.OLEDB.4.0;" '不能把这里的4.0改为3.51 pstr = pstr & "Data Source=" & FileName cat.Create pstr '创建数据库 cat.ActiveConnection = pstr tbl.Name = "下载记录" '表的名称 tbl.Columns.Append "编号", adInteger '表的第一个字段 tbl.Columns.Append "姓名", adVarWChar, 8 '表的第二个字段 tbl.Columns.Append "住址", adVarWChar, 50 '表的第三个字段 cat.Tables.Append tbl '建立数据表
Public g_oCnn As Connection Public g_oCat As Catalog Public g_oRst As Recordset Public Const DBNAME_PLACE_HODLER As String = "<DBPATH>" Public Const DATABASE_CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBNAME_PLACE_HODLER
Public Function GetConnStr(ByVal sDBName As String) As String GetConnStr = DATABASE_CONNECTION_STRING GetConnStr = Replace(GetConnStr, DBNAME_PLACE_HODLER, sDBName) End Function
Public Function CreateDB(ByVal sDBName As String) As Boolean On Error GoTo Error_Handler Dim sCnnStr As String Set g_oCat = New ADOX.Catalog
sCnnStr = GetConnStr(sDBName)
g_oCat.Create sCnnStr
CreateDB = True Exit Function Error_Handler: End Function
Public Function CreateTable(ByVal sTableName As String) As Recordset On Error GoTo Error_Handler If g_oCat Is Nothing Then Exit Function Dim oTable As Table Set oTable = New Table
With oTable .Name = sTableName Set .ParentCatalog = g_oCat .Columns.Append "id", adInteger .Columns("id").Properties("AutoIncrement") = True .Keys.Append "colid", adKeyPrimary, "id" .Columns.Append "name", adVarWChar, 50 .Columns.Append "value", adVarWChar, 50 .Columns("value").Attributes = adColNullable End With
g_oCat.Tables.Append oTable
Set CreateTable = New ADODB.Recordset With CreateTable .Open sTableName, g_oCat.ActiveConnection, adOpenDynamic, adLockOptimistic End With
Set oTable = Nothing Exit Function Error_Handler: Set oTable = Nothing Set CreateTable = Nothing End Function -------------------------------------------------------------------------------Private Sub Command1_Click() Dim tdExample As TableDef Dim fldForeName As Field Dim fldSurname As Field Dim fldDOB As Field Dim fldFurtherDetails As Field Dim dbDatabase As Database Dim sNewDBPathAndName As String
sNewDBPathAndName = "d:\NewDB" & Right$(Time, 2) & ".mdb" Set dbDatabase = CreateDatabase(sNewDBPathAndName, dbLangGeneral, dbEncrypt) Set tdExample = dbDatabase.CreateTableDef("DongGe") 'Example")
Set fldForeName = tdExample.CreateField("Fore_Name", dbText, 20) Set fldSurname = tdExample.CreateField("Surname", dbDouble, 20) Set fldDOB = tdExample.CreateField("DOB", dbDate) Set fldFurtherDetails = tdExample.CreateField("Further_Details", dbMemo)
我试了一下: 建一个空数据库test7.mdb,运行 Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim col As New ADOX.Column ' 打开目录 cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=D:\test7.mdb;" With tbl .Name = "testTbl" Set .ParentCatalog = cat ' 创建字段并将它们追加到新的 Table 对象中。 .Columns.Append "ID", adInteger ' 产生 ContactId 列和自动递加列 .Columns("ID").Properties("AutoIncrement") = True .Columns.Append "Name", adVarWChar End With
cat.Tables.Append tbl Set cat = Nothing没有问题啊,建表成功,ID是自动编号的
Dim cat As New ADOX.Catalog '不用cat用另外一个名字也可以
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim pstr As String '定义该变量是为了后面的书写方便
Dim tbl As New Table pstr = "Provider=Microsoft.Jet.OLEDB.4.0;" '不能把这里的4.0改为3.51
pstr = pstr & "Data Source=" & FileName
cat.Create pstr '创建数据库
cat.ActiveConnection = pstr
tbl.Name = "下载记录" '表的名称
tbl.Columns.Append "编号", adInteger '表的第一个字段
tbl.Columns.Append "姓名", adVarWChar, 8 '表的第二个字段
tbl.Columns.Append "住址", adVarWChar, 50 '表的第三个字段
cat.Tables.Append tbl '建立数据表
' conn.Open pstr
' rs.CursorLocation = adUseClient
' rs.Open "MyTable", conn, adOpenKeyset, adLockPessimistic
' rs.AddNew '往表中添加新记录
' rs.Fields(0).Value = 9801
' rs.Fields(1).Value = "孙悟空"
' rs.Fields(2).Value = "广州市花果山"
' rs.UpdateEnd Function呵呵!!解决了上面这段代码就是了,但我想知道,如我要建立"自动编号"ID,该用什么常数呀
类似这样:
Option Explicit
Public g_oCnn As Connection
Public g_oCat As Catalog
Public g_oRst As Recordset
Public Const DBNAME_PLACE_HODLER As String = "<DBPATH>"
Public Const DATABASE_CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBNAME_PLACE_HODLER
Public Function GetConnStr(ByVal sDBName As String) As String
GetConnStr = DATABASE_CONNECTION_STRING
GetConnStr = Replace(GetConnStr, DBNAME_PLACE_HODLER, sDBName)
End Function
Public Function CreateDB(ByVal sDBName As String) As Boolean
On Error GoTo Error_Handler
Dim sCnnStr As String
Set g_oCat = New ADOX.Catalog
sCnnStr = GetConnStr(sDBName)
g_oCat.Create sCnnStr
CreateDB = True
Exit Function
Error_Handler:
End Function
Public Function CreateTable(ByVal sTableName As String) As Recordset
On Error GoTo Error_Handler
If g_oCat Is Nothing Then Exit Function
Dim oTable As Table
Set oTable = New Table
With oTable
.Name = sTableName
Set .ParentCatalog = g_oCat
.Columns.Append "id", adInteger
.Columns("id").Properties("AutoIncrement") = True
.Keys.Append "colid", adKeyPrimary, "id"
.Columns.Append "name", adVarWChar, 50
.Columns.Append "value", adVarWChar, 50
.Columns("value").Attributes = adColNullable
End With
g_oCat.Tables.Append oTable
Set CreateTable = New ADODB.Recordset
With CreateTable
.Open sTableName, g_oCat.ActiveConnection, adOpenDynamic, adLockOptimistic
End With
Set oTable = Nothing
Exit Function
Error_Handler:
Set oTable = Nothing
Set CreateTable = Nothing
End Function
-------------------------------------------------------------------------------Private Sub Command1_Click()
Dim tdExample As TableDef
Dim fldForeName As Field
Dim fldSurname As Field
Dim fldDOB As Field
Dim fldFurtherDetails As Field
Dim dbDatabase As Database
Dim sNewDBPathAndName As String
sNewDBPathAndName = "d:\NewDB" & Right$(Time, 2) & ".mdb"
Set dbDatabase = CreateDatabase(sNewDBPathAndName, dbLangGeneral, dbEncrypt)
Set tdExample = dbDatabase.CreateTableDef("DongGe") 'Example")
Set fldForeName = tdExample.CreateField("Fore_Name", dbText, 20)
Set fldSurname = tdExample.CreateField("Surname", dbDouble, 20)
Set fldDOB = tdExample.CreateField("DOB", dbDate)
Set fldFurtherDetails = tdExample.CreateField("Further_Details", dbMemo)
tdExample.Fields.Append fldForeName
tdExample.Fields.Append fldSurname
tdExample.Fields.Append fldDOB
tdExample.Fields.Append fldFurtherDetails
dbDatabase.TableDefs.Append tdExample
MsgBox "New .MDB Created - '" & sNewDBPathAndName & "'", vbInformation
End Sub
tbl.Columns("ID").Properties("AutoIncrement") = True//建立一个加了密码的MDB
没办法吧
意思就是不能在建立的时候加入密码?
建一个空数据库test7.mdb,运行
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim col As New ADOX.Column ' 打开目录
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\test7.mdb;" With tbl
.Name = "testTbl"
Set .ParentCatalog = cat
' 创建字段并将它们追加到新的 Table 对象中。
.Columns.Append "ID", adInteger
' 产生 ContactId 列和自动递加列
.Columns("ID").Properties("AutoIncrement") = True
.Columns.Append "Name", adVarWChar
End With
cat.Tables.Append tbl
Set cat = Nothing没有问题啊,建表成功,ID是自动编号的