作者: 蓝叶菱
标题: Access系统表的访问2 
关键字: Access系统表 
分类: 数据库-文件型 
密级: 公开


2002-12-8 21:08:37

〖摘自 文章区〗
(问): 怎样使用一个查询获得数据库对象的名称(查询/窗体/表/报表/模块/宏)?

(答): 请详细参阅ADO参考文档中OpenSchema

(答): Access 系统表 MsysObjects 包含了数据库对象列表, 尽管未在文档中记载, 你仍可通过查询它来获取你想要的.

注: 请不要有意或无意地修改任何ACCESS系统表,否则会出现不可意料的情况.

使用下列 SQL 语句来获取你想要的

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name; 

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name; 

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name; 

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;
OpenSchema 方法


Set recordset = connection.OpenSchema (QueryType, Criteria, SchemaID)


返回包含模式信息的 Recordset 对象。Recordset 将以只读、静态游标打开。


QueryType   所要运行的模式查询类型,可以为下列任意常量。

Criteria   可选。每个 QueryType 选项的查询限制条件数组,如下所列:

QueryType 值 Criteria 值 

SchemaID   OLE DB 规范没有定义用于提供者模式查询的 GUID。如果 QueryType 设置为 adSchemaProviderSpecific,则需要该参数,否则不使用它。


OpenSchema 方法返回与数据源有关的信息,例如关于服务器上的表以及表中的列等信息。

Criteria 参数是可用于限制模式查询结果的值数组。每个模式查询有它支持的不同参数集。实际模式由 IDBSchemaRowset 接口下的 OLE DB 规范定义。ADO 中所支持的参数集已在上面列出。

如果提供者定义未在上面列出的非标准模式查询,则常量 adSchemaProviderSpecific 将用于 QueryType 参数。在使用该常量时需要 SchemaID 参数传递模式查询的 GUID 以用于执行。如果 QueryType 设置为 adSchemaProviderSpecific 但是没有提供 SchemaID,将导致错误。

提供者不需要支持所有的 OLE DB 标准模式查询,只有 adSchemaTables、adSchemaColumns 和 adSchemaProviderTypes 是 OLE DB 规范需要的。但是对于这些模式查询,提供者不需要支持上面列出的 Criteria 条件约束。

远程数据服务用法   OpenSchema 方法在客户端 Connection 对象上无效。

注意   在 Visual Basic 中,在由 Connection 对象的 OpenSchema 方法所返回的 Recordset 中有 4 字节无符号整型 (DBTYPE UI4) 的列无法与其他变量比较。有关 OLE DB 数据类型的详细信息,请参阅“Microsoft OLE DB 程序员参考”的第十章和附录 A。

Problems with C# 
Because ADO MD relies heavily on schema rowsets, you need to be able to fully utilize the OpenSchema method. Other than MDX, OpenSchema is the only mechanism that ADO MD provides to retrieve the contents of the schema rowset tables. Unfortunately, the OpenSchema method is difficult to call from a .NET language such as C# because of the complexity of the OpenSchema parameters. Two factors create this complexity: The parameters are optional, and the criteria parameter is an array. Each of these factors creates a problem when you try to integrate .NET and COM.

OpenSchema is designed so that you call it with one, two, or three parameters, as the following example calls show:

OpenSchema QueryType
OpenSchema QueryType, Criteria
OpenSchema QueryType, Criteria, SchemaID
However, in C# you can't issue the first two of these calls because you can't omit parameters—even when they're declared optional in the COM type library. This restriction is part of C# syntax. In other functions that take optional parameters, this type of restriction isn't a problem because you can issue the call with all parameters, passing default values for any parameters you would have omitted if the language had supported optional parameters (like VB does). But with OpenSchema, passing default parameters is difficult and risky because the values for the SchemaID parameter aren't available in the ADO MD type library. You'd have to determine what the default value for SchemaID is and declare the equivalent value in C#. Using SchemaID's default value is risky because a future version of ADO MD could change the values of these constants, and your code would no longer work because its declaration would contain an old value. The methods I used to solve these problems don't include redeclaring any ADO MD SchemaID constants in C# that could possibly change in the future. Let's look at each of the two problems you face with OpenSchema.

Solving Integration Problems 
First, let's look at how to deal with the problem of OpenSchema's optional parameters. As I just explained, C# doesn't support omitting parameters, and the values for the third parameter, SchemaID, aren't included in the ADO MD type library. Thus, you have to determine an innovative way to call OpenSchema. To omit a parameter, you have to take advantage of late binding. Usually, binding takes place during a program's compilation, but late binding means that a method call and its parameters aren't validated against its definition until runtime. Listing 1 shows an example of how to use late binding with only one parameter to call the Open method on the Connection object. The example works because the InvokeMember method in the .NET runtime library can determine how to issue a method call with some parameters omitted, which the C# compiler can't do. Calling the Open method with just a connection string is convenient because you rarely use other parameters (e.g., UserID, Password, Options) with Analysis Services. The UserID and Password parameters are typically unnecessary because Analysis Services uses Windows authentication to determine the identity of the client application.

The use of InvokeMember() in Listing 1 deserves an explanation. InvokeMember() is a flexible and powerful method that lets you perform late binding to access an object property (e.g., GET or SET) or lets you call a method in which the structure of the call is determined at runtime. Because ADO and ADO MD support retrieval of type information about the available methods, parameters, and properties at runtime, InvokeMember() can determine how to package the parameters you provide it so that it creates the correct call to the Open method on the Connection object.

The InvokeMember method is available on each Type object in .NET. Don't confuse a Type object with an object of a particular type. A Type object in .NET contains information about the type but isn't an instance of the type. (The fourth parameter of InvokeMember, Conn in Listing 1, is the instance of the type on which you issue the method call.)

The first parameter of InvokeMember, Open in Listing 1, is the name of the method or property to be accessed. The second parameter, BindingFlags.InvokeMethod, tells InvokeMember what type of operation you want performed. In the example that Listing 1 shows, you want InvokeMember to invoke a method. The third parameter is the binding object. If you pass NULL as the third parameter, as Listing 1 shows, you get the default binding object. The binding object controls the manner in which the .NET runtime library accesses the object (which is the fourth parameter, Conn). Finally, the fifth parameter, new Object[], is the list of parameters that you pass to the method. I could have added multiple items to the array to pass more parameters to the method, but instead I passed an array containing just the connection string.

The second problem you encounter when you issue method calls to OpenSchema is figuring out how to declare and construct the Criteria parameter in C#. When I was trying to integrate my COM applications with .NET, I encountered this problem because I didn't have any examples of how to declare the elements of the criteria array in C#. Usually, you can use the Visual Studio .NET object browser to see a parameter's type. But in this case, the types can change with different calls to OpenSchema (depending on what restrictions you want to use) and the parameters are nested inside an array—the object browser doesn't show types inside an array parameter. C#'s data types aren't exactly the same as those in COM, so to match the parameters in a COM method call correctly, you need to know how the COM interoperability layer in .NET will translate the .NET data types into COM data types. Unfortunately, if you get the data types wrong, you get a generic error message that doesn't give you a clue about how to fix the problem. In COM, the Criteria parameter is a safe array of BSTR variants; you'd never need to know that information if you used ADO MD from VB 6.0. You can pass a regular VB array to OpenSchema, and it just works. Explaining a safe array of BSTR variants is beyond the scope of this article, but I'll demonstrate how to define the equivalent criteria array in .NET.

LISTING 1: Code That Omits Optional Parameters in the Open Method
Conn = new ADODB.ConnectionClass();
ADODB.Recordset Rs;
Object ob=new Object();
string connectionString =
      "Provider=" + Provider + ";" +
      "Data Source=" + Server;

      // Use late binding to issue an Open method.
            new Object[] { connectionString } );

catch (Exception e)
    string ErrorString = e.Message;




 回复人: savage5000(野人) ( ) 信誉:99  2003-07-22 14:49:18Z  得分:70  
string  dsn="Data Source=" + combo_DSN.Text + ";UID=" + UserName.Text + ";PWD=" + Password.Text +";";

 ADODB.Connection con=new ADODB.Connection();
     con.Open (dsn,"","",0);  }
 catch( Exception e1){
    MessageBox.Show (e1.Message );

ADODB.Recordset rs;
Object aa=new Object ();
Object ob=new Object ();
// Use late binding to issue an Open method.
ob=con.GetType ().InvokeMember (
System.Reflection .BindingFlags.InvokeMethod,
new Object[] { ADODB.SchemaEnum.adSchemaTables ,new Object[]{null,null,null,"TABLE"}} );

rs=(ADODB.Recordset )ob;
 aa=rs.get_Collect(2);  // 获取表名称,详细的index号内容察看
                                // ADODB.SchemaEnum.adSchemaTables查询
                               // 类型返回字段 
con.Close ();