ADODB Recordsets

Hello all,

I am in need of some data crunching capabilities and don't know whether there is an easy way to achieve this.

I need to perform SQL queries on some data I load into excel. Taking this in mind, I loaded the data into a ADODB recordset however since it is a disconnected recordset, I am unable to perform any SQL queries on it

Once I have imported the data and got the info I need, i'll be needing to insert into an access database.

I know recordsets have filter and sort capabilities, but I need a little more complex SQL functionality.

I am not talking massive amounts of data; depends on what you call massive I suppose. I'll be importing ~500 records and then exporting ~100.

Anyone have any ideas?

Thanks

Originally Posted by romperstomper

IMO, your question is too vague to give you any suggestions. What exactly do you want ideas for or help with?

Hi, romperstomper

What i'm after is a way to be able to run an SQL query on a disconnected recordset ie to extract data and to transfer the data I want into an access database

or

another way to manipulate temporary data on a local machine using SQL before uploading it.

Thanks

Originally Posted by romperstomper

Is there a reason for not performing your SQL queries on the data while it is in a worksheet?

The main reason for not doing this is that the information is coming from an xml file and I wanted it to go straight into a recordset with as little intermediate steps as possible as it easy to do and less to go wrong or so I thought. I would have thought microsoft would have made this easier but I guess not.

Another reason for deciding against this was that I couldn't find much information on how to do this - I would like to know more as it will come in useful later on in another project but as I say could not find too much info when I wanted it.

Originally Posted by romperstomper

Everything you ever wanted to know about ADO and Excel here.

What sort of manipulation are you attempting on the recordset? (you cannot perform SQL queries on a recordset)

Basically I am doing some work with a bill of materials which involves importing an xml file and then finding differences between BOM revisions, summation of quantities etc...

I'm going to show my ignorace here, but what is the point of a recordset when all you can do is filter and sort? Wouldn't it be better to be able to maipulate data via SQL as more often than not it's SQL which populates a recordset in the firstplace.

Thanks for the info BTW.

Originally Posted by romperstomper

That's precisely the point - you use SQL to get exactly the data you want into a recordset, not to manipulate a recordset after you have created it.

ummm, this gets me thinking...what do I do when I have a situation where I need to get the data as I am; I cannot get it straight from the xml file so what do I put it into to be able to get the right data.

I think what i'm getting at is what can I run a sql query on to get the data when it is held in a xml file - AFAIK I cannot run queries on the xml file itself

Originally Posted by romperstomper

Depending on the XML you may be able to open or load it directly into an Excel file and then run queries on that?

I really wish it was as simple as that...I've tried everything I can think of to read directly from the xml file, the trouble is that the format doesn't seem readable by anything VBA has to offer...

Here's an example of the format:
<?xml version="1.0" ?>
- <Catalog xmlns:dt="urn:schemas-microsoft-com:datatypes">
- <Rec>
<ITEM dt:dt="string" />
<QTY dt:dt="string">1</QTY>
<SUB dt:dt="string">1</SUB>
<CATALOG dt:dt="string">855E-24TL3</CATALOG>
<MFG dt:dt="string">AB</MFG>
<ASSYCODE dt:dt="string" />
<DESC dt:dt="string">GREEN LED BEACON</DESC>
<QUERY2 dt:dt="string">BEACON</QUERY2>
<QUERY3 dt:dt="string">24V AC/DC</QUERY3>
<MISC1 dt:dt="string" />
<MISC2 dt:dt="string" />
<USER1 dt:dt="string">GE00010</USER1>
<USER2 dt:dt="string" />
<USER3 dt:dt="string" />
<TABNAM dt:dt="string">BE</TABNAM>
<TAGS dt:dt="string">P385</TAGS>
<DESC1 dt:dt="string" />
<DESC2 dt:dt="string" />
<DESC3 dt:dt="string" />
<INST dt:dt="string">50-031</INST>
<LOC dt:dt="string">FW1-POD</LOC>
<HDL dt:dt="string">h=EA0</HDL>
<DWGIX dt:dt="string">156</DWGIX>
</Rec>
</Catalog>