D
debbieirch22
Guest
Currently I have an Access 2000 report that makes a call to a view in an Oracle 8i database. The code that I have written to call the view is working fine. Here's the problem:
The view is a set of select statements with unions that retrieves data from 14 different connections to 14 remote Oracle 8i databases. When one of those connections is down, the view fails and thus the Access report fails. I need to solve that problem.
Here is what I have done so far:
I created an Oracle stored procedure that checks the links to the remote databases and dynamically creates a SQL statement to create the view, based on which links are active. I cannot run the 'CREATE VIEW AS...' statement from the stored procedure because stored procedures only have public access. What I thought that I would do is call the procedure that creates the 'CREATE VIEW' statement from a script in Access, and retrive the statement as a variable in VBA. Then I could run that statement from the VBA before the call to the view is made.
Am I on the right track here? And does anyone know how to call an Oracle stored procedure from VBA and retrieve the return parameter as a variable in the VBA script? (This is really the information that I am looking for.)
Thanks -Debbie
The view is a set of select statements with unions that retrieves data from 14 different connections to 14 remote Oracle 8i databases. When one of those connections is down, the view fails and thus the Access report fails. I need to solve that problem.
Here is what I have done so far:
I created an Oracle stored procedure that checks the links to the remote databases and dynamically creates a SQL statement to create the view, based on which links are active. I cannot run the 'CREATE VIEW AS...' statement from the stored procedure because stored procedures only have public access. What I thought that I would do is call the procedure that creates the 'CREATE VIEW' statement from a script in Access, and retrive the statement as a variable in VBA. Then I could run that statement from the VBA before the call to the view is made.
Am I on the right track here? And does anyone know how to call an Oracle stored procedure from VBA and retrieve the return parameter as a variable in the VBA script? (This is really the information that I am looking for.)
Thanks -Debbie