[已解决]请问，如何历遍access中的表？并获得它们的属性？

mansohu

请问，如何历遍access中的表？并获得它们的属性？

最佳答案

月排行榜 / 总排行榜

hwc2ycy

2013-5-5 15:51

OpenSchema 方法
      

从提供者获取数据库模式信息。

语法

Set recordset = connection.OpenSchema (QueryType, Criteria, SchemaID)

返回值

返回包含模式信息的 Recordset 对象。Recordset 将以只读、静态游标打开。

参数

QueryType   所要运行的模式查询类型，可以为下列任意常量。

Criteria   可选。每个 QueryType 选项的查询限制条件数组，如下所列：

QueryType 值 Criteria 值
AdSchemaAsserts CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
AdSchemaCatalogs CATALOG_NAME
AdSchemaCharacterSets CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
AdSchemaCheckConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
AdSchemaCollations COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
AdSchemaColumnDomainUsage DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
COLUMN_NAME
AdSchemaColumnPrivileges TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
GRANTOR
GRANTEE
adSchemaColumns TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintColumnUsage TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintTableUsage TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaForeignKeys PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
adSchemaIndexes TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME
TYPE
TABLE_NAME
adSchemaKeyColumnUsage CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaPrimaryKeys PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
adSchemaProcedureColumns PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
COLUMN_NAME
adSchemaProcedureParameters PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PARAMTER_NAME
adSchemaProcedures PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PROCEDURE_TYPE
adSchemaProviderSpecific 参见说明
adSchemaProviderTypes DATA_TYPE
BEST_MATCH
adSchemaReferentialConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaSchemata CATALOG_NAME
SCHEMA_NAME
SCHEMA_OWNER
adSchemaSQLLanguages <无>
adSchemaStatistics TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaTableConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
adSchemaTablePrivileges TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
GRANTOR
GRANTEE
adSchemaTables TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
adSchemaTranslations TRANSLATION_CATALOG
TRANSLATION_SCHEMA
TRANSLATION_NAME
adSchemaUsagePrivileges OBJECT_CATALOG
OBJECT_SCHEMA
OBJECT_NAME
OBJECT_TYPE
GRANTOR
GRANTEE
adSchemaViewColumnUsage VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViewTableUsage VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViews TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME

跳转到最佳答案楼层

hwc2ycy

哪些属性，是数据库呢还是？

hwc2ycy

OpenSchema方法可以研究下
CONNECTION对象的一个方法，从提供者获取数据库模式信息。

hwc2ycy

OpenSchema 方法
      

从提供者获取数据库模式信息。

语法

Set recordset = connection.OpenSchema (QueryType, Criteria, SchemaID)

返回值

返回包含模式信息的 Recordset 对象。Recordset 将以只读、静态游标打开。

参数

QueryType   所要运行的模式查询类型，可以为下列任意常量。

Criteria   可选。每个 QueryType 选项的查询限制条件数组，如下所列：

QueryType 值 Criteria 值
AdSchemaAsserts CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
AdSchemaCatalogs CATALOG_NAME
AdSchemaCharacterSets CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
AdSchemaCheckConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
AdSchemaCollations COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
AdSchemaColumnDomainUsage DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
COLUMN_NAME
AdSchemaColumnPrivileges TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
GRANTOR
GRANTEE
adSchemaColumns TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintColumnUsage TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintTableUsage TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaForeignKeys PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
adSchemaIndexes TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME
TYPE
TABLE_NAME
adSchemaKeyColumnUsage CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaPrimaryKeys PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
adSchemaProcedureColumns PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
COLUMN_NAME
adSchemaProcedureParameters PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PARAMTER_NAME
adSchemaProcedures PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PROCEDURE_TYPE
adSchemaProviderSpecific 参见说明
adSchemaProviderTypes DATA_TYPE
BEST_MATCH
adSchemaReferentialConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaSchemata CATALOG_NAME
SCHEMA_NAME
SCHEMA_OWNER
adSchemaSQLLanguages <无>
adSchemaStatistics TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaTableConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
adSchemaTablePrivileges TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
GRANTOR
GRANTEE
adSchemaTables TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
adSchemaTranslations TRANSLATION_CATALOG
TRANSLATION_SCHEMA
TRANSLATION_NAME
adSchemaUsagePrivileges OBJECT_CATALOG
OBJECT_SCHEMA
OBJECT_NAME
OBJECT_TYPE
GRANTOR
GRANTEE
adSchemaViewColumnUsage VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViewTableUsage VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViews TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME

mansohu

hwc2ycy 发表于 2013-5-5 15:51
OpenSchema 方法
      

我的意思是想罗列数据库有我创建的表，比如说：
for each i in mydbm.tables
     AAA=i.name     ‘对各表进行操作
next i
当然上面的代码是不对了，但应该知道我想得到啥？

hwc2ycy

openschama方法会把得到的信息返回到RECORDSET对象中，你再遍历RECORDSET就成了。

hwc2ycy

1. Function getSheetName(strFileName As String) As String
   
2. '需要定义的常量
   
3. '    Const adUseClient = 3
   
4. '    Const adModeShareDenyWrite = 8
   
5. '    Const adModeReadWrite = 3
   
6. '    Const adModeRead = 1
   
7. 
   
8.     Dim AdoConn As Object, AdoRst As Object
   
9.     Dim StrConn$, strSQL$
   
10.     Dim DataSource$
    
11.    
    
12.     Set AdoConn = CreateObject("ADODB.Connection")
    
13.     Set AdoRst = CreateObject("ADODB.Recordset")
    
14. 
    
15.     DataSource = strFileName
    
16. 
    
17.     Select Case Application.Version
    
18.         Case Is = "14.0":
    
19.             StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
    
20.                       DataSource & ";Extended Properties=""Excel 12.0;HDR=yes;imex=1"";"""
    
21.         Case Is = "12.0"
    
22.             StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
    
23.                       DataSource & ";Extended Properties=""Excel 12.0;HDR=yes;imex=1"";"""
    
24.         Case Else
    
25.             StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
    
26.                       "Data Source=" & DataSource & "Extended Properties=""Excel 8.0;HDR=yes;imex=1"";"
    
27.     End Select
    
28. 
    
29.     On Error GoTo ErrCheck
    
30.     With AdoConn
    
31.         .CommandTimeout = 5
    
32.         .ConnectionTimeout = 5
    
33.         .CursorLocation = adUseClient
    
34.         .Mode = adModeRead    'Write    'adModeShareDenyWrite
    
35.         .ConnectionString = StrConn
    
36.         .Open
    
37.     End With
    
38.     Set AdoRst = AdoConn.OpenSchema(adSchemaTables)
    
39. 
    
40.     Do Until AdoRst.EOF
    
41.         getSheetName = getSheetName & "[" & AdoRst!TABLE_NAME & "],"
    
42.         AdoRst.MoveNext
    
43.     Loop
    
44.     getSheetName = Left(getSheetName, Len(getSheetName) - 1)
    
45. 
    
46.     AdoConn.Close
    
47.     Exit Function
    
48. 
    
49. ErrCheck:
    
50. 
    
51.     MsgBox Err.Number & vbCrLf & _
    
52.            Err.Description
    
53. 
    
54.     Set AdoRst = Nothing
    
55.     Set AdoConn = Nothing
    
56. End Function
    

复制代码

自定义函数，通过ADO方法获取工作簿中所有的工作表名称，用的就是OPENSCHAMA方法。