ADODB.Connection.OpenSchema from C#.NET 2.0

[Photog]

I'm using ADO (through the ADODB interop assembly) from inside a C# .NET
application.

I'm trying to use ADODB.Connection.OpenSchema to return table and column
info from an MS Access database.

Here's the call:

rs = m_cn.OpenSchema(ADODB.SchemaEnum.adSchemaTables,
new object[] {null, null, tableName, "TABLE"}, null);

m_cn is an object of type ADODB.Connection. The variable tableName is a
string holding the name of the table for which schema info is desired.

When the code is exectued, a COMException is thrown with the following
message:

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

I've successfully run this code in VBA as follows:

OpenSchema(adSchemaTables, Array(Empty, Empty Empty, "TABLE"))

Any ideas why the call from .NET is not working? I've successfully run
SELECT, CREATE and UPDATE statements in this environment - it's just
OpenSchema that's giving me problems.

Thanks for your help...

[Kevin Yu [MSFT]]

Hi Photog,

It is because of the last argument for OpenSchema you have specified, that
the exception was thrown. The last argument is a schemaID which cannot be
null. It is the GUID for a provider-schema query not defined by the OLE DB
specification.

Since you're developing an app in C#, I strongly suggest you use ADO.NET
instead of ADO. It works better in a .NET app and doesn't involve COM
Interop. In ADO.NET, if you need to get the schema of certain table in an
MSAccess database, you can use

OleDbConnection cnn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\db1.mdb;Persist Security Info=False");
cnn.Open();
DataTable dt = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, "Table1", "TABLE" });
cnn.Close();

In ADO.NET 2.0, you can also use the following instead of
GetOleDbSchemaTable. Either will be fine.

dt = cnn.GetSchema("Tables", new string[] { null, null,
"Table1", "TABLE" });

If anything is unclear, please feel free to let me know.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Photog]

Kevin,

Thanks much for the quick response. I understand that ADO.NET is
preferable, but right now I have an already written app using ADO and I
would like to get this one function call to work correctly.

Is there any way to do so?

My documentation says that the SchemaID parameter is ignored unless a
provider specific schema is requested. I'm not requesting a provider
specific schema, so I think the function will just ignore whatever is passed
in this parameter. If this is true, it seems that I need to provide a dummy
GUID which the function can ignore.

Is this the case? I'll play with this some more, but I would appreciate it
if you could send me an example showing how to make the function call with a
dummy GUID. I'm not quite sure how to obtain a GUID in the correct format -
I tried generating one and passing it as a text string, but it did not work.

I do appreciate your help...thanks again.

Post by Kevin Yu [MSFT]
Hi Photog,
It is because of the last argument for OpenSchema you have specified, that
the exception was thrown. The last argument is a schemaID which cannot be
null. It is the GUID for a provider-schema query not defined by the OLE DB
specification.
Since you're developing an app in C#, I strongly suggest you use ADO.NET
instead of ADO. It works better in a .NET app and doesn't involve COM
Interop. In ADO.NET, if you need to get the schema of certain table in an
MSAccess database, you can use
OleDbConnection cnn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\db1.mdb;Persist Security Info=False");
cnn.Open();
DataTable dt = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, "Table1", "TABLE" });
cnn.Close();
In ADO.NET 2.0, you can also use the following instead of
GetOleDbSchemaTable. Either will be fine.
dt = cnn.GetSchema("Tables", new string[] { null, null,
"Table1", "TABLE" });
If anything is unclear, please feel free to let me know.
Kevin Yu
Microsoft Online Community Support
============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi Photog,

To call the OpenSchema method without supplying the SchemaID, you can use
System.Reflection.Missing.Value. Here is an example.

ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
cnn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\db1.mdb;Persist Security Info=False", null, null, -1);
rs = cnn.OpenSchema(ADODB.SchemaEnum.adSchemaTables, new
object[] { null, null, "Table1", "TABLE" },
System.Reflection.Missing.Value);

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi Photog,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)