OpenSchema fails when connecting to a persisted recordset

[Dominic Marshall]

Hi,

I am trying to read a nested recordset persisted through Access into Excel. This all work fine by accessing the recordset fields property except when the nested elements are empty. This means that the loaded data can end up in the wrong column cell when using the fields index and trying to load the nested values across a sheet.

I tried to use OpenSchema so I could access the table and column structure so that I could access fields via column name rather than index which would enable me to ensure that the data ended up in the correct column cell.

Set conn = New ADODB.Connection
conn.Open "Provider=MSPersist;"
Set myRecordset = conn.Execute("C:\Temp\cust.xml")
Set TableSchema = conn.OpenSchema(adSchemaTables)

When calling OpenSchema the following error is returned "Run-time error: 3251: Application-defined or object-defined error"

The Microsoft documentation indicates that the adSchemaTables and adSchemaColumns should be available for all Providers.

Is this not the case?

Thanks,

Dominic

[Dominic Marshall]

Further to this the same error occurs if I try to use the recordset to get the same information

Set myRecordset = New ADODB.Recordset
myRecordset.Open "C:\Temp\cust.xml", "Provider=MSPersist", adOpenUnspecified, adLockUnspecified, adCmdFile
Set TableSchema = myRecordset.ActiveConnection.OpenSchema(adSchemaTables)

Note that is both case I am able to read teh data from the recordset Iam just not able to get the schema information.

Any assistance in resolving this is appreciated.

Thanks,

Dominic

[Dominic Marshall]

Hi,

Just an update on this. I thought I would try this in Access so added the following code to test this:

Set conn = New ADODB.Connection

conn.Provider = "MSDataShape"
conn.Open "Data Source=Test"
Set rs = New ADODB.Recordset
rs.Open shape, conn, adOpenStatic, adCmdText
rs.Save "C:\Temp\ADO\cust.xml", adPersistXML
rs.Close
Set conn2 = New ADODB.Connection
conn2.Open "Provider=MSPersist"
Set rs2 = conn2.Execute("C:\Temp\ADO\cust.xml")
Set TableSchema = conn2.OpenSchema(adSchemaColumns)

This results in the same error code as Excel but gives a much more meaningful message "Run-time error 3251: Current provider does not support schema recordsets".

So even though Microsoft go to the trouble of providing a persisted connection to access files persisted by them which include the schema, you are not able to gain access to the schema.

Very frustrating,

Dominic