To convert from Oracle Database to Access

R

Raghav

Hi,
Can we convert sequences of oracle database into access If yes then
How much time it will take to convert the database into Access. Details of
of Oracle Database are as follows:

1) there is no triggers.
2) Tables are 400. And I have already imported these tables into Access.
3) Procedures are 350.
4) Functions are 100.
5) Packages are 50.
6) Constraints are 2432.
7) Indexes are 350.
8) Types are 2.
9) Sequences are 22.
10) Views are 12.




Raghvendra Pratap Singh.
+91931394011
 
A

Albert D. Kallal

Hi,
Can we convert sequences of oracle database into access

You have to explain what you mean by "sequences" of oracle. I not heard that
term used in respect to databases.
If yes then
How much time it will take to convert the database into Access.

Software development is as much as a art as it is a science. In many studies
some developers will produce as much as a 100 times work as the next fellow.
So, any estimates would likely need a person well versed in oracle, and
ms-access. Further, you have to ask that PARTICULAR developer for a
estimate. It is all so common for the manger, or the person NOT writing the
code to come up with a estimate, and that is THE WORST WAY TO MAKE an
estimate. You have to talk to the DEVELOPER who is actually going to do the
work to make the estimate, someone here can't do that.

My estimate is likely to be up to 100 times different then another
developer.

Since most here are not versed in oracle, you likely going to have to talk
the oracle people as to the time and effort they currently invested, and
then to the developer who is well experienced in both platforms. And, as I
said, the time estimates can ONLY COME FROM THE DEVELOPER that

is ACTUALLY going to do the work, since as mentioned, the time taken to

complete a task is OFTEN 100 times different from one developer to another.

Further, I think you are miss-understanding ms-access. You can not ask:

how can I convert a oracle database into c++, or vb6?

As you can see, the above question makes little, if any sense. ms-access is
a developers tool that lets you write code, build forms, build reports. In
fact, ms-access makes a great front end, and your data can remain in the
oracle database. And, it should be pointed out that oracle does not have the
ability to build forms. So, it is often the case that you build the code,
forms
(the interface) in ms-access, but the data part would be placed on the
oracle database, or sql server database.

So, ms-access is a user interface tool that lets you write software, and
CONNECT (or "access") a database engine of your choice. This is the same as
using VB, or c++.

Ms-access is not the database, but a developers tool that
lets you write code.

For the last 8 or so years, the office cd has shipped with two database
engines that you can use with ms-access. The "default" data engine that MOST
developers building applications with ms-access choose to connect to the JET
database engine. The other engine that been on the office cd is desktop
edition of sql server. (it has stored procedures, and triggers).

So, not knowing what data engine you plan to use to replace the oracle data,
it makes an answer quite difficult. However, lets put the answer in context
of the two default database engines that have been shipping on the office cd
for use with ms-access for many years now.

1) there is no triggers.

JET - jet does not have triggers
MSDE - this data engine have triggers

However, you don't seem to need them, so, not a huge issue.
2) Tables are 400. And I have already imported these tables into Access.

Ouch!!...400 tables? Something is so wrong here in terms of design that I
will just refuse to comment. 400 tables speaks of such a horrible

un-normalized
data mess. I can't imagine why there is so many tables, but, this issue
should be addressed. The amount of money and time being wasted here could
used to feed the poor, or at least save someone a huge amount of money.
Some should be held accountable here.
3) Procedures are 350.

JET - the jet database engine does not have stored procedures. The only
choice you have with this data engine is to write your code in VBA. However,
VBA is actually a great language to write code in, but the problem is that
VBA is VERY MUCH different then the stored procedures in oracle pl-sql. In
fact, VBA is much better system to write code in, but VBA is so much
different then the pl-sql stored procedures, that this going to be somewhat
difficult. To convert the procedures, one would have to be fluent in the
pl-sql of oracle, and that of ms-access VBA coding.

MSDE - this database engine does support stored procedures and further the
coding style is MUCH MORE similar to the stored procedures of oracle
procedures. They are different, and each procedure would have to be
re-written, but this approach would likely be MUCH easier then writing the
code in VBA (despite VBA being a much better language to write code in).
4) Functions are 100.

JET - again, JET does not support functions. This means that those functions
would have be written in VBA in ms-access. However, once again, this ability
of JET to use ms-access functions is often considered one of the MOST
powerful features when you use JET with ms-access.

MSDE - functions can be written in MSDE. They are again much more similar to
those functions written in pl-sql for oracle.
5) Packages are 50.

I don't know what the above means....likely a oracle term.
6) Constraints are 2432.

That sounds like a huge number, but then again, we dealing with that
horrifying insane large number of tables (400). If this application was
normalized, you likely would cut this down to 30-50 tables max.

JET - jet supports referential integrity, and does not really have a
constraints settings that is much use.

MSDE - this much better in this regards.
7) Indexes are 350.

JET - JET supports a max of 32 indexes in one table. I don't see this as a
problem

MSDE - don't think there is particular limit

8) Types are 2.

Hum...I have no idea what the above means.

9) Sequences are 22.

Hum, perhaps these are autonumber fields...
10) Views are 12.

JET - any select query you build in ms-access is considered a view...so, non
issue, and no problem

MSDE - same...no problem.
 
A

Albert D. Kallal

Having 400 tables in Access is insane, not to mentioned "it's Stupid!".

Well, do remember, that the JET database engine is going to be perhaps 2, or
MORE times
faster then the oracle system. the reason for this is that you read data
DIRECTLY from
the disk drive (no odbc, or oledb) drivers are used. Further, there is no
network. Even
when you run JET on the SAME hardware as oracle, you still get faster read
speed. Further
oracle is ATOMIC, and thus updates are always going through several layers
of processes
before the actual disk drive is hit with a write (you have transaction logs,
and even updates
are a "copy" of the data and not released to the drive to write until a
commit. And, even
after a commit..you still have a "layer" of data. So, many many many more
processes
go on when you work with a server based data engine. They are typically much
slower
then JET which just scrapes data directly right off of the disk drive.

Of course in a multi-user environment, or when a "network" is involved, then
oracle is going
to be faster (mostly due to oracle reducing the traffic on the network.
However, using
JET on a desktop computer means no odbc, no oledb, and
JET has near direct access to the disk drive as compared to "many" layers of
processes
that have to go on for any well designed database server like oracle.

So, the issue is not that you can't place 400 tables in ms-access due to
some performance
reason (you can place 32,000 tables). My point is that 400 tables seems very
bad....
Albert,

It is possible to have more than 400 tables in Oracle, especially in large
organization with many branches.

Of course you can have that many tables. Of course it is possible,
and as I mentioned, ms-access and JET will likely perform faster then
the oracle system on a desktop computer in this case anyway.

My point is that we can
only hope that in place of 40 tables being coped over and over
for the 10 branches, the person added a simple ONE field for the branch,
and not a new set of tables for each new branch.

I think it is obvious that reporting or grouping any type of data across
each
branch becomes a night mare if a new set of tables is just "copied" each
time. Copying data over and over is bain of our industry, and is much
like the person who creates a new table everyone month in place of
adding a date field to the table!!! After 3 years...you have 36 sets of
tables.

We really don't know the reasons for so many tables, but is still a
HUGE warning light as to this being a mess....

There is still a big "if" as to why so many tables exist, but I
accept your point that there can be many reasons for this.
Even when the reasons are explained for such a large
number of tables, it is STILL usually a sign of bad things,
poor design, or people just being lazy, and copying
sets of tables over and over to representing
"something". That "something" could be a month, a
year, or a branch...and in all cases, this is useally
the worst type of problem to deal with, but one
we all too often see.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top