Convert SQL script to Excel VBA module

What is the general method to incorporate an SQL script (.sql file) into an Excel VBA module? At this point, I have to run the SQL script using SQL*XL to get the data into an Excel worksheet. Then I run an Excel VBA script to get the end result. Both these actions work well independently; however, I want a single VBA module that includes the SQL script and the existing VBA script so it all runs as one module. I can make the connection to the Oracle database independently so I don't need that part. Once my connection is established, though, I want to run the combined SQL/VBA module to do what the SQL script and the VBA code now do independently. How should I go about doing this?
Glenn StearnsAnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
If the script does not return a recordset then this should work:

   Dim MyDatabase As ADODB.Connection
   Dim FilePath As String
   Dim FileNumber As Long
   Dim FileData As String

   FilePath = "C:\Full\Path\To\File.sql"
   FileNumber = FreeFile
   Open FilePath For Binary Access Read As FileNumber
   ' Use following if non-unicode data
   FileData = StrConv(InputB(LOF(FileNumber), FileNumber), vbUnicode)
   ' Use following if unicode data
   'FileData = InputB(LOF(FileNumber), FileNumber)
   Close FileNumber
   
   ' Open database connection
   Set MyDatabase = New ADODB.Connection
   MyDatabase.CursorLocation = adUseClient
   MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
   ' For more information about Open syntax:
   '   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
   '   and navigate to Open Method (ADO Connection)
   '   Additional help constructing connection strings can be found at http://www.connectionstrings.com/

   ' Execute any SQL statement or script
   MyDatabase.Execute FileData

If it does return a recordset then:

   Dim MyDatabase As ADODB.Connection
   Dim MyRecordset As ADODB.RecordSet
   Dim FilePath As String
   Dim FileNumber As Long
   Dim FileData As String

   FilePath = "C:\Full\Path\To\File.sql"
   FileNumber = FreeFile
   Open FilePath For Binary Access Read As FileNumber
   ' Use following if non-unicode data
   FileData = StrConv(InputB(LOF(FileNumber), FileNumber), vbUnicode)
   ' Use following if unicode data
   'FileData = InputB(LOF(FileNumber), FileNumber)
   Close FileNumber
   
   ' Open database connection
   Set MyDatabase = New ADODB.Connection
   MyDatabase.CursorLocation = adUseClient
   MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
   ' For more information about Open syntax:
   '   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
   '   and navigate to Open Method (ADO Connection)
   '   Additional help constructing connection strings can be found at http://www.connectionstrings.com/

   ' Execute any SQL statement or script that returns a recordset
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open FileData, MyDatabase, adOpenDynamic, adLockPessimistic

The above code assume the .sql file is just a text file containing the script.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kismokus13Commented:
Ok, here is a twist in teh problem,
suppose my sql script has a create #tmp statement in it first, and then the result is returned from querying that. For some reason the above solution does not work then.

If i read teh file and break it into 2 statements, one that creates the #tmp table and I call cmd.Execute with that, then I run your solution for the second part, that works...

IS there a more elegant way of doing this?

Thanks,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.