用ADO连接数据库怎样获取表名？

[dxssx]

rt

[daisy8675]

要判斷資料庫中某一個 Table 是否存在？最簡單的方法就是錯誤嘗試法！什麼叫做錯誤嘗試法呢？就是先假設它存在，直接去開啟它，如果它真的存在，不會有錯誤產生，但是如果它不存在的話，就會有錯誤產生！做法大致如下：

1、設定 On Error Resume Next
2、直接開啟要檢查的 Table
3、如果檔案存在，則 err.Number=0

我們就以 Access 為例，資料庫使用 VB 內附的 Biblio.mdb，程式碼如下：

On Error Resume Next '1
Set Conn = CreateObject("ADODB.Connection")
Conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\Biblio.mdb"
Set rs = Conn.execute("Titles") '2
If Err.Number <> 0 Then MsgBox "Table 不存在" Else MsgBox "Table 存在" '3

[daisy8675]

看错了

Private Sub Command1_Click()
Dim intIndex As Integer
Dim objCon As ADODB.Connection
Dim objRst As ADODB.Recordset

' 產生一個新Connection物件，並設定objCon作為存取的物件變數。
Set objCon = New ADODB.Connection

' 使用Open方法連接，並設定相關參數。
objCon.Open gstrConn_NWind

' 找出資料表名稱。
Set objRst = objCon.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))

' 列舉名稱、型態、修改日期資料。
If objRst.RecordCount <> 0 Then
intIndex = 1
Do Until objRst.EOF
lvw1.ListItems.Add intIndex, , objRst.Fields("TABLE_NAME")
lvw1.ListItems(intIndex).SubItems(1) = objRst.Fields("TABLE_TYPE")
lvw1.ListItems(intIndex).SubItems(2) = Format(objRst.Fields("DATE_MODIFIED"), "yyyy/mm/dd")
intIndex = intIndex + 1
objRst.MoveNext
Loop
End If

objRst.Close
objCon.Close
Set objRst = Nothing
Set objCon = Nothing
End Sub

[online]

'引用微软 ADO Ext.2.7 for dll and Security
Private Sub Command1_Click()

Dim cat As ADOX.Catalog
Dim cnn As ADODB.Connection
Dim tbl As ADOX.Table


Set cat = New ADOX.Catalog
Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\csdn_vb\查看表名\article.mdb"

Set cat.ActiveConnection = cnn

On Error Resume Next

'Set tbl = cat.Tables("MyTable")
For Each tbl In cat.Tables
If Left(tbl.Name, 4) <> "MSys" Then
List1.AddItem tbl.Name
End If
Next

Set cat = Nothing

Set con = Nothing

End Sub

[dxssx]

我现在想的是连上一个不知道内容的数据库
想要将里面的得表名列出来

[lxqlogo0]

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

[lxqlogo0]

确定sql中的表的个数
Dim objcon As New ADODB.Connection
Dim objrs As New ADODB.Recordset
objcon.Open "Provider=sqloledb.1;user id=sa;password=csm@csm;Initial Catalog=xiazaijilu;Data Source="
objrs.Open "select * from sysobjects where xtype='u' ", objcon, 3, 1

[liaorui]

来晚了.

[TechnoFantasy]

http://community.csdn.net/Expert/topic/3149/3149165.xml?temp=.486706

[dxssx]

objRst.Fields("TABLE_NAME")
这个是什么类型？？？
怎么不能msgbox

[online]

表名啊，可以

试试我的代码

[yinweihong]

搜索（VB）
作者： 关键字：表名

时间：2004年数据

搜大类~

[dxssx]

我这不行啊
说什么参数"prompt"不能转换为类型"string"

MsgBox(rst.Fields("TABLE_NAME"))
你的代码我再试试看

[dxssx]

adox是什么？ 要添加引用么
我这好像不认

[online]

'引用微软 ADO Ext.2.x for dll and Security

[dxssx]

For Each tbl In cat.Tables
If Left(tbl.Name, 4) <> "MSys" Then
MsgBox(tbl.Name)
End If
Next
那个left报错
C:\Documents and Settings\dxssx\桌面\sx\test\WindowsApplication4\WindowsApplication4\Form1.vb(166): “Public Property Left() As Integer”没有任何参数，并且无法对它的返回类型进行索引。

[online]

搞了半天是vb.net

http://community.csdn.net/Expert/topic/3181/3181636.xml?temp=.7434351

[online]

Dim result() As String
Dim conn As New OleDbConnection
Dim tbl As DataTable
　　　　'连接数据库并返回表
With conn
'路径改为你所需的
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "E:\Made by myself\ReadFile-DB\ReadFile\bin\Code.mdb"
.Open()
tbl = .GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
.Close()
.Dispose
End With

'把报表加入数组
ReDim result(tbl.Rows.Count - 1)
Dim i As Integer

For i = 0 To tbl.Rows.Count - 1
result(i) = tbl.Rows(i).Item("TABLE_NAME")
Next

MsgBox(result(0))

[online]

http://community.csdn.net/Expert/ForumList_Search.asp?searchtype=1&bigclassid=52&smallclassid=5201&searchKeys=%B1%ED%C3%FB&author=&tabletype=now

[yinweihong]

Option Explicit

Private Const DB_PROVIDER = "SQLOLEDB"
Private Const SQL_SRV = "yige" '服务器名

Private cn As ADODB.Connection
Private rs As ADODB.Recordset
Private Sub OpenConnection() '打开连接
Set cn = New ADODB.Connection

With cn
.Provider = DB_PROVIDER
.ConnectionString = "Data Source=" & SQL_SRV & ";User ID=sa;Password=sa" '注意用户名 密码
.Open
End With

End Sub

' Lists all SQL Databases on server in connection object

Private Sub ListAllServerDatabases()
Dim rsDB As ADODB.Recordset
Dim i As Integer


lvwDatabases.ListItems.Clear 'ListView.name=lvwdatabase .view=3 插入几列 i = 1
Set rsDB = cn.OpenSchema(adSchemaCatalogs)

While Not rsDB.EOF
lvwDatabases.ListItems.Add i, "DB" & i, rsDB!CATALOG_NAME
rsDB.MoveNext
i = i + 1
Wend

rsDB.Close
Set rsDB = Nothing

End Sub
Private Sub lvwDatabases_ItemClick(ByVal Item As MSComctlLib.ListItem)
Call ListAllTables(Item.Text)
End Sub
' List the tables in selected Database

Private Sub ListAllTables(ByVal sDB As String)

Dim rsTables As ADODB.Recordset
Dim i As Integer
Dim NewConnString As String


lvwTables.ListItems.Clear 'ListView .name=lvwTables .view=3 插入几列
lvwTables.ColumnHeaders(1).Text = "Tables in " & sDB & " database"
NewConnString = "Data Source=" & SQL_SRV & ";User ID=sa;password=sa" & ";Initial Catalog=" & sDB

cn.Close
cn.Open NewConnString

i = 1
Set rsTables = cn.OpenSchema(adSchemaTables)

While Not rsTables.EOF

If UCase(rsTables!TABLE_TYPE) = "TABLE" Then
lvwTables.ListItems.Add i, "TABLE" & i, rsTables!TABLE_NAME
i = i + 1
End If

rsTables.MoveNext

Wend

rsTables.Close
Set rsTables = Nothing

End Sub

Private Sub Form_Load()
Call OpenConnection
Call ListAllServerDatabases
End Sub

[dxssx]

谢了