dim conn as adodb.connection dim rs as adodb.recordseton error resume next set conn=createobject("adodb.connection") conn.open "数据库连接"set rs=conn.execute("你的表",,adCmdTableDirect) if err.number<>0 then msgbox "表不存在" else msgbox "表存在" end if
1.连接数据库,如果成功,则存在,否则,不存在. 2.数据表是否存在. 'TRUE 存在,FALSE不存在 Function ChkTab(Cnn As ADODB.Connection, Tablename As String) As Boolean On Error Resume Next Dim strsql As String strsql = "select top 1 from " & Tablename Cnn.Execute strsql ChkTab = (Err.Number = 0) End Function
rs.open "select 1 from master..sysdatabases where name='test200'",cn if not rs.eof then msgbox "存在数据库test200" rs.close rs.open"select 1 from test200..sysobjects where xtype='u' and name='company'",cn if not rs.eof then msgbox "数据库test200中存在表名为company的表"
'********************************************************* '* 名称:TableExists '* 功能:判断表是否存在(表名) '* 用法:TableExists(表名) adoCN是一个access或者sql 的连接 '********************************************************* 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
在Sqlserver中,如果表存在,删除表的SQL语句: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[表名]
dim rs as adodb.recordseton error resume next
set conn=createobject("adodb.connection")
conn.open "数据库连接"set rs=conn.execute("你的表",,adCmdTableDirect)
if err.number<>0 then
msgbox "表不存在"
else
msgbox "表存在"
end if
2.数据表是否存在.
'TRUE 存在,FALSE不存在
Function ChkTab(Cnn As ADODB.Connection, Tablename As String) As Boolean
On Error Resume Next
Dim strsql As String
strsql = "select top 1 from " & Tablename
Cnn.Execute strsql
ChkTab = (Err.Number = 0)
End Function
if not rs.eof then msgbox "存在数据库test200"
rs.close
rs.open"select 1 from test200..sysobjects where xtype='u' and name='company'",cn
if not rs.eof then msgbox "数据库test200中存在表名为company的表"
'* 名称:TableExists
'* 功能:判断表是否存在(表名)
'* 用法:TableExists(表名) adoCN是一个access或者sql 的连接
'*********************************************************
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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[表名]