Re: Access-to-Oracle Question

Having suffered this transition many times (Access data migration to Oracle) I've changed my approach when using Access. (We "expanded" to Oracle when we began to approach the 1-gig limits of Access).

1. Since its a pain on Oracle when field names contain blanks, one has to convert the Access field names to use, say, underscores i.e. "[My Field]" --> "My_Field".
2. You cannot append anything from Access to an Oracle table unless the destination table contains a primary key. Sometimes, finding a primary key to use from within an Access table is difficult. (The 1st pass works fine with Access' autonumber field type, but with, say, weekly imports, the autonumber can go astray.
3. Before moving any "production" data to Oracle, without question, unconditionally, absolutely, positively, FIND AN ODBC DRIVER and test the crap out of it. (We've settled on INTERSOLVE's, but even it has problems which "they are working on..."

lotsa luck.

J. V. MORRIS <ovmorris_at_carol.net> wrote in article <01bc5058$d1e19fa0$0100007f_at_default>...
> Well, I hope somebody has already responded to your query, but--if
> not-here are my two cents worth. First, what do you mean "... it seems
> Oracle isn't set up at the moment to handle the amount of data and the
> fields that he has."?
> Surely, it can't be the size of the database per se. Are you saying he
> has a lot of tables with a lot of fields?
> Are you having problems defining an analogous Oracle database structure?
> If so, try ERwin or S-Designer and reverse engineer the Access database
> into Oracle. Heck, I think Oracle even has a product of its own to
> upgrade from Access to Oracle (to compete with Microsoft's product to
> upgrade to MS SQL Server).
> Once you have the Oracle database structure in place, all you have to do
> is attach the Oracle tables as external table and do an Access "Append
> Query" to upload the Access data tables to their Oracle counterparts;
> (after successful completion) drop the Access tables and redirect your
> forms and reports to the Oracle tables. The number of rows in any Access
> database is not going to present a problem. I've done as many as 70
> tables manually with well over 10,000 rows in a matter of hours.
> The real difficulty is in getting the equivalent Oracle database with
> referential integrity and appropriate indexes--that's why ERwin,
> S-Designer, etc., are invaluable. Of course, if the Access database is
> not well-designed, that's a different problem. (And a lot of Access
> databases kinda ignore piddling details like normalization, referential
> integrity and appropriate indexes. Design a good Oracle database and
> then use more selective Append Queries to populate the Oracle database.
> --
>
> Regards
> Joseph V. Morris
> ovmorris_at_carol.net
>
> nick.diciaccio_at_usa.net wrote in article
> <5j6dsk$1rd_at_basement.replay.com>...
> >
> > On 4/16, Rob Brown posted a message about converting an Access
> > database to Oracle. I too have a similar issue. At my company, they
 are going
> > to implement an Oracle database, and one individual has an extensive
> > database in Access. At this time, it looks like he may have to keep
> > running his database from his desktop, separate from everyone else,
 because it
> > seems Oracle isn't set up at the moment to handle the amount of data
> > and the fields that he has.
> >
> > Can anyone offer any suggestions as to how this information can be
> > converted into Oracle?
> >
> > --Nick DiCiaccio
> >
>
Received on Thu May 01 1997 - 00:00:00 CEST