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

Zricepig 2003-12-12 02:16:21
就是任意打开一个数据库,显示其中所有的表

thx
...全文
233 6 打赏 收藏 举报
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
哇呜 2003-12-12
使用ADOX会很方便,引用Microsoft ADO Ext. 2.X For DDL...那个,看看ADO或MSDN的帮助就知道了,有个Tables的
  • 打赏
  • 举报
回复
yoki 2003-12-12
怎样使用一个查询获得数据库对象的名称(查询/窗体/表/报表/模块/宏)?


方法一:

请详细参阅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;



这个问题问了好多遍了!提问前最好是自己搜索一下!!
  • 打赏
  • 举报
回复
yoki 2003-12-12
'*************************************************************************
'**函 数 名: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 2003-12-12
'功能:获取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
  • 打赏
  • 举报
回复
lovepeacer 2003-12-12
upup
  • 打赏
  • 举报
回复
SoHo_Andy 2003-12-12
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
  • 打赏
  • 举报
回复
相关推荐
发帖
数据库(包含打印,安装,报表)
加入

1199

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
帖子事件
创建了帖子
2003-12-12 02:16
社区公告
暂无公告