Dim SJKnm$, Tablenm$, TabYN As Boolean '建立表用的定义
Dim db As Database ' DAO->Database object, used to do load the database.
Dim rsdata As Recordset
Private Sub Command1_Click()
'判断表是否存在(如果不存在建立,如果存在,则不做动作)
On Error Resume Next
SJKnm = App.Path & "\test data" & "\data.mdb"
Tablenm = MonthView1.Year & "年" & MonthView1.Month & "月" & MonthView1.Day & "日" & Combo2.Text
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & SJKnm
Set rs = conn.Execute(Tablenm)
TabYN = IIf(Err.Number <> 0, False, True)
If TabYN Then
'MsgBox "表已存在"
Else
Call CreateTable(SJKnm, Tablenm)
' Exit Sub
End If
End Sub'建立表的子程序
Public Sub CreateTable(MDBnm$, Tabnm$)
On Error GoTo Errhandler
'****************** 定义表与字段 ****************
Dim DefDatabase As Database
Dim DefTable As TableDef, DefField As Field
Dim a
Set DefDatabase = Workspaces(0).OpenDatabase(MDBnm, 0, False)
Set DefTable = DefDatabase.CreateTableDef(Tabnm)
'建立项目(列)Set DefField = DefTable.CreateField("序号", dbLong, 20)
DefTable.Fields.Append DefField
DefField.AllowZeroLength = True '该字段允许为空Set DefField = DefTable.CreateField("data", dbText, 18)
DefTable.Fields.Append DefField
DefField.AllowZeroLength = True '该字段允许为空Set DefField = DefTable.CreateField("测试结果", dbText, 8)
DefTable.Fields.Append DefField
DefField.AllowZeroLength = TrueSet DefField = DefTable.CreateField("测试时间", dbText, 8)
DefTable.Fields.Append DefField
DefField.AllowZeroLength = True'表追加
DefDatabase.TableDefs.Append DefTable '***********************************************
MsgBox "新表已建立完成"
Exit Sub
Errhandler:
MsgBox Err.Description
End Sub我用以上代码建立一个表,表里边有三列,
序号 data 测试结果 测试时间
我想在序号上加上索引,(有重复的索引)
Dim idx As DAO.IndexSet idx = tdf.CreateIndex("序号")
idx.Primary = FALSE
Set fld = idx.CreateField("序号", dbText)
Option ExplicitPrivate Sub cmdCheckTable_Click() 'DAO不熟悉,给你一个ADO的例子
'连接数据库
Dim cnnOpenDatabase As ADODB.Connection
Set cnnOpenDatabase = New ADODB.Connection
cnnOpenDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\a.mdb;Persist Security Info=False"
'查询表是否存在
Dim strTableName As String
strTableName = "TestTable"
Dim rsCheckTable As ADODB.Recordset
'设置错误陷阱,检查数据表是否存在,和格式是否正确
On Error GoTo 0
On Error Resume Next
Set rsCheckTable = cnnOpenDatabase.Execute("SELECT [序号], [Data], [测试结果], [测试时间] FROM [" & strTableName & "] ORDER BY [序号]")
If Err.Number = 0 Then
Call MsgBox("数据表[" & strTableName & "]已存在!", vbInformation + vbOKOnly, "提示")
On Error GoTo 0
Else
'如果数据表不存在或者格式不正确,则删除数据表,并且新建格式正确的数据表
If MsgBox("数据表不存在或格式不正确,是否建立数据表[" & strTableName & "]?", vbQuestion + vbYesNo, "提问") = vbYes Then
cnnOpenDatabase.Execute "DROP TABLE [" & strTableName & "]"
cnnOpenDatabase.Execute "CREATE TABLE [" & strTableName & "](" & _
"[序号] Long, [Data] Text NOT NULL, [测试结果] Text NOT NULL, " & _
"[测试时间] DateTime NOT NULL, CONSTRAINT PK_MT_ID PRIMARY KEY ([序号]))"
End If
On Error GoTo 0
End If
End Sub
End If
On Error GoTo 0
End If
rsCheckTable.Close
Set rsCheckTable = Nothing
Set cnnOpenDatabase = Nothing
End Sub
strSql = "ALTER TABLE strTable CONSTRAINT 序号 "
Sub CreateIndexX1()
Dim objDB As Database
Set objDB = OpenDatabase(SJKnm) 'SJKnm指向数据库位置
'在表中创建 aaa 索引。
objDB.Execute "CREATE INDEX aaa ON [" & Tablenm & "](序号)" 'Tablenm是表名,在“序号”这一列创建存在重复数据的索引。
objDB.Close
End Sub