Hello,
I am trying to run SQL directly within MS-Forms (VBA) add-in without using the DataProvider object (e.g. using ADO, etc.)
Is it possible?
Thanks,
Shlomo Eshet 
eshet 
(BOB member since 2002-09-02)
Sure! As long as you make a reference to ADO in your VBA Project. In the VBA Editor goto menu Tools/References and select Microsoft ActiveX Data Objects 2.x Library.
Rosalind Beasley (BOB member since 2002-09-11)
Rosalind… if you have some sample code to post, that would be really helpful. I can get something in a few days if you don’t have anything handy. 
Dave
Dave Rathbun 
(BOB member since 2002-06-06)
I posted some simple code here…
I have worked at many places that update the db from within BO reports to flag which invoices have been printed and the like. I will try and dig that up to post here later…
avaksi 
(BOB member since 2002-08-22)
I am unfortunately several hundred miles away from my PC, but here is a good place to start learning more about ADO. I can post something tommorow, if this isn’t enough help.
The bottom of the page contains a hyperlink to VB code samples related to various methods.
good luck,
Rosalind Beasley
Westbay Solutions Group
Rosalind Beasley (BOB member since 2002-09-11)
I can’t wait…I may have the answers for the 1:00 meeting yet!!! 
Eileen King (BOB member since 2002-07-10)
Here are a few ADO samples below. I made them as basic as possible. Basically there are three examples. 1. Return a recordset, 2.Execute an insert Statement and 3. Execute a StoredProcedure.
Sub OpenRecorsetExample()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Purpose: Retrieve data using the ADO recordset object in Oracle via ADOODBC
'----------------------------------------------------------------------------
Dim rs As New ADODB.Recordset
Dim sConnectString As String
Dim sSQL As String
Dim sDSN As String
Dim sServerName As String
Dim sUID As String
Dim sPWD As String
Dim aData As Variant
sDSN = "DSN_NAME"
sServerName = "SERVER_NAME"
sUID = "USER_NAME"
sPWD = "PASSWORD"
sSQL = "SELECT CUSTOMER_NAME FROM DIM_CUSTOMER"
sConnectString = "Data Source=" & sDSN & ";SERVER=" & sServerName & ";UID=" & sUID & ";PWD=" & sPWD & ";"
'open a recordset
rs.Open sSQL, sConnectString
If Not rs.BOF Then
'Place results in an array
aData = rs.GetRows
End If
'Do Stuff
'cleanup
rs.Close
Set rs = Nothing
End Sub
Sub ExecuteInsertSQLStatementExample()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Purpose: Insert a new record into MSSQL Server table using the ADO command object
'----------------------------------------------------------------------------
Dim cmd As New ADODB.command
Dim sConnectString As String
Dim sSQL As String
Dim sDSN As String
Dim sUID As String
Dim sPWD As String
sDSN = "DSN_NAME"
sUID = "USER_NAME"
sPWD = "PASSWORD"
sConnectString = "Data Source=" & sDSN & ";UID=" & sUID & ";PWD=" & sPWD & ";"
sSQL = "INSERT INTO tblUsers (FirstName,LastName,EmailAddress) VALUES('Rosalind','Beasley','Rosalind@webidesign.com')"
cmd.ActiveConnection = sConnectString
cmd.CommandText = sSQL
cmd.Execute
'cleanup
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
End Sub
Sub ExecuteStoredProcedureExample()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Purpose: Execute a Stored Procedure in Microsoft SQL Server
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim cmd As New ADODB.command
Dim rs As New ADODB.Recordset
Dim sConnectString As String
Dim sDSN As String
Dim sUID As String
Dim sPWD As String
Dim sSPName As String
Dim sSPParamName As String
Dim sSPParamValue As String
sDSN = "DSN_NAME"
sUID = "USER_NAME"
sPWD = "PASSWORD"
sConnectString = "Data Source=" & sDSN & ";UID=" & sUID & ";PWD=" & sPWD & ";"
sSPName = "getSubCategoryList" 'Name of Stored Procedure
sSPParamValue = 1 'Value to pass to Stored Procedure as parameter
sSPParamName = "CategoryID" 'Name of the Parameter in the Stored Procedure
With cmd
.ActiveConnection = sConnectString 'set connetion string
.CommandText = sSPName 'provide the stored procedure's name
.CommandType = adCmdStoredProc 'specifies that the command is a stored procedure
.Parameters.Append cmd.CreateParameter("Return_Value", 3, 4) 'in this case my stored procedrue returns a value
.Parameters.Append cmd.CreateParameter(sSPParamName, 17, 1, 0) 'the store procedure also accepts a parameter as well
.Parameters(sSPParamName) = sSPParamValue 'specific the value that is passed in the parameter
End With
Set rs = cmd.Execute
'cleanup
Set cmd = Nothing
Set rs = Nothing
End SubRosalind Beasley (BOB member since 2002-09-11)
If my stored procedure is returning a table array (multidimensional array) that shouldn’t be a problem, right? I’m just going to populate the cube with the array…
Eileen King (BOB member since 2002-07-10)
No this isn’t a problem. The GetRows() method handles this for you automagically.
The GetRows method takes a Recordset object and converts it into the equivalent multi-dimensioned array.
regards,
Rosalind Beasley
Westbay Solutions Group
Rosalind Beasley (BOB member since 2002-09-11)
I picked up a book last night on ADO – Microsoft ADO.NET Step by Step. Hopefully, this will help fill in a lot of the blank spots!
Thanks so much for all of your help! I’m looking at this from possibilities I didn’t even know existed! I’m so excited! 
Eileen King (BOB member since 2002-07-10)
OK…the book has to go back…we don’t use ADO.NET yet…
I’m finding it fascinating all of the things that I can do with Business Objects this way that I didn’t have a clue to being able to do!!!
Thanks to everyone who helped I’ve now proven that I can indeed make the connection and run an Oracle Stored Procedure through the Visual Basic Procedures. Here’s the code so far…
Public Sub dpVBA_trial(dpInterface As DpVBAInterface)
Dim rs As New ADODB.Recordset 'Create an ADO recordset Object
Dim cmd As New ADODB.Command 'Create an ADO Command
Dim oCn As New ADODB.Connection 'Create and ADO Conection
Dim sConnectString As String 'Create a string to hold the connection information
Dim aData As Variant 'Create a variant variable to recieve the data from getrows()
Dim sSQL As String
Dim sDSN As String
Dim sServerName As String
Dim sUID As String
Dim sPWD As String
Dim sSPName As String
Dim sSPParamName As String
Dim sSPParamValue As String
' Set Connection Variables
sDSN = "{ORACLE ODBC Driver}"
sServerName = "xxxxxprod"
sUID = "eileen"
sPWD = "king"
sDBQ = "xxxxxprod"
sConnectString = "DRIVER=" & sDSN & ";SERVER=" & sServerName & ";UID=" & sUID & ";PWD=" & sPWD & ";DBQ=" & sDBQ & ";"
'Open a Connection
oCn.Open sConnectString
'Set Stored Procedure Variables
sSPName = "elk_test_bo_proc" 'Name of Stored Procedure
sSPParamValue = "abc" 'Value to pass to Stored Procedures as parameter
sSPParamName = "p_count" 'Name of the Parameter in the Stored Procedure
With cmd
.ActiveConnection = sConnectString 'Set Connection String
.CommandText = sSPName 'Provide the Stored Procedure's name
.CommandType = adCmdStoredProc 'Specifies that the command is a Stored Procedure
.Parameters.Append cmd.CreateParameter("p_info", adInteger, adParamOutput) 'SP returns a value
.Parameters.Append cmd.CreateParameter(sSPParamName, adVarChar, adParamInput, 3) 'SP also accepts a parameter
.Parameters(sSPParamName) = sSPParamValue 'Specify the value that is passed in the paramter
End With
Set rs = cmd.Execute
'Cleanup
If rs.State = adStateOpen Then
rs.Close
End If
If oCn.State = adStateOpen Then
oCn.Close
End If
Set cmd = Nothing
Set rs = Nothing
Set oCn = Nothing
End Sub
Here’s the simple Oracle stored procedure…
create or replace procedure elk_test_bo_proc
(p_count in varchar2,
p_info out number)
as
begin
if p_count = 'xyz' then p_info := 123;
else
p_info := 999;
end if;
end;
Next…on to a multidimensional array! 
Thanks again! 
Eileen King (BOB member since 2002-07-10)
Hello Dear Bobsters,
Thank you all for your efforts. I found your answers very helpful and consequently managed to resolve my problem.
Thank you again,
Eshet
eshet (BOB member since 2002-09-02)
Well it looks as if this post and others has helped me understand that their are plenty of other people with far more experience than I out their calling Oracle from BO to run stored procedures. Maybe I should stick to it?
CarlB 
(BOB member since 2002-08-27)
The above code returms some results from oracle database. How can i enter the output of this query to a table in access database. This vb code is written in a access mdb.
Any sample code…??
JaiGupta (BOB member since 2002-09-12)
ADO and BO is great, here is an outline of something that a team I was on used it for.
We had to convert several “other product” reports to BO. The only difference we found with the reports was the where clause had a different inlist criteria, same field, different values. Thr first round of rewrite had us create an object that had a combination or the inlist with prompt, basicly, field inlist() and prompt = “report1” or inlist and prompt = “report2”. The inlist criteria was way to big to have the users select on there own also. The next round was to use the @script function in designer and we coded a form to pop up before refresh. The form had a drop down box that we hardcoded a few report names into, then the code sent the @script an inlist value. This worked awesome. So we then created a small database that held report names and values for the inlist that went with it. We connected the drop down box to the database and also used the database to build the inlist.
So here is how the final worked. User open report, clicks refresh, they get a custom form with a drop down, basicly a custom prompt, they select what report they want to run. VB builds the list of values, sends it to the @script the sql is built and the report runs. 30 reports were combined into one using this method.
Scott
Scott Bowers (BOB member since 2002-09-30)
Would it be possible for you send this code/report/databse to my email id gj2002@indiya.co
JaiGupta (BOB member since 2002-09-12)
I wish I could, I dont work at that place anymore so I dont have the code.
I can try and walk you through creating one like it though.
Scott Bowers (BOB member since 2002-09-30)
Do you have sample code by which I can call query written in a .sql file in Access through VBA and then run that query against a oracle database.
SQl file contains 2 pages of code. I am finding difficulty in making it as a single string.
Any idea
JaiGupta (BOB member since 2002-09-12)
That I have, I will have to find it at home and send get it to you tommorow.
Scott Bowers (BOB member since 2002-09-30)