Dim cat As New ADOX.Catalog '不用cat用另外一个名字也可以
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim pstr As String '定义该变量是为了后面的书写方便
Private Sub Command1_Click()
Call CreateTable
End Sub
Private Sub CreateTable()
Dim i As Long
Dim j As String
Dim fm As String 'fm变量用来获取用户输入的文件名
CommonDialog1.Filter = "MDB文件(*.mdb)|*.mdb|AllFiles(*.*)|*.*|"
CommonDialog1.FilterIndex = 1
CommonDialog1.InitDir = App.Path
CommonDialog1.Flags = 6
CommonDialog1.Action = 2
If CommonDialog1.FileName = "" Then
MsgBox "你必须输入一个文件名,请重新保存一次!"
Exit Sub
Else
fm = CommonDialog1.FileName
End If
pstr = "Provider=Microsoft.Jet.OLEDB.4.0;" '不能把这里的4.0改为3.51
pstr = pstr & "Data Source=" & fm & ";Jet OLEDB:Database Password=" & "Secret"
cat.Create pstr '创建数据库
'分隔线----------------------------------------------------'
For i = 1 To 2
CreatTable (i)
Next i
' CreatTable (2)
End Sub
Private Sub CreatTable(i As Long)
Dim tbl As New Table
Dim j As String
Dim ii As Long
j = "数据集" & i
cat.ActiveConnection = pstr
tbl.Name = j '表的名称
tbl.Columns.Append "编号", adInteger '表的第一个字段
tbl.Columns.Append "文字1", adVarWChar, 255 '表的第二个字段
tbl.Columns.Append "文字2", adVarWChar, 255 '表的第三个字段
tbl.Columns.Append "文字3", adVarWChar, 255 '表的第四个字段
tbl.Columns.Append "文字4", adVarWChar, 255 '表的第四个字段
' tbl.Keys.Append "PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "编号", "", ""
cat.Tables.Append tbl '建立数据表
conn.Open pstr
rs.CursorLocation = adUseClient
rs.Open j, conn, adOpenKeyset, adLockPessimistic
For ii = 0 To 256
rs.AddNew '往表中添加新记录
rs.Fields(0).Value = ii
rs.Fields(1).Value = "111"
rs.Fields(2).Value = "222"
rs.Fields(3).Value = "333"
rs.Fields(4).Value = "444"
rs.Update
Next ii
conn.Close
End Sub
如上是我用的代码,使用ADOX创建数据库并创建表并填充数据,基本上是网上搜索拼凑得来的
若不设置主键一切正常,但是加了这一句
' tbl.Keys.Append "PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "编号", "", ""
之后,表的第一行数据就没了,原来是1,2,3这样填充下来的,添加了这一行之后,再用Access打开查看,1这一行就没有了,底下的“共有记录数”显示的也是少了一行
但是我在VB中,用ADO连接字符串连接数据库,再用RS把它绑定到DataGrid时,没掉的那一行竟然显示出来,而且之前填充的“111”那些数据也有,但是不能对这一行进行任何的修改删除。
要引用ADOX:
Dim cat As ADOX.Catalog
Dim idx As ADOX.Index
Dim tb As Table
Dim iKey As Key
Dim strDB As String
Dim myDefaultvalue
'创建数据库
strDB = "newDB" '指定数据库名称
'If Dir(App.Path & "\" & strDB & ".mdb") Then
Kill App.Path & "\" & strDB & ".mdb"
'End If
Set cat = New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\" & strDB & ".mdb;" & _
"Jet OLEDB:Database Password=abc" '不要密码,可以省略
'连接数据库
Set cat = New ADOX.Catalog
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\" & strDB & ".mdb;" & _
"Jet OLEDB:Database Password=abc"
'创建新数据库中的表 tb_info
Set tb = New Table
tb.Name = "tb_info"
tb.Columns.Append "id", adInteger
tb.Columns.Append "iName", adVarWChar, 50
tb.Columns.Append "iDub", adDouble
cat.Tables.Append tb
'创建字段 id 为主键
Set iKey = New Key
iKey.Name = "mTB"
iKey.Type = adKeyPrimary
iKey.RelatedTable = "tb_info"
iKey.Columns.Append "id"
cat.Tables("tb_info").Keys.Append iKey
'创建字段id的索引
Set idx = New ADOX.Index
idx.Name = "mul"
idx.Columns.Append "id"
' 将索引追加到表上
tb.Indexes.Append idx
'指定iDub的默认值
myDefaultvalue = 0
cat.Tables("tb_info").Columns("iDub").Properties("Default").Value = myDefaultvalue
Set tb = Nothing
Set cat = Nothing
MsgBox "你成功创建了数据库" & strDB
换一种方式试试,你的需求,完全可以用SQL来创建表,如:
create table 表名
(
编号 int,
文字1 text(255),
文字2 text(255),
文字3 text(255),
文字4 text(255)
)
动态用代码构造SQL语句,然后创建表。