Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Run queries with a button (Read 6816 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Run queries with a button
01.11.05 at 14:47:55
 
Jorma from Finland asked the following question:
 
Question:
I have two queries (master-detail) that I want to run. Both queries take values from the worksheet in a form that I will create. At the bottom of the form I want to create a button that will run both select statements. I want the first statement to be transposed; it will return only a single row. This is my master record. I want the results of the second statement to be updatable. The results of the second query are the detail records.
 
Answer:
This is very easy to do. The results from the last select statement you execute are always updatable so you don't need to do anything extra for that.
You need to do a few things to get this implemented:

  • Create a reference to SQL*XL.  
    When you use the macro recorder this will automatically be done for you. Otherwise read this topic to see how you can manually create the reference to SQL*XL:
    http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130851761
  • Create a macro / subroutine to run the query. I have recorded a sample select statement and tweaked the recorded code a little to give:
    Code:
    Sub RunQuery(SQLText As String, StartFrom As String, Transpose As Boolean)
      SQLXL.Sql.setText SQLText
      Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
      SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
      With Targets(litExcel)
        .AutoFilter = False
        .AutoFit = True
        .Headings = True
        .Sort = False
        .StartFromCell = StartFrom
        .Transpose = Transpose
        .SQLInNote = False
        .FormatData = True
        .FreezePanes = False
        .PasteInsert = False
      End With
      With SQLXL.Sql.Statements(1)
        .ShowParametersDlg = False
        .ShowResultsetDlg = False
      End With
      SQLXL.Sql.Statements(1).Execute
    End Sub
     
    
    

    This code will take a select statement, the target cell and a parameter to tell SQL*XL whether to put the data normal or transposed.
  • Now you need another small macro / subroutine that calls the RunQuery subroutine once for each statement. For example:
    Code:
    Public Sub RunStatements
        RunQuery "select * from mytable where colX=" & Range("A10").Value, "F12", True
        RunQuery "select * from myothertable where colY=" & Range("D4").Value", "F20", False
    End Sub
     
    
    

  • Finally you need a button to call this macro.
    In Excel, go to View | Toolbars | Visual Basic
    Now press the Control Toolbox button.  
    Select the command button icon from the panel and draw a button on your worksheet.
    Right click the button and choose properties. Change the properties as required.
    Now double click the button and Excel will create a macro for you that runs when the button is clicked at run time. Add a call to your RunStatement routine:
    Code:
    Private Sub CommandButton1_Click()
      RunStatements
    End Sub
     
    
    

    In the Control Toolbox, click the Exit Design Mode button and remove the Toolbox and the VIsual Basic toolbar.

This is it! You may want to add some code that also connects to the database and disconnects when you are done. Finally you may want to clear the results area before you run your statements again.
Back to top
 
« Last Edit: 02.11.05 at 09:14:58 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Run queries with a button
Reply #1 - 26.01.10 at 13:02:25
 
How to: Run queries with a button
 
Quote:
How do I use the subroutine if I want to get the SQL from the spreadsheet (ie. range B5:B12)

I would like to:

1. Connect to the database.
2. Run a number of queries (which are written in a number of cells, ie. B5:B12)
3. Return the results to cell B30
4. Then disconnect from the database.


 
Add a reference to SQL*XL
 
As a prerequisite you need to add a reference to SQL*XL. If you have recorded a macro SQL*XL will automatically add the reference to your workbook. If you want to do it manually please refer to this topic:
 
Manually adding a reference to SQLXL
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130851761
 
How the macro recorder helps coding SQL*XL VBA
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133075706
 
If the macro recorder does not record any SQL*XL actions, please switch on the macro recording support in the SQL*XL program preferences.  
 
Connect to the database
 
Another prerequisite to the RunQueries routine is that you have established a connection to your database. The best way to do this is to record a macro when connecting to the database. This will ensure all the technically difficult settings (in the connection string) are correctly set. You can program it manually if required. To get an idea how such a macro looks please refer to this topic:
 
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1213471017
 
Use SQL from spreadsheet cells
 
You indicate you would like to use SQL that is stored on the spreadsheet. There are two ways to do this. The first is to use the RunQuery routine as it is and pass in the SQL. Your code would look something like this:
 
Code:
Dim rng as Range
Dim strSQL as String

For each rng in Range("B5:B12").Cells
  strSQL = strSQL & rng.Value & " " 
Next rng
RunQuery strSQL, "B20", False 


 
The second way to do it is to use the setTextFromWorkSheet method as shown below. I have recorded a macro connecting to a SQL Server database, running a query and then closing the connection. I have replaced the SQLXL.SQL.setText command with the setTextFromWorksheet command. That was the only edit.
 
Code:
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 26/01/2010 by gjlinker
'

'
    SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litSQLXLADO
    SQLXL.Database.Connect UserName:="Admin", PassWord:="", DBAlias:="BIBLIO.MDB (\\test databases\access97\BIBLIO.MDB)", ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=\\test databases\access97\BIBLIO.MDB;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False", AllowTransactions:=True
    SQLXL.Sql.setTextFromWorkSheet SelectedCells:=Range("C5:C7"), OneStatement:=True
    Set SQLXL.Sql.Statements(1).Target = SQLXL.Targets(litExcel)
    SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
    With SQLXL.Targets(litExcel)
	  .AutoFilter = False
	  .AutoFit = True
	  .Headings = True
	  .Sort = False
	  .StartFromCell = "$C$9"
	  .Transpose = False
	  .SQLInNote = True
	  .ShowNote = True
	  .FormatData = True
	  .FreezePanes = False
	  .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(1)
	  .ShowParametersDlg = False
	  .ShowResultsetDlg = False
    End With
    SQLXL.Sql.Statements(1).Execute
    SQLXL.Database.DisConnect
End Sub
 

Back to top
 
« Last Edit: 26.01.10 at 13:42:30 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Run queries with a button
Reply #2 - 27.01.10 at 10:08:29
 
Quote:
Thanks very much for your quick response – much appreciated.

One small problem though which should be easy to solve but I don’t know what I am doing wrong…

I’m pressing the button on a different sheet to the one which contains the queries. When I run the code…

For Each rng In Range("k5:k28").Cells
strSQL = strSQL & rng.Value & " "
Next rng

The range is picking up the wrong sheet, ie. not the query.

I’ve used Sheets("abc").Activate

At the start of my macro, so the correct sheet is active, BUT it is still picking up the data on the other sheet. I’m puzzled. Do you know why ?


 
I think you have to select the workksheet first and then activate it (or activate and select I cannot remember):
Sheets("abc").Select
Sheets("abc").Activate
 
Use this instead to avoid the problem
 
Range("abc!B5:B12")
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1