Crosstab query on connectionless ADODB.Recordset

[MisterC]

Is there any way to run a crosstab query on a connectionless ADODB.Recordset object in memory?

 

[VBrit]

yes.

You need to use the ADO Datashaping facility.

See <href>

http://support.microsoft.com/default.aspx?scid=kb;en-us;189657</href>

 

[MisterC]

Thanks for your reply, but I don't think that is what I'm looking for.

I have a connectionless ADODB.Recordset object:

Set rs = New ADODB.Recordset
With rs.Fields
    .Append "Store", adVarChar, 4
    .Append "PLU", adDouble
    .Append "Description", adVarChar, 25
    .Append "Units", adDouble
End With
'... then the records are inserted

What I need to be able to do is something like:

SELECT * FROM [blue]rs[/blue] WHERE....

But how do I reference "rs" in my query?

 

[VBrit]

Use the rs.filter function

put a where clause together also, without the where eg:

rs.filter = "customer_name = 'ICI' , country='England'"

I hope this helps!!

 

[MisterC]

Yes, the rs.filter method works fine for the SELECT part of the sql. But how do I implement the TRANSFORM / PIVOT part of a crosstab?

 

[VBrit]

Once you have filtered the recordset, then it is in that state.

You then need to go back to the shaping part of ADO to shape your filtered recordset. This is where you PIVOT and CROSS - TAB the filtered recordset.

You could however use Crystal Reports to pivot and cross-tab very easily.....

Hope I am being helpful,

Martin

 

[MisterC]

My recordset is connectionless; it exists only in memory. There is no database. So with a recordset RS, how can I reference it?

"SELECT * FROM RS" doesn't work.
I don't want to use crystal as I'm just writing to a simple TXT file.

Thanks for your help

 

[VBrit]

You don't need a connection. The shaping facility is provided by ADO, and works on its current resultset.

Imagine a connectionless recordset as an in memory database view.

Hope I can help ya more - if you have any more questions then post a reply matey!