Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=Microsoft.ace.OLEDB.12.0;Data Source=" & App.Path & "\storage.accdb" & ";Persist Security Info=False"
Set rs = cn.OpenSchema(adSchemaTables)
Do Until rs.EOF
If rs!TABLE_NAME = Text3.Text Then
MsgBox "存在此表名"
Exit Do
End If
rs.MoveNext
Loop
方法1:另外一种风格
connectstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "\my.mdb" & ";Persist Security Info=False;Jet OLEDB:Database Password=xiangyang"
Set gsSys_db = New ADODB.Connection
gsSys_db.Open connectstr
Dim TableName As String
Set rs = gsSys_db.OpenSchema(adSchemaTables)
Do While Not rs.EOF
TableName = rs.Fields("TABLE_NAME")
If TableName = "你要查询的表名" Then
MsgBox "表已存在"
Exit Do
end if
rs.MoveNext
Loop
方法2:
'*********************************************************
'* 名称:TableExists
'* 功能:判断表是否存在(表名)
'* 用法:TableExists(表名) adoCN是一个access的连接
'*********************************************************
Public Function TableExists(findTable As String) As Boolean
Dim rstSchema As New ADODB.Recordset
Set rstSchema = adoCN.OpenSchema(adSchemaTables)
rstSchema.Find "TABLE_NAME='" & findTable & "'"
If rstSchema.EOF Then
TableExists = False
Else
TableExists = True
End If
rstSchema.Close
End Function
关于openschema:http://www.w3school.com.cn/ado/met_conn_openschema.asp