From my previous workplace, I'm used to using MS-Access for lightweight browsing and manipulation of data in the Db I'm working with.
In my new workplace, it's an Oracle 10 back-end, and I can use the ODBC drivers to link the tables in Access just fine. The problem arises when I try to update any data. Many of the tables I'm working with have \"BeforeUpdate\" triggers on them, so if I make a change to a row in one of these linked tables through Access, it (correctly!) reports that \"the data has changed by someone else\" before my update is applied, and won't allow the change.
I've done a little research online, but all the workarounds I've found are related to TimeStamp fields, which is not the problem in my case.
Any suggestions?
Access, ODBC linked tables, and Db triggers
Why not bite the bullet, start a SQL*Plus session and execute a proper SQL Update query to accomplish what you are trying to do? That way Oracle can can execute its triggers and establish the row locks that are necessary for the updates while maintaining the database integrity.
Most of the DBAs and application programmers I know would be appalled by the notion of someone trying to update a production Oracle database in an ad hoc fashion through MS Access. That's just Pure Evil.
Most of the DBAs and application programmers I know would be appalled by the notion of someone trying to update a production Oracle database in an ad hoc fashion through MS Access. That's just Pure Evil.
I've found sqlplus to be best for certain admin tasks (especially fixing of tablespace issues, breaking and fixing jobs, etc), but if I'm wanting to manipulate data or data-mine, the formating and restrictive nature of sqlplus and vi as your editor are powerful in some ways but very restrictive in others, infinitely moreso on Solaris systems than Linux (vim ftw).
For simple manipulation of data or plsql development, I actually end up resorting to Aqua Data Studio. It's free and runs on all the mainstream operating systems (I use it at work in Ubuntu, for example). It's also very quick and easy for formatting of the data, exporting it to csv or excel sheets as needed so others can see your results, and it's very customizeable as far as how it interacts with Oracle. Aqua also supports multipe database formats as well from the same GUI, so you can map multiple connections to various databases such as several flavors of Oracle, MS SQL, and Sybase all from the same interface. I suspect one of your reasons for continuing to use Access is because you can directly update fields in the data displayed and commit your changes instead of relying on sql queries to make the changes for you, and Aqua allows you to do that. Depending on how much data you're needing to alter, though, I'd recommend getting comfortable with using queries to make bulk changes so that your workload is streamlined. Just don't practice on the production database. ;p
http://www.aquafold.com/
I was using TOAD for a long time, but having to download a new copy every couple of months once it expired was getting annoying, and I was having other issues with it after a while that were all resolved once I switched to Aqua.
As an aside, I can't remember if Aqua's mechanics will help you circumvent BeforeUpdate triggers. It may be that you have to resort to using queries for your updates so that the snapshot they apply their changes to is not stale. If you need help with learning the plsql equivalents of what you're attempting to do, I can help a bit here and there. I'd have to agree with akula65 on the Bad Mojo of what you're attempting. In a limited capacity with specific configuration tables that don't have constraints against larger parts of the schema, it's ok, but still frowned upon.
For simple manipulation of data or plsql development, I actually end up resorting to Aqua Data Studio. It's free and runs on all the mainstream operating systems (I use it at work in Ubuntu, for example). It's also very quick and easy for formatting of the data, exporting it to csv or excel sheets as needed so others can see your results, and it's very customizeable as far as how it interacts with Oracle. Aqua also supports multipe database formats as well from the same GUI, so you can map multiple connections to various databases such as several flavors of Oracle, MS SQL, and Sybase all from the same interface. I suspect one of your reasons for continuing to use Access is because you can directly update fields in the data displayed and commit your changes instead of relying on sql queries to make the changes for you, and Aqua allows you to do that. Depending on how much data you're needing to alter, though, I'd recommend getting comfortable with using queries to make bulk changes so that your workload is streamlined. Just don't practice on the production database. ;p
http://www.aquafold.com/
I was using TOAD for a long time, but having to download a new copy every couple of months once it expired was getting annoying, and I was having other issues with it after a while that were all resolved once I switched to Aqua.
As an aside, I can't remember if Aqua's mechanics will help you circumvent BeforeUpdate triggers. It may be that you have to resort to using queries for your updates so that the snapshot they apply their changes to is not stale. If you need help with learning the plsql equivalents of what you're attempting to do, I can help a bit here and there. I'd have to agree with akula65 on the Bad Mojo of what you're attempting. In a limited capacity with specific configuration tables that don't have constraints against larger parts of the schema, it's ok, but still frowned upon.