Results 1 to 7 of 7

Thread: How To Create Dynamic Oracle Query Using Excel VBA And Forms

  1. #1
    Junior Member
    Join Date
    Jun 2013
    Posts
    9
    Rep Power
    0

    How To Create Dynamic Oracle Query Using Excel VBA And Forms

    Presently,I am using the method : Data--> From other Sources --> From Data Connection Wizard --> Others/Advanced--> Microsoft Provider for Oracle to run a Query on Oracle Database and Storing the Data in the Excel Sheet.
    I am having a requirement to Use Forms to Get the Data from the users and want to run the Dynamic Oracle query and want to return the Data in the Form

    Ex: Select * from Employee_Database where Employee = 'Ravi' and Department = 'HR'

    In the above Query, I want to get the Employee name and Department from the users using Forms and want to run a query in Oracle database and return the value in Forms.

    Please let me know if this can be automated completely using VBA , Forms and Dynamic Oracle Queries. If so, Please assist me how to do it as i am new to VBA programming.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    vijaysram, this can be done. Do you a form design in mind? When you say dynamic query, you mean that the where criteria has to be changed using some drop-down value selected by user? Did you try recording a macro when you created the connection, along with the querying?

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535
    https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-01-2024 at 03:02 PM.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Jun 2013
    Posts
    9
    Rep Power
    0
    Thanks for your quick response Excel Fox.

    We have 8 Different Oracle Databases.

    In the Form, It will ask the users to Input 2 Values,

    Name :

    Department :

    Location : Drop down List box (ALL Sites
    London
    Newyork
    Delhi
    ... )


    Submit Cancel

    When i select All sites, it has to search the input data in all the Databases and need to return the value via a Msg box or in a Text box stating " The Employee :Joseph is working in London at HR Department " and has to return a value "Employee is not working in this Location " if not matching the data..

    When i Select Individual Sites, It has to search only the particular database and return the value via a Msg box or in a Text box stating " The Employee :Joseph is working in XXXX at YYY Department " and has to return a value "Employee is not working in this Location " if not matching the data..


    I used Record macro to get the VBA code for connecting the Oracle Database and Got the VBA Code,

    But when i am running the recorded macro, it is giving error.

    i think the below connection string will suit for me,

    Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword

    but my recorded macro is showing different,

    Source = _ oLEDB;Provider=msdaora.1;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword

    Can you please help connecting to the Database thru VBA first, so i can make progress on this work?

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    I do not have an Oracle database to test this, but you should be able to figure this out, and work your way through

    Code:
    Sub GetData()
    
        Dim strSQL As String
        Dim strConnectionString As String
        Set con = New ADODB.Connection
        Set rst = New ADODB.Recordset
        Dim recordCount As Long
        strConnectionString = "Provider=msdaora;Data Source=Oracle_Database_Name;User Id=userId;Password=password"
        
        con.ConnectionString = strConnectionString
        con.Open strConnectionString 'ConnectionString    
        
        strSQL = "Select Employee From MyTable WHERE LOCATION IN ('London','Boston')"
        rst.Open strSQL, con
        'Do what you need here
        rst.Close
    
    End Sub
    You could also try to Connect To An Oracle Database Using VBA
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Jun 2013
    Posts
    9
    Rep Power
    0
    Thanks for your code Excel Fox.

    strSQL = "Select Employee From MyTable WHERE LOCATION IN ('London','Boston')"

    Here, London, Boston etc is not the Location name in MyTable. It will be the database name.

    i.e. Suppose i have a database name London, Boston , newyork etc

    If i am getting the values i.e. Employee name and Employee num from the VBA Form and i click submit

    It should run a dynamic query based on the values entered in the form it should run as below,

    Select Employee_name, Employee_num from London where Employee_num = ' ?? '

    If the Employee_num = vba form value then it should say Employee is available in London if not it should say Employee is not available in London

    ... The above needs to run for rest of all the databases using different datasources and need to collectively say" The Employee num is available London, Newyork"

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    All you need to do is then run a loop for each location, and store each result (whatever is available or not) in a variable, probably an array variable, and depending on where the employee is found, display a message or show an output to the user accordingly.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    I answered this in one of your cross-posts. You simply need to use concatenation to build your SQL string. As I had posted before:

    Code:
    Dim s as string
    s = "Select * from Employee_Database where Employee = '" & TextBox1.Value & "' and Department = '" & TextBox2.value & "'"
    MsgBox s
    Of course the actual code with the build SQL string would typically be in a UserForm's Command Button Click event where TextBox1 and TextBox2 controls contain the values needed.

    Besides the ADO method that ExcelFox showed, a similar QueryTables can be used:
    Creating Dynamic Reports with Query Tables in Excel

Similar Threads

  1. Excel Dynamic Waterfall Chart
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 10-08-2015, 05:33 PM
  2. Creating Pivots in Excel VBA and Create an Email
    By aaron.mendes in forum Excel Help
    Replies: 24
    Last Post: 07-26-2013, 09:29 PM
  3. Create A Dynamic Border Using Excel Native Formula
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-02-2013, 06:07 AM
  4. Automatically Create Multiple Dynamic Named Ranges Using VBA
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 04-24-2013, 03:49 PM
  5. Dynamic Chart Query
    By leopaulc in forum Excel Help
    Replies: 6
    Last Post: 11-26-2012, 04:50 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •