ODBC Linked Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an access database that has some oracle tables lnked, By default
I do not have select privileges on those oracle tables, I get the all the
privileges after I login thru Access application .

And my access application CALLS AN ORACLE STORED PROCEDURE WHICH ENABLES THE
APPROPRIATE PRIVILEGES for the logged in user for that session.

However When I try to click on any of the combo bax that has the linked
table as the record source I don't see any data. because I think when I
execute the Workspace.OpendataBase method acess connects to oracle with the
connect string specified tries to pull the data from oracle. and since there
are no select privileges available yet it it does not populate any data.

My question is is there a way to refresh the data for those linked tables
after the privileges have been enabled for that session.

Any help will be greatly appeciated

Thanks
 
Hi.
However When I try to click on any of the combo bax that has the linked
table as the record source I don't see any data. because I think when I
execute the Workspace.OpendataBase method acess connects to oracle with the
connect string specified tries to pull the data from oracle. and since there
are no select privileges available yet it it does not populate any data.
Click to expand...

You didn't mention when the combo boxes are populated with data, but if the
combo boxes are populated when the form loads and the connection to the
Oracle tables is made afterward, then you won't see the data from the Oracle
tables in the combo boxes. (Executing the OpenDatabase( ) method does not
pull data from tables or views across the network, so you would have to run
code after executing OpenDatabase( ) method in order to populate the combo
boxes with data.)

Can you can manually open the linked Oracle tables and see the records after
the stored procedure is run to grant the role to the user? If so, then the
combo boxes can be requeried to pull the data across the network. After the
stored procedure is run, try the following syntax:

Me!cboOracleData.Requery

....where cboOracleData is the name of the combo box, and you would requery
each combo box whose record source is an Oracle table or view. You could
execute these lines of code in the VBA procedure immediately after the stored
procedure is run.

If you cannot open the linked Oracle tables and see the records after the
stored procedure is run, then you will need to relink the tables now that the
user has sufficient SELECT privileges on the tables. This requires first
dropping these links, then recreating each link.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top