How To Create Dynamic Oracle Query Using Excel VBA And Forms

**vijaysram** · 08-14-2013, 09:37 PM

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.

**Excel Fox** · 08-14-2013, 11:04 PM

**vijaysram** · 08-15-2013, 07:20 AM

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?

**Excel Fox** · 08-15-2013, 12:28 PM

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

**vijaysram** · 08-19-2013, 09:02 PM

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"

**Excel Fox** · 08-19-2013, 10:10 PM

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.

**Kenneth Hobson** · 08-23-2013, 09:33 PM

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