Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert SQL script to Excel VBA module

Posted on 2008-06-19
2
Medium Priority
?
1,434 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

670 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