Making Oracle Linked Table Read-Only

[rickj65]

I have an Access 2002 db that has ODBC-linked tables to an Oracle 8i database.

When I link tables that have an index on Oracle, the indexes are mimicked in the Access linked tables. This provides the users the ability to add and update rows to Oracle via the linked table.

How can I remove (or never include) the indexes on the linked table? Or the better question is, how can I set up ODBC-linked tables so that users can only view the data and not update?

TIA,
Rick

 

[dhookom]

Create a pass-through query to the Oracle table rather than a linked table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[rickj65]

I don't have the option of using pass-throughs in this database. The MDB is a supplementary "query" database created primarily for users to create their own ad hoc queries using the Access GUI and linked tables. The concern is that the linked tables musn't provide the users the capability to update tables either inadvertently or intentionally.

The MDB is an upgrade from Acc97 to Acc2002. In 97 the tables were linked, but were not updateable. I'm trying to mimic the exact same set up in 2000.

Thanks,
Rick

 

[willir]

Rick

Perhaps, this is better handled by Oracle security. An end user, as a practice, should never be given an account that allows ODBC access that allows them to update unless the Access front end is designed for this, and then only through forms where you can control and validate the data.

The Oracle DBA should give you an account that allows just read access to the required tables.

Richard

 

[rickj65]

Willir,

You're suggestion is absolutely correct; however, due to time and bureaucratic constraints I'm not in a position to modify the Oracle security accounts for this project.

There are 2 MDBs that access the Oracle database. One MDB is the primary front-end GUI with a fairly sophisticated security process tied in with Oracle security. The other MDB is merely a querying tool for some of the users and it utilizes a modified security similar to the primary MDB. Whoever originally designed the querying tool probably should've implemented a read-only Oracle account, but it's been this way for 10 years now.

Thanks for the suggestion,
Rick

 

[rickj65]

Answer to Issue:

For the benefit of those reading this thread, I found the solution through the Microsoft website.

When ODBC-linked tables have a unique index on the source server database table, Access (Jet) will create it's own local index. The index isn't a true Jet engine index, but is used merely for defining the index locally and allowing linked tables/views to be updatable in Access.

The solution is to delete the index in Access; however, this is not possible through the Access GUI (you will get the error message stating that properties of linked tables cannot be modified). Instead you have to create and run a DDL query that drops the index from the table and execute it in VBA.

For example:

CurrentDb.Execute "DROP INDEX MyIndex ON MyTable;"

There may be another way to accomplish the same task, but this solved the problem for me.

Regards,
Rick

 

[dhookom]

It's not clear why "I don't have the option of using pass-throughs in this database". I would create p-t queries to the Oracle, delete the links to all oracle tables, and then rename the p-t queries to the previous table names.

All query tools should then work nearly the same as the previous linked tables.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[rickj65]

DrHookom,

What I meant by "not having an option of using pass-throughs in this database" was that I'm not in a position to overhaul the system. The application is a mission-critical FDA-adherent database that requires formal SDLC methodologies and extensive testing (Unit, Regression, & SIT) whenever the slightest change is made. My deliverable is this week and a change of the magnitude you suggest would be far too disruptive to the existing structure and literally take months to implement within the organization.

Perhaps I wasn't exactly clear that I wasn't looking for a workaround, I simply needed to figure out how to remove the indexes from linked ODBC tables.

While your suggestion to use pass-throughs will work, it would be less efficient than using linked tables...

http://support.microsoft.com/?kbid=208858

I do appreciate your input.
Thanks,
Rick

 

[dhookom]

I work at a mfg plant that is also under FDA so I now understand your issues.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.