如何遍历 MS Access DB 中的所有表 [英] How to loop through all Tables in an MS Access DB

问题描述

我需要读取 Access 2003 数据库中 100 多个表的属性，并将这些详细信息 - 表名、字段名、类型和大小 - 写入文件以供进一步记录.

I need to read the properties of over 100 tables in an Access 2003 database and write those details - table name, field name, type and size - to a file for further documentation.

我无法从有关读取字段属性的网络搜索中找到任何内容，只能找到字段值...

I can find nothing from web searches about reading field properties, just field values ...

有人可以告诉我我必须声明哪些记录集变量(和语法)来循环遍历数据库中的所有表并从每个表中提取字段名称、类型和大小?我会将结果写入文本文件，但我想我可以处理！:)

Can someone please tell me what recordset variables I have to declare (and the syntax) to loop through all of the tables in the DB and extract the field name, type and size from each of them? I will be writing the results to a text file, but I think I can handle that! :)

我处于停滞状态，直到我能解决这个问题.我花了一天时间手动记录两个表.有些表有超过 100 个字段.

I'm at a standstill until I can sort this out. It took me a day to document TWO tables manually. Some of the tables have well over 100 fields.

推荐答案

带有这些选项的 Database Documenter 向导应该能够以最少的努力为您提供所需的内容.

The Database Documenter wizard with these options should give you what you want with the least effort.

如果这种方法不令人满意，您可以使用自定义 VBA 代码来收集您想要的信息.您可以通过循环访问 DAO TableDefs 集合来检索数据库中表的名称.

If that approach is not satisfactory, you can use custom VBA code to gather the information you want. You can retrieve the names of the tables in your database by looping through the DAO TableDefs collection.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
    ' ignore system and temporary tables
    If Not (tdf.name Like "MSys*" Or tdf.name Like "~*") Then
        Debug.Print tdf.name
    End If
Next
Set tdf = Nothing
Set db = Nothing

要获取所需的字段详细信息，请改编 Allen Browne 的 TableInfo() 函数 ...替换文件写入语句Debug.Print 语句.请注意，该函数使用了 2 个辅助函数，GetDescript 和 FieldTypeName，这两个函数都包含在该链接页面中.

To get the field details you want, adapt Allen Browne's TableInfo() function ... substitute file write statements for the Debug.Print statements. Note that function uses 2 helper functions, GetDescrip and FieldTypeName, both of which are included in that linked page.

这是我的数据库中一个表的 TableInfo() 的立即窗口输出示例——我认为它包含您想要的字段信息.

Here is an Immediate window output sample from TableInfo() for a table in my database --- I think it includes the field information you want.

TableInfo "foo"
FIELD NAME    FIELD TYPE    SIZE          DESCRIPTION
==========    ==========    ====          ===========
id            AutoNumber     4            
MyNumber      Long Integer   4            
MyText        Text           255          
bar           Long Integer   4            
==========    ==========    ====          ===========

调整函数后，从上面示例中的 For Each tdf 循环中调用它，并将其提供给每个 tdf.name:

After you've adapted the function, call it from the For Each tdf loop in the sample above and feed it each tdf.name:

TableInfo tdf.name

这篇关于如何遍历 MS Access DB 中的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！