Update oracle via Access forms?
in [Help]
|
From: Luting on 24 Jun 2008 15:26 Hi, Is it possible to update oracle via Access forms? I am thinking maybe I could make a link table connnected with oracle database. And the form could be based on the link table. Does this sound feasible? Any better ways? Thank you.
From: Rich P on 24 Jun 2008 17:47 A reliable approach for your task is to use ADO. If you can make an ODBC connection to the Oracle database then you can make an ADO connection. You can get the connection string of the ODBC linked table by setting a tableDef object (VBA) and do a Debug.Print tdf.Connection Then set an ADO command object to this connection. The catch is that what you are trying to accomplish using a Form for RELIABLY updating an Oracle table will require a little bit of programming. So before I throw out a bunch of meaningless code you might evaluate your goal and decide if you want to go the VBA solution. If you do - just let me know (here). Rich *** Sent via Developersdex http://www.developersdex.com ***
From: purpleflash on 24 Jun 2008 19:55 On 24 Jun, 20:26, Luting <houlut...(a)gmail.com> wrote: > Hi, > > Is it possible to update oracle via Access forms? > I am thinking maybe I could make a link table connnected with oracle > database. And the form could be based on the link table. Does this > sound feasible? > Any better ways? > > Thank you. It is extremely easy to make an ODBC connection to an Oracle back end, Microsoft provide a very good and (free) ODBC driver for Oracle! Just go to File, Get External Data, Link Tables, in the drop down go to the last entry ODBC and follow the instructions, you may have to install an appropriate driver (using hte Microsoft provided MDAC).Then base your form on that linked table. In an enterprise situation (Oracle back end) it is normal for any constraints and privileges to be implemented at the Oracle end where they belong. There is no real need to involve VBA unless you wish to provide complex data entry or updating facilities for the user. We run entire enterprise wide, highly complex data manipulation, entry, retrieval and reporting systems utilising MS Access forms linked to complex Oracle tables. We find them to be very flexible, quick to build and operate, efficient and robust. Once a link has been made to an Oracle table Access sees that table just the same as a native Access table, you can write queries, create forms and manipulate your data as if it is a local table as long as you have the requisite privileges to do those operations granted to you by your Oracle DBA.
|
Pages: 1 Prev: Query Oracle Dual via Access Link Table? Next: Changing Form's view in VBA |