Re: Access front-end to Oracle - How to?

Paolo,

I suggest that you look into products by Embarcadero Technologies, Inc. Those would be, "DB Artisan", "ER/Studio", and "Rapid SQL", although you would probably only need "ER/Studio" to do this. http://www.embarcadero.com

These are RDBMS Client tools that run on Win95/NT, and allow you to interface or manage multiple RDBMS systems concurrently.

These are what I used in order to do this same exact type of thing here at the phone company that I am consulting to. They had a 3rd party development firm that was doing all its work for them in Microsoft Access. There were 3 databases comprised of up to 60 tables each, primary keys, indexes, table relationships, etc.

Using ER/Studio I imported the Access database into a visual data modeling tool.

Using the data modelling tool, I first inspected their tables for good design and entity relationships, then wrote a report on how/why their design could be made better in the future.
Then I asked ER/Studio to generate a new Oracle type visual data model based upon the Access one,
which it did, including automatic translation of all data-types and relationships to Oracle types.
Then I asked it perform a "consistency check" against Oracle RDBMS rules. It produced a list of "Errors", or things that were legal in Access but would no longer be legal in Oracle, such as non alpha-numeric letters in table and index names, etc.
I went through and fixed all of the offending fields, tables, indexes, relationships, etc.

Then, using DB Artisan (or its Oracle equivalent, Enterprise Manager), I created the necessary Tablespaces on our Oracle server that would hold the tables and indexes from each of their original Access projects.

Then, I used ER/Studio to "Auto Generate" all of the Oracle database entities based upon the visual data model that I had created and error-checked based on the original Access tables.

This it did... and let me tell you... a computer program can generate and run the SQL code necessary to create 130 tables and concommital indexes and relationships MUCH, MUCH, MUCH faster than you can write out the SQL to do it yourself. *Grin*

Anyway... THEN, in Access I unloaded all of their data into raw, comma-delimetted data files.
(This step was perhaps unecessary, as I you can do a DIRECT Access to Oracle data migration, but I needed to go through and massage their original data a little on the pure text level.)
In DB Artisan I created a set of "Load Utility" control files for Oracle, based upon my new Oracle tables.
Then using DB Artisan I started a "Load Process", using those same control files and comma-delimitted raw data files, to load up my new Oracle database.

Of course, you might have to be careful to load the tables in a sequence that is appropriate to any table relationships.

I did all this from my NT Workstation machine, over the network, to my Oracle Server running on a SUN Enterprise Server.

This whole process, takes a little reading and learning, but once you've got it down, it is extremly effective. We purchased the Embarcadero tools for about $2,000 including an extra support policy. I have found their products to be extraordinary in their functionality and ease of use... saving me hundreds and hundreds of hours over what I usually would have done in SQL*Plus or SQL Script files.

Good luck!

--
James Arvigo

• SQL Server & Oracle DBA
• Software & Intranet Developer
• Thrifty Call, Inc. via The Maxim Group
• Austin, Texas
  *----------------------------------------------------------
  
• EMAILS:
• Work: JamesA -AT- ThriftyCall.com
• Home: JArvigo -AT- Hotmail.com
  *
  
• ( Sorry I had to obfuscate the emails... *sigh* )

  ---

Paolo F. Cantoni wrote:

> Hi,
>
> We have a Human Resources Database which also handles Organisational
> Structure among multiple Business Entities if required.  It is currently
> written in Microsoft Access and has separate Application Database and Data
> Database.  The data is fully normalised and consists of over 130 tables.
> The database works fine with the small number of concurrent users it
> currently has, but a client wants to move it to Oracle (back end) as that is
> their corporate-wide database.  They want to make the "public" data in the
> HR DB generally available.  Are there any information sources, white papers
> etc on how to do this?
>
> The database design makes _extensive_ use of referential and semantic
> integrity to ensure high quality data (and, in fact, has been used to
> consistency check the operational application from which we import data).
>
> We have large numbers of queries (both for look-up and processing).
>
> We haven't implemented security in the past (because the application was HR
> internal).
>
> Any pointers (and success stories from the front line) appreciated.
>
> Thanx in Advanz
>
> Regardz, Paolo F. Cantoni   Tel: +61-2-9498 5945
>   Director    Fax: +61-2-9418 4402
>   -Semantica-    Cell: +61-416 11 00 95
> Suite 2, Charing House    EMail: pcantoni_at_semantica.com.au
> 45 Kendall Street    Web: http://www.semantica.com.au

> Pymble    NSW    2073     AUSTRALIA