如何从MS Access数据库获取表名？

Microsoft SQL Server and MySQL have an INFORMATION_SCHEMA table that I can query. However it does not exist in an MS Access database.

Microsoft SQL Server和MySQL有一个我可以查询的INFORMATION_SCHEMA表。但是,它在MS Access数据库中不存在。

Is there an equivalent I can use?

有可用的等价物吗?

7 个解决方案

#1

To build on Ilya's answer try the following query:

要建立在Ilya的答案上,请尝试以下查询:

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6)))
order by MSysObjects.Name

(this one works without modification with an MDB)

(这个没有修改的MDB工作)

ACCDB users may need to do something like this

ACCDB用户可能需要做这样的事情

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6))
        AND ((MSysObjects.Flags)=0))
order by MSysObjects.Name

As there is an extra table is included that appears to be a system table of some sort.

因为有一个额外的表,似乎是某种系统表。

#2

You can use schemas in Access.

您可以在Access中使用模式。

Sub ListAccessTables2(strDBPath)
   Dim cnnDB As ADODB.Connection
   Dim rstList As ADODB.Recordset

   Set cnnDB = New ADODB.Connection

   ' Open the connection.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   ' Open the tables schema rowset.
   Set rstList = cnnDB.OpenSchema(adSchemaTables)

   ' Loop through the results and print the
   ' names and types in the Immediate pane.
   With rstList
      Do While Not .EOF
         If .Fields("TABLE_TYPE") <> "VIEW" Then
            Debug.Print .Fields("TABLE_NAME") & vbTab & _
               .Fields("TABLE_TYPE")
         End If
         .MoveNext
      Loop
   End With
   cnnDB.Close
   Set cnnDB = Nothing
End Sub

From: http://msdn.microsoft.com/en-us/library/aa165325(office.10).aspx

#3

Here is an updated answer which works in Access 2010 VBA using Data Access Objects (DAO). The table's name is held in TableDef.Name. The collection of all table definitions is held in TableDefs. Here is a quick example of looping through the table names:

以下是使用数据访问对象(DAO)在Access 2010 VBA中运行的更新答案。表的名称保存在TableDef.Name中。所有表定义的集合都保存在TableDefs中。以下是循环表名称的快速示例:

Dim db as Database
Dim td as TableDef
Set db = CurrentDb()
For Each td In db.TableDefs
  YourSubTakingTableName(td.Name)
Next td

#4

Schema information which is designed to be very close to that of the SQL-92 INFORMATION_SCHEMA may be obtained for the Jet/ACE engine (which is what I assume you mean by 'access') via the OLE DB providers.

可以通过OLE DB提供程序为Jet / ACE引擎(我假设您的意思是“访问”)获取旨在与SQL-92 INFORMATION_SCHEMA非常接近的模式信息。

See:

OpenSchema Method (ADO)

OpenSchema方法(ADO)

Supported Schema Rowsets

支持的架构行集

#5

Getting a list of tables:

获取表格列表:

SELECT 
    Table_Name = Name, 
FROM 
    MSysObjects 
WHERE 
    (Left([Name],1)<>"~") 
    AND (Left([Name],4) <> "MSys") 
    AND ([Type] In (1, 4, 6)) 
ORDER BY 
    Name

#6

SELECT 
Name 
FROM 
MSysObjects 
WHERE 
(Left([Name],1)<>"~") 
AND (Left([Name],4) <> "MSys") 
AND ([Type] In (1, 4, 6)) 
ORDER BY 
Name

#7

Best not to mess with msysObjects (IMHO).

最好不要搞乱msysObjects(恕我直言)。

CurrentDB.TableDefs
CurrentDB.QueryDefs
CurrentProject.AllForms
CurrentProject.AllReports
CurrentProject.AllMacros

#1