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
'功能:获取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
'************************************************************************* '**函 数 名: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
怎样使用一个查询获得数据库对象的名称(查询/窗体/表/报表/模块/宏)? 方法一: 请详细参阅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的
'功能:获取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
'用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
'**函 数 名: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
方法一: 请详细参阅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;这个问题问了好多遍了!提问前最好是自己搜索一下!!