Opening ADO recordset from XML created by VB.NET

[BFarley]

I have an XML file that was created by a VB.NET routine.

When I try to open it as an ADO recordset in VBA, I get the following error:

(3709) The connection cannot be used to perform this operation. It is either closed or invalid in this context.

Code I'm using to attempt opening the XML as recordset:

   Dim rst As New ADODB.Recordset
  
   rst.Open ("C:\vendors.xml")

Is this happening because the XML file was created in something other than ADO?

If this is more of a VB.NET question than VBA, please let me know

Thanks in advance!

 

[Chance1234]

I think you may be getting ado.net and ado mixed up. I dont think you can query xml with ADO

have you looked at using xpath ? some vbscript here that you should be able to port over to vba

http://www.eggheadcafe.com/articles/20030627d.asp

Chance,

F, G + yeah reached 50

 

[BFarley]

I created the file in VB.NET, using ADO.NET, and now trying to open it from Access using VBA.

Is that a little more clear?

 

[Golom]

Usually you need to specify

rst.Open "C:\vendors.xml", , , , 256

To indicate that the source for the recordset is a file.
256 corresponds to the adCmdFile constant.

 

[BFarley]

New error when I try it that way:

-2147467259 
Recordset cannot be created. Source XML is incomplete or invalid.

Also occurs when I use this:

    Dim strConnect As String
    strConnect = "Provider=MSPersist"
       
    Dim rst As New ADODB.Recordset
    rst.Open "C:\vendor.xml", strConnect, , , 256

From my initial research, it seems as though unless the data is originally persisted using ADODB recordset, then it cannot be directly opened in an ADODB recordset.

 

[Golom]

I suspect that you're correct. The XML that persisting a recordset in XML produces looks something like this

<s:Schema id='RowsetSchema'>
	<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
		<s:AttributeType name='DEPT' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='DEPT'
			 rs:basecolumn='DEPT' rs:keycolumn='true'>
			<s:datatype dt:type='string' dt:maxLength='10'/>
		</s:AttributeType>
[blue] ... and on and on and on ...[/blue]

Looks pretty specific to establishing all the parameters needed to define the ADO recordset. Just any old XML won't necessarily have the information needed to properly define the recordset parameters.