Solved

Call to an Oracle stored procedure from Access 97

Posted on 1998-03-05
4
181 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 150 total points
Comment Utility
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
Comment Utility
Could you please give me an example code?
Thanks!
0
 
LVL 1

Expert Comment

by:Carmy
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now