(note: most of the users at work have only Excel 97 - so I'm stuck with that level of VBA as a tool).

At work we have a new system driven by a massive Oracle db. All day long reports are created. This has been setup to where a user runs an Oracle sql file in SQL*Plus. The reports spool into a text file. Once the reports are done, I have built several Excel VBA projects to read the text files, format (make pretty) the data, sort it and create summary information sheets (VBA pivot table commands, etc).

I want to sidestep the need for SQL*Plus and the .sql files by having a set of Excel VBA 'macros' that bring up a user form and pull data directly from the Oracle db. This would save a lot of steps, and speed things up as well as giving the user more flexibility. Imagine non-tech people having to be taught how to open SQL*Plus, run the right .sql file and then run a Excel macro after the spool file is finished. (question and problem nightmare).

So far I've read up on how to connect to the Oracle db using ADO. I've already made some VBA code and tested the Oracle db connection - it works fine.
(OpenConnString = "Provider=MSDAORA.1;Password=mypassowrd;Persist Security Info=True;User ID=bubba;Data Source=Mydb")

I'm going to hit a big learning curve tomorrow as the Oracle sql works a lot different than anything I've seen in VB.


-Does MS ADO support the column.table method? If so, is the formatting the same (or how?)
Select mt1.mycolumn, mt2.mycolumn3
From mytable mt1, mytable2 mt2
Where mt.mycoulmn1 = "bubba", and mt2.mycolumn3 "fred"

-Does the MS ADO support any of the Oracle commands or functions (like '=NUL')

-Considering all the funky formatting characters you have to put around an SQL statement in VB, and all the formatting crap I have to get rid of from the Oracle SQL (the spool stuff, like: RPAD, Name of Header, Length of Header) --- is there anything that can parse an Oracle SQL 'script' into VB format (or make it easier)?


With VB I'm use to making a 'virtual table' and pulling it's data out one record at a time. With Oracle sql I'm use to doing everything in one step (several select/from/where/union). My brain is hurting going from one style to the next and for all I know I can do it the same whay in VB.

Thanks for any help or any tips, or links to examples. I'll take anything.