1,216
社区成员
发帖
与我相关
我的任务
分享怎样遍历数据库中的数据表
我变了一个程序，运行的时候希望查找一下数据库中是否存在一个名字叫mytable的数据表，如果有则删除掉，请问怎样才能知道这个表是否存在呀
...全文
请发表友善的回复…
发表回复
RUKYO 2004-08-14
- 打赏
- 举报
回复 | 推荐 | 收藏 | 专题 | 公告 | 管理 | 保存 | 关闭窗口
点击“管理”
点击“管理”
haohaohappy 2004-08-14
- 打赏
- 举报
简单点：
如果你只想删除他那就这样
no error resume next
cn.Execute "drop table mytable"
on error goto 0
如果你只想删除他那就这样
no error resume next
cn.Execute "drop table mytable"
on error goto 0
cnqdcc 2004-08-13
- 打赏
- 举报
万分感谢，可是我怎么给大家分呀
online 2004-08-13
- 打赏
- 举报
'引用微软 ADO Ext.2.7 for dll and Security
Dim cat As ADOX.Catalog
Dim cnn As ADODB.Connection
Dim tbl As ADOX.Table
Private Sub Command1_Click()
On Error Resume Next
For Each tbl In cat.Tables
If Left(tbl.Name, 4) <> "MSys" Then
List1.AddItem tbl.Name
End If
Next
End Sub
Private Sub Form_Load()
Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\csdn_vb\database\treeview的节点添加\article.mdb"
Set cat.ActiveConnection = cnn
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set cat = Nothing
Set con = Nothing
End Sub
Dim cat As ADOX.Catalog
Dim cnn As ADODB.Connection
Dim tbl As ADOX.Table
Private Sub Command1_Click()
On Error Resume Next
For Each tbl In cat.Tables
If Left(tbl.Name, 4) <> "MSys" Then
List1.AddItem tbl.Name
End If
Next
End Sub
Private Sub Form_Load()
Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\csdn_vb\database\treeview的节点添加\article.mdb"
Set cat.ActiveConnection = cnn
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set cat = Nothing
Set con = Nothing
End Sub
脚印 2004-08-13
- 打赏
- 举报
Dim DBConn As New Adodb.Connection
Dim TableNameRs As New Adodb.Recordset
With DBConn '自己写ConnectionString
If .State = adStateClosed Then
.Open
End If
Set TableNameRs = .OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
If TableNameRs.RecordCount > 0 Then
Do While Not TableNameRs.EOF
If trim(TableNameRs!Table_Name)= mytable Then
.Execute "DROP TABLE mytable"
Exit do
End If
TableNameRs.MoveNext
Loop
end if
end with
Dim TableNameRs As New Adodb.Recordset
With DBConn '自己写ConnectionString
If .State = adStateClosed Then
.Open
End If
Set TableNameRs = .OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
If TableNameRs.RecordCount > 0 Then
Do While Not TableNameRs.EOF
If trim(TableNameRs!Table_Name)= mytable Then
.Execute "DROP TABLE mytable"
Exit do
End If
TableNameRs.MoveNext
Loop
end if
end with
lihonggen0 2004-08-13
- 打赏
- 举报
sql server是:
SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U'
SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U'
lihonggen0 2004-08-13
- 打赏
- 举报
'----------------------------------------------------------------------------
'
'Author:lihonggen0
'Date:2003-6-19
'功能：获取access库中表的个数及表的名称
'用ado怎样实现
'工程--->引用--->Microsoft ActiveX Data Object 2.x（版本号）
'----------------------------------------------------------------------------
Private Sub Form_Load()
Dim adoCN As New ADODB.Connection '定义数据库的连接
Dim strCnn As New ADODB.Recordset
Dim I As Integer
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.MDB;Persist Security Info=False"
adoCN.Open str1
Set rstSchema = adoCN.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
If rstSchema!TABLE_TYPE = "TABLE" Then
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
I = I + 1
End If
rstSchema.MoveNext
Loop
MsgBox I
rstSchema.Close
adoCN.Close
Debug.Print out
End Sub
http://expert.csdn.net/Expert/FAQ/FAQ_Index.asp?id=8159
northwolves 2004-08-13
- 打赏
- 举报
Sub disptables(mydbpath As String) '显示指定数据库中所有表名
Dim msg As String
Dim mycnn As New ADODB.Connection
Dim mycat As New ADOX.Catalog
mycnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & mydbpath & ";"
msg = ""
Set mycat.ActiveConnection = mycnn
For i = 0 To mycat.Tables.Count - 1
If Left(mycat.Tables.Item(i).Name, 4) <> "MSys" Then '去掉系统表
msg = msg & mycat.Tables.Item(i).Name & vbCrLf
End If
Next
MsgBox msg
End Sub
Dim msg As String
Dim mycnn As New ADODB.Connection
Dim mycat As New ADOX.Catalog
mycnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & mydbpath & ";"
msg = ""
Set mycat.ActiveConnection = mycnn
For i = 0 To mycat.Tables.Count - 1
If Left(mycat.Tables.Item(i).Name, 4) <> "MSys" Then '去掉系统表
msg = msg & mycat.Tables.Item(i).Name & vbCrLf
End If
Next
MsgBox msg
End Sub
