使用ADO如何查找ACCESS数据库中的所有表名

[Zricepig]

就是任意打开一个数据库,显示其中所有的表

thx

[SoHo_Andy]

Private Sub Command1_Click()
'功能：获取access库中表的个数及表的名称
'用ado怎样实现
'工程--->引用--->Microsoft ActiveX Data Object 2.x（版本号）
'----------------------------------------------------------------------------
Dim adoCN As New ADODB.Connection '定义数据库的连接
Dim strCnn As New ADODB.Recordset
Dim rstSchema As New ADODB.Recordset
Dim i As Integer
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\db1.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

• 打赏
• 举报

回复 赞

[lovepeacer]

upup

• 打赏
• 举报

回复 赞

[yoki]

'功能：获取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 rstSchema 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

• 打赏
• 举报

回复 赞

[yoki]

'*************************************************************************
'**函 数 名：LoadTbList
'**功能描述：列举数据库中的对象列表.对象的字段列表
'**输 入： sConcStr ADO的数据库连接字符串
'** ： sTbType 要列出的类型,0为表,1为视图.2是表和视图
'**输 出：在debug窗口中打印
'**调用模块：Microsoft ADO Ext. 2.x for DDL and Security
'*************************************************************************
Public Sub LoadTbList(ByVal sConcStr$, Optional ByVal sTbType = 2)
Dim iDbx As New ADOX.Catalog, iCount&, iI&

If sConcStr = "" Then Exit Sub

On Error GoTo LoadErr

iDbx.ActiveConnection = sConcStr
If sTbType < 0 And sTbType > 2 Then sTbType = 2

On Error Resume Next
With iDbx
For iCount = 0 To .Tables.Count - 1
Select Case UCase(.Tables(iCount).Type) & sTbType
Case "TABLE0", "TABLE2", "VIEW1", "VIEW2"
Debug.Print "对象名:" & .Tables(iCount).Name
With .Tables(iCount).Columns
For iI = 0 To .Count - 1
Debug.Print vbTab & "字段名:" & .Item(iI).Name
Next
End With
Case Else
End Select
Next
End With
Exit Sub

LoadErr:
MsgBox "错误:" & Error, 48
End Sub

• 打赏
• 举报

回复 赞

[yoki]

怎样使用一个查询获得数据库对象的名称(查询/窗体/表/报表/模块/宏)?


方法一：

请详细参阅ADO参考文档中OpenSchema
如：
'功能：获取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 rstSchema 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

方法二：

Access 系统表 MsysObjects 包含了数据库对象列表, 尽管未在文档中记载, 你仍可通过查询它来获取你想要的.

注: 请不要有意或无意地修改任何ACCESS系统表,否则会出现不可意料的情况.

使用下列 SQL 语句来获取你想要的

查询:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;


窗体:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;


表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;


报表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;


模块:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;


宏:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;



这个问题问了好多遍了！提问前最好是自己搜索一下！！

• 打赏
• 举报

回复 赞

[哇呜]

使用ADOX会很方便，引用Microsoft ADO Ext. 2.X For DDL...那个，看看ADO或MSDN的帮助就知道了，有个Tables的

• 打赏
• 举报

回复 赞