I am using MS Access 2003 as a front end to 2 Oracle databases. I am trying to retrieve a recordset from a database through ADO/DAO, display that recordset in a Form for users to make changes, and then write the records that have been changed to a different database (i.e. I don't need to make changes to the original recordset). I am happy with retrieving the initial recordset, and inserting/updating records in the different database, but the middle part is causing me lots of pain.
Basically, I have an unbound form, which has a search button on it. When the user clicks it, they enter a value in an InputBox, and then I have used this value to fetch data into a recordset from a read only Oracle database connection. I then bind the form to this recordset, and the user sees the records in the form. No problems yet.
When I try to change 1 or 2 of the values in form view, it brings back error messages, or won't allow me to update the field/s (dependant on which field I change first), and I can't get past this. I have tried to use a Disconnected recordset, as well as various combinations of cursor location, cursor types and locktypes (although doesn't really make much difference to a read only connection), but nothing seems to work.
I am trying not to have to use a temporary database and/or temp table, but is this the only option I have? Can I use an array to pass the recordset into, and then use the array as the source for the form (without having to loop through each record in the array)? Is there another way to do this?