如何在程序里获取Access数据库中的所有表和查询的列表

[cloonist]

就像SQLServer中可以使用SQL语句：sp_help 获得一个数据库的所有表和视图的列表一样，如何在Access数据库中也能获得其所有表和查询的列表呢？

[wwonion]

从Access数据库中取出所有表名，不包括系统表
using System;
using System.Data;
using System.Data.OleDb;


string ConnectionString = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=C:\Program Files\Microsoft Office\Office\1033\FPNWIND.mdb";
OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();

DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});
foreach (DataRow dr in schemaTable.Rows)
{
//表名
Console.WriteLine(dr["TABLE_NAME"]);

//字段名
DataTable columnTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
new object[] {null, null, dr["TABLE_NAME"].ToString(),null});


foreach(DataRow dr2 in columnTable.Rows)
{
Console.WriteLine(" {0}", dr2["COLUMN_NAME"]);
}

}
conn.Close();

[athossmth]

要使用DAO或者ADOX

[whwang]

Public Sub ListTablesAndQuerys()

Dim Tb As TableDef
Dim ncStr As String
Dim DB As Database

Set DB = CurrentDb

For Each Tb In DB.TableDefs
If Len(Tb.Connect) > 0 Then '判断是否链接表
Debug.Print "链结表：" & Tb.name
Else
Debug.Print "本地表：" & Tb.name
End If
Next

Dim qry As QueryDef
For Each qry In DB.QueryDefs
If qry.Connect <> "" Then
Debug.Print "传递查询：" & qry.name
Else
Debug.Print "本地查询：" & qry.name
End If
Next

End Sub

[蒋晟]

The following statement returns all the views in an Access database.

DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[] {null, null, null, "VIEW"});

For an example, visit http://www.aspitalia.com/articoli/db/storedqueries.aspx?page=3

An Access Stored procedure directly translates into an Access Query. That’s how it’s stored on the database. See

http://www.devcity.net/Articles/18/msaccess_sp.aspx

Note:queries created programmatically are invisible in Access. You can view the stored procedures by using Tools->Analyze->Documenter the select Queries Tab, select your query and click OK.