Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert SQL script to Excel VBA module

Posted on 2008-06-19
2
Medium Priority
?
1,447 Views
Last Modified: 2013-12-19
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?
0
Comment
Question by:Glenn Stearns
2 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 21828572
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
 

Expert Comment

by:kismokus13
ID: 21902167
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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question