Help needed for creating Dynamic Oracle Query using Excel VBA and Forms

[vijaysram]

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.

[Kenneth Hobs]

Obviously, you would build the SQL string from a TextBox control normally.
e.g.

Dim s as string
s = "Select * from Employee_Database where Employee = '" & TextBox1.Value & "' and Department = '" & TextBox2.Value" & "'"
and then use that in the query. Try recording what you want in an QueryTable and then modify it. Otherwise, an ADO method might work for you. See some of my recent posts for ADO examples using MySQL or see these ADO examples: http://www.erlandsendata.no/english/index.php?t=envbadac

The main part you will need if you don't use the recorded macro QueryTable method is the connection string. For that, see: http://www.connectionstrings.com/oracle/

[vijaysram]

Thank you Kenneth for your quick response.

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.

From your link, i think the below connection string will suit,

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?

[Kenneth Hobs]

From the link that I provided for the connection strings, and your recorded macro, this connection string might work.

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;
Password=myPassword;

See this thread for an example. http://www.vbaexpress.com/forum/showthread.php?40986-Solved-Connect-to-Oracle-Database-using-Excel-VBA

[p45cal]

Cross posted at:
http://www.excelguru.ca/forums/showthread.php?2030-Help-needed-for-creating-Dynamic-Oracle-Query-using-Excel-VBA-and-Forms
and
http://www.mrexcel.com/forum/excel-questions/720215-help-needed-creating-dynamic-oracle-query-using-excel-visual-basic-applications-forms.html

[Bob Phillips]

I would suggest that you create a parameterised stored procedure in the database, and then pass those form values to the SP call.

[vijaysram]

From the link that I provided for the connection strings, and your recorded macro, this connection string might work.

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;
Password=myPassword;

See this thread for an example.

Yes, I am now able to connect My Database.. I am now having the requirement to run a dynamic query.

Ex : In my database , if i am running a query : Select * from Employee_database ; there is a Field and it is returning some value A = 200 or some A = some numeric value.

I now need to get the input from the VBA form text box and need to run the dynamic oracle query

i.e. Suppose if i am entering text box value =100 it has to run the query : Select Employee_name, Value from Employee_database where value = 'A= ???'

Please advise further...

[vijaysram]

Can you let me know how to do it ? I don't find the parameter option while i am pulling the data from my database manually... I am using MSExcel 2010

[Kenneth Hobs]

Without code, or the code in an example workbook, I don't know how we can help.

I don't know what you mean by parameter. There are parameter queries in the database and ADO object and parameters for VBA routines.

ADO would be the best route I suspect. The example links that I gave should suffice.