1.首先我们可以学习一下错误捕捉,当数据表不存在的时候,删除数据表就会出现错误,此时就会有错误记录,我们就可以利用本地窗口看到错误的信息
Sub 错误捕捉()
Dim mydata As String
Dim mytable As String
Dim con As New ADODB.Connection
mydata = ThisWorkbook.Path & "\成绩管理.accdb"
mytable = "期末成绩"
With con
.Provider = "microsoft.ace.oledb.12.0"
.Open mydata
End With
On Error Resume Next '遇到错误,继续往下执行
'删除数据表
con.Execute "drop table " & mytable
If Err.Number <> 0 Then
MsgBox Err.Description
Else
MsgBox "该表存在"
End If
con.Close
Set con = Nothing
End Sub
2.判断数据表是否存在
Sub 检查数据表是否存在()
Dim mydata As String
Dim mytable As String
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
mydata = ThisWorkbook.Path & "\成绩管理.accdb"
mytable = "期末成绩"
With con
.Provider = "microsoft.ace.oledb.12.0"
.Open mydata
End With
'利用connection对象的openschema方法产生数据表记录集
Set rs = con.OpenSchema(adSchemaTables)
'利用循环查询判断是否存在该数据表
' Do While Not rs.EOF
' 'rs!table_name=rs("table_name")
' If LCase(rs!table_name) = LCase(mytable) Then
' MsgBox "数据表《" & mytable & "》存在"
' GoTo hhh
' End If
' rs.MoveNext
'
' Loop
' MsgBox "数据表《" & mytable & "》不存在"
'利用recordset 对象的find方法查找数据表并判断是否存在
'find 方法会直接将光标定位到找的记录,如果没有找的,则将光标移动到eof
rs.Find "table_name='" & mytable & "'"
If rs.EOF Then
MsgBox "数据表《" & mytable & "》不存在"
Else
MsgBox "数据表《" & mytable & "》存在"
End If
hhh:
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
End Sub