Acess 2000 - calling oracle user-defined function

[sunil707]

Hello All - This is my first time to this forum.

I am using Access 2000. Backend is Oracle V10g thru ODBC connection.

I have a function (written by someone else) in oracle there I supply three
values and it returns me a date.

How do I access that function from Access and get the desired date? Can it be
done in a query? I am not familiar w/ VB code etc. I use access only to
create simple query/form/design etc..

If someone help me step-by-step, would be a great help.

Thanx a ton in advance...

Sunil

 

[Stefan Hoffmann]

hi,

How do I access that function from Access and get the desired date? Can it be
done in a query? I am not familiar w/ VB code etc. I use access only to
create simple query/form/design etc..
If someone help me step-by-step, would be a great help.

1. Using ADO:

Use ADO.Connection and ADO.Command, e.g. like here

http://support.microsoft.com/kb/185125

2. Using DAO:
You need to create a passthrough query. Use VBA to modify the SQL statement.

Dim qd As DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("passthroughQuery")
qd.Statement = "SELECT function(parameters) FROM dual"

You need to assemble the string by your self or use parameters.


mfG
--> stefan <--

 

[sunil707]

Hi Stefan - I sincerely appreciate you efforts to help me. But The code looks
Greek to me as I mentioned earlier that I have no clue how VB works. Thats
the reason I requested for step-by-step approach. May be I am asking too much.
but I have no alternate as I have never worked in any VB code etc...

Once again thanks for your inputs...

 

[Stefan Hoffmann]

hi,

Hi Stefan - I sincerely appreciate you efforts to help me. But The code looks
Greek to me as I mentioned earlier that I have no clue how VB works. Thats
the reason I requested for step-by-step approach. May be I am asking too much.
but I have no alternate as I have never worked in any VB code etc...

Once again thanks for your inputs...

http://support.microsoft.com/kb/181734
http://support.microsoft.com/kb/303968
http://www.techonthenet.com/access/tutorials/passthrough/basics01.php
http://msdn.microsoft.com/en-us/library/dd638587.aspx

You need also to learn to write PL/SQL, the Oracle SQL Dialect.

mfG
--> stefan <--