Solved

Convert SQL script to Excel VBA module

Posted on 2008-06-19
2
1,415 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 500 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

632 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