?
Solved

Call to an Oracle stored procedure from Access 97

Posted on 1998-03-05
4
Medium Priority
?
196 Views
Last Modified: 2012-06-21
Is it possible to call (execute) an Oracle stored procedure from Access 97?

If so, could you please explain the whole process..

0
Comment
Question by:nesho
  • 2
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
Carmy earned 300 total points
ID: 1970123
Use Pass-truogh Query (use ODBC).
Your Query should look like that:
"SELECT <myprocedure> FROM DUAL"
This will execute the function and return any value from your procedure.
0
 

Author Comment

by:nesho
ID: 1970124
Could you please give me an example code?
Thanks!
0
 
LVL 1

Expert Comment

by:Carmy
ID: 1970125
This function will return the ORACLE System Date (in-built stored procedure)
As I have no idea what your function is doing I used this example.  Replace 'SYSDATE' with your function.

For more indepth details - search Microsoft knowledge base, use keywords Pass-trough and Oracle.

Function GetOracleDate () as String
   
    Dim MyDB As Database
    Dim MyQuery As QueryDef
    Dim MySet as RecordSet
    Dim SourceConnectString As String
    Dim strSQL As String

    Set MyDB = CurrentDB()
    'Create a temporary query in memory only
    Set MyQuery = MyDB.CreateQueryDef ("")

    strSQL = "SELECT SYSDATE FROM DUAL"
    SourceConnectString = "ODBC;"
    MyQuery.Connect = SourceConnectString
    MyQuery.ReturnsRecords = True
    MyQuery.SQL = strSQL
    'Open the Recordset to return the Date
    Set MySet = MyQuery.OpenRecordSet()
    'Return the system date
    GetOracleDate = MySet!SYSDATE

End function

0
 

Author Comment

by:nesho
ID: 1970126
Have you ever got it working with your own function instead of built-in and how about using parameters with function?

Anything special I should do in case I want to pass a parameter to my function?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question