获得ACCESS数据库的表及字段信息_不死鸟

http://blog.sina.com.cn/u/1237418690

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

获得ACCESS数据库的表及字段信息

(2016-11-03 16:01:30)

标签：

access

vba

字段

表

分类： WORD/ACCESS更奇妙

前几天写了一个VBA程序，可以获得ACCESS数据库的表及字段信息。保存一下

用引用了ADOx

下面是百度上关于ADOx的内容

ADOX 概述编辑

Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) 是对 ADO 对象和编程模型的扩展。它可用于创建、修改和删除模式对象。它还包括安全对象，可用于维护用户和组，以及授予和撤消对象的权限。要通过开发工具使用 ADOX，需要建立对 ADOX 类型库的引用"Microsoft ADO Ext. for DDL and Security"。

ADOX 对象编辑

Catalog 包含描述数据源模式目录的集合。

Column 表示表、索引或关键字的列。

Group 表示在安全数据库内有访问权限的组帐号。

Index 表示数据库表中的索引。

Key 表示数据库表中的主关键字、外部关键字或唯一关键字。

Procedure 表示存储的过程。

Table 表示数据库表，包括列、索引和关键字。

User 表示在安全数据库内具有访问权限的用户帐号。

View 表示记录或虚拟表的过滤集。

ADOX 方法编辑

Append（Columns）将新的 Column 对象添加到 Columns 集合。

Append（Groups）将新的 Group 对象添加到 Groups 集合。

Append（Indexes）将新的 Index 对象添加到 Indexes 集合。

Append（Keys）将新的 Key 对象添加到 Keys 集合。

Append（Procedures）将新的 Procedure 对象添加到 Procedures 集合。

Append（Tables）将新的 Table 对象添加到 Tables 集合。

Append（Users）将新的 User 对象添加到 Users 集合。

Append（Views）将新的 View 对象添加到 Views 集合。

Create 创建新的目录。

Delete 删除集合中的对象。

所属程序集

COM-->Microsoft ADO Ext. 2.8 for DDL and Security(Access 97-03文件，mdb类型)

若要支持Access2007版本以上创建的accdb文件，就需要使用

COM-->Microsoft ADO Ext. 6.0 for DDL and Security

http://s14/mw690/001lK57Yzy767YfNn2t9d&690

Option Explicit

Sub dosth()

Dim cat As New ADOx.Catalog

Dim mytbl As ADOx.Table

Dim myfield As ADOx.Column

Dim i%, lei%

Dim dbfullname As String

dbfullname = ThisWorkbook.Path & "\data_inf.mdb"

cat.ActiveConnection = "provider=microsoft.jet.oledb.4.0; data source=" & dbfullname

Cells.Clear

Range("A1:F1") = Array("表名称", "表类型", "字段名称", "字段类型", "字段长度", "字段描述")

i = 2

For Each mytbl In cat.Tables

For Each myfield In mytbl.Columns

Sheet3.Cells(i, 1) = mytbl.Name

Sheet3.Cells(i, 2) = mytbl.Type

Sheet3.Cells(i, 3) = myfield.Name

lei = myfield.Type

If lei = 3 Then Sheet3.Cells(i, 4) = "整数"

If lei = 7 Then Sheet3.Cells(i, 4) = "日期"

If lei = 202 Then Sheet3.Cells(i, 4) = "文本"

Sheet3.Cells(i, 5) = myfield.DefinedSize

Sheet3.Cells(i, 6) = myfield.Properties("Description")

i = i + 1

Next myfield

Next mytbl

Set cat = Nothing

Set myfield = Nothing

End Sub

http://s4/mw690/001lK57Yzy767YfW5Fh73&690

2017-5-27更新

Sub dosth()
    Dim cat As New ADOx.Catalog
    Dim mytbl As ADOx.Table
    Dim myfield As ADOx.Column
    Dim i%, lei%
    Dim dbfullname As String
    Dim myVBAConstant$

    dbfullname = ThisWorkbook.Path & "\data_inf.mdb"
    cat.ActiveConnection = "provider=microsoft.jet.oledb.4.0; data source=" & dbfullname
    Cells.Clear
    Range("A1:F1") = Array("表名称", "表类型", "字段名称", "字段类型", "字段长度", "字段描述")
    i = 2
    For Each mytbl In cat.Tables
        For Each myfield In mytbl.Columns
            Sheet3.Cells(i, 1) = mytbl.Name
            Sheet3.Cells(i, 2) = mytbl.Type
            Sheet3.Cells(i, 3) = myfield.Name
            lei = myfield.Type
            Select Case lei
            Case 20: myVBAConstant = "adBigInt"
            Case 128: myVBAConstant = "adBinary"
            Case 11: myVBAConstant = "adBoolean"
            Case 8: myVBAConstant = "adBSTR"
            Case 136: myVBAConstant = "adChapter"
            Case 129: myVBAConstant = "adChar"
            Case 6: myVBAConstant = "adCurrency"
            Case 7: myVBAConstant = "adDate"
            Case 133: myVBAConstant = "adDBDate"
            Case 134: myVBAConstant = "adDBTime"
            Case 135: myVBAConstant = "adDBTimeStamp"
            Case 14: myVBAConstant = "adDecimal"
            Case 5: myVBAConstant = "adDouble"
            Case 0: myVBAConstant = "adEmpty"
            Case 10: myVBAConstant = "adError"
            Case 64: myVBAConstant = "adFileTime"
            Case 72: myVBAConstant = "adGUID"
            Case 9: myVBAConstant = "adIDispatch"
            Case 3: myVBAConstant = "adInteger"
            Case 13: myVBAConstant = "adIUnknown"
            Case 205: myVBAConstant = "adLongVarBinary"
            Case 201: myVBAConstant = "adLongVarChar"
            Case 203: myVBAConstant = "adLongVarWChar"
            Case 131: myVBAConstant = "adNumeric"
            Case 138: myVBAConstant = "adPropVariant"
            Case 4: myVBAConstant = "adSingle"
            Case 2: myVBAConstant = "adSmallInt"
            Case 16: myVBAConstant = "adTinyInt"
            Case 21: myVBAConstant = "adUnsignedBigInt"
            Case 19: myVBAConstant = "adUnsignedInt"
            Case 18: myVBAConstant = "adUnsignedSmallInt"
            Case 17: myVBAConstant = "adUnsignedTinyInt"
            Case 132: myVBAConstant = "adUserDefined"
            Case 204: myVBAConstant = "adVarBinary"
            Case 200: myVBAConstant = "adVarChar"
            Case 12: myVBAConstant = "adVariant"
            Case 139: myVBAConstant = "adVarNumeric"
            Case 202: myVBAConstant = "adVarWChar"
            Case 130: myVBAConstant = "adWChar"
            Case Else: myVBAConstant = "Error"
            End Select
            Sheet3.Cells(i, 4) = myVBAConstant
            Sheet3.Cells(i, 5) = myfield.DefinedSize
            Sheet3.Cells(i, 6) = myfield.Properties("Description")
            i = i + 1
        Next myfield
    Next mytbl
    Set cat = Nothing
    Set myfield = Nothing
End Sub

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：[转载]圆周率是怎样算出来的

后一篇：《Access VBA活用范例大辞典》封面及目录

新浪BLOG意见反馈留言板　欢迎批评指正