Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: moving from Access to Oracle

Re: moving from Access to Oracle

From: Don Jerman <>
Date: Mon, 12 Mar 2001 14:47:59 -0800
Message-ID: <>

You might try just changing the tables in the master Access DB to links-to-Oracle-tables through ODBC. I don't know enough about Access replication to tell if this would work, but it's cheapest. Alternatively, install Oracle Lite on the mobile client and use links in the client MDB, but let Oracle Lite handle replication to the master. This I think might be more successful, but it is more complicated in terms of what must be changed.

You don't have to change the function calls unless you want your SQL to be efficient ;-). Access will happily interperet the SQL and manipulate the data as long as you don't use passthrough queries, but you may grow old (or crash) waiting for a table join as Access will try to read and filter the tables itself. You may also find a need to create sequences and auto-incrementor triggers to simulate autonumber columns if they're used... Hopefully there are no Memo fields. If the VBA is deep enough to use bidirectional recordsets you may be in trouble (and headed for a rewrite).

A better application of technology might be to write a Java or C++ front end and use Oracle Lite on the mobile client to synchronize with the Oracle database, dropping Access altogether, but yes, it is expensive to manage data well :-).

Bryan Sirtosky wrote:

> Listers,
> I have a client who currently has an application that uses an Access
> database with an Access front end interface (VBA). Since the users are
> required to make changes to data at any time, they each have the app and a
> local database replicated on their laptops which they can update and then
> later 'resync' their changes with the centralized Access database at work.
> Well, there seems to be too much data in the database and Access cannot
> handle it anymore. They would like to move to a web interface with an
> Oracle backend, but they can't afford to do all that. So they want to take
> the Access database and drop it into Oracle (most likely 8i) but use the
> same Access GUI. Then they expect their application to still work
> correctly.
> I think what they are asking for could be done, but lots of changes would
> have to be made. For example:
> 1. We would have to create an Oracle ODBC connection for the app to use to
> access the oracle database.
> 2. We would have to export the tables from Access to the new Oracle
> environment.
> 3. We would have to change their VBA code anywhere "nonOracle" function
> calls are made (e.g., datetime() would have to be changed to to_date() ).
> I don't know what would be done about the 'syncing' process.
> What may happen is that it may be just as expensive to make the changes
> required as to go to a web app (which they really need). Has anyone ever
> had to move an application from Access to Oracle?
> Any input would be greatly appreciated.
> Thanks,
> Bryan
> --
> Please see the official ORACLE-L FAQ:
> --
> Author: Bryan Sirtosky
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Mon Mar 12 2001 - 16:47:59 CST

Original text of this message