如何循环访问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个辅助函数GetDescrip和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屋！