using vba/access to call a function in an Oracle db

[ahlersmj]

Here's the situation:
There is a function (FN1) in an Oracle database (ODB1) which accepts a Customer ID (CustID) and returns an associated dollar figure (GR1).

I have an Access database (ADB1) that has a table (TCust) that has around 100 records. Amoung the fields are CustID and a null field for GR1.

What would you reccommend for calling the function form an Access Update Query or from a VBA Module to populate the field?

Thanks in advance for any/all suggestions.

--Michael

 

[Pat Hartman]

I don't think you'll be able to do this with an update query because you'll need to use a pass-through query to execute the Oracle function and joining to a non-updateable query will make your update query non-updateable. I would create a DAO or ADO code loop to read the pass-through query and and then using the .Find and .update methods, update the local table.
PS - this probably isn't a good idea because as soon as you update the local table, the Oracle table could change and so your data is out of date. It is best to just show this value on a form or report as needed.

 

[mdlueck]

Like Pat suggested, you need to execute the "FN1" function via Pass-Through query. I would suggest an ADO type connection, an ADO.Command if possible, otherwise ADO.RecordSet. I have an example of such in this thread:

Using VBA ADO objects to execute a Stored Procedure
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

The other technique which might assist you would be to download the record from the Oracle BE DB to an Access FE temp table, so you may join its value with values from other FE tables. I have an example here of how that works:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

That was back when I was sending up the SQL query to the BE DB. Now I execute Stored Procedures for the pass-through query, so the syntax now looks like:

    strSQLbe = "SET NOCOUNT ON; " & _
               "DECLARE @projectid smallint; " & _
               "SET @projectid = " & ObjProjectsTbl.id & "; " & _
               "EXEC dbo.clsObjProductsTbl_RefreshLocalTmpTbl_All @projectid;"

P.S. The main difference is where you need the record which is downloaded from the BE DB.

1) If you need it in VBA variables, then use the ADO method.
2) If you need it in an Access table, then use the DAO method.

 

[ahlersmj]

Thanks Pat! Thanks Michael!

This helps a lot.