By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

Accessing data from an MS access db from an Oracle UNIX server

P: n/a
Hello,

I am a newbie to Oracle databases....

We have a visualization front-end tool connected to an Oracle back-end
database on a Tru64 UNIX server. We also have clients with MS access
databases who would like to share their data using this visualization
tool but do not want to import their data into the Oracle server
back-end.

Is there any way we could use the Oracle backend to query the MS
Access databases and merge this data with other data in the Oracle
back-end for presentation by the visualization tool? Perhaps through
ODBC or some other standard?

The Oracle back-end is a a Tru64 UNIX server running Oracle 8i.

Any input would be appreciated.

Andrea Segovia
A/Project Manager
Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Andrea M. Segovia wrote:
Hello,

I am a newbie to Oracle databases....

We have a visualization front-end tool connected to an Oracle back-end
database on a Tru64 UNIX server. We also have clients with MS access
databases who would like to share their data using this visualization
tool but do not want to import their data into the Oracle server
back-end.

Is there any way we could use the Oracle backend to query the MS
Access databases and merge this data with other data in the Oracle
back-end for presentation by the visualization tool? Perhaps through
ODBC or some other standard?

The Oracle back-end is a a Tru64 UNIX server running Oracle 8i.

Any input would be appreciated.

Andrea Segovia
A/Project Manager


Oracles Heterogeneous services should do what you need, you set up a
dblink to the Access "database" thru ODBC and then more or less treat it
like any other linked db.
Nov 12 '05 #2

P: n/a
Andrea M. Segovia wrote:
Hello,

I am a newbie to Oracle databases....

We have a visualization front-end tool connected to an Oracle back-end
database on a Tru64 UNIX server. We also have clients with MS access
databases who would like to share their data using this visualization
tool but do not want to import their data into the Oracle server
back-end.

Is there any way we could use the Oracle backend to query the MS
Access databases and merge this data with other data in the Oracle
back-end for presentation by the visualization tool? Perhaps through
ODBC or some other standard?

The Oracle back-end is a a Tru64 UNIX server running Oracle 8i.

Any input would be appreciated.

Andrea Segovia
A/Project Manager


Your management needs to stop playing games with those that have the MS
Access databases and put all of the data into Oracle.

If you didn't have Oracle ... fine so you use MS Access. But once you
have Oracle there is no excuse for business data sitting on PCs where
there is no security, no guarantee of data integrity, and almost
undoubtedly no nightly backup.

All that money for Oracle and they are letting a some whiners make the
business decisions. Management ... needs to manage.

Move the d... data.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #3

P: n/a
Daniel Morgan wrote:
Andrea M. Segovia wrote:
Hello,

I am a newbie to Oracle databases....

We have a visualization front-end tool connected to an Oracle back-end
database on a Tru64 UNIX server. We also have clients with MS access
databases who would like to share their data using this visualization
tool but do not want to import their data into the Oracle server
back-end.

Is there any way we could use the Oracle backend to query the MS
Access databases and merge this data with other data in the Oracle
back-end for presentation by the visualization tool? Perhaps through
ODBC or some other standard?

The Oracle back-end is a a Tru64 UNIX server running Oracle 8i.
Any input would be appreciated.

Andrea Segovia
A/Project Manager

Your management needs to stop playing games with those that have the MS
Access databases and put all of the data into Oracle.

If you didn't have Oracle ... fine so you use MS Access. But once you
have Oracle there is no excuse for business data sitting on PCs where
there is no security, no guarantee of data integrity, and almost
undoubtedly no nightly backup.

All that money for Oracle and they are letting a some whiners make the
business decisions. Management ... needs to manage.

Move the d... data.


I agree thats the best solution overall Daniel, but in some cases (like
mine) it may not be practical or even doable. Many of our areas purchase
these little MSaccess back end "niche" programs that do only one thing
and the vendor's eyes start to glaze over when you ask them if they have
an Oracle or SQL server version. I can rant all I want about the lack of
security, no restore to point of failure etc.. but in the end the
business unit will purchase what fits their needs best and I have to
deal with it.
Fortunatly I only have one set of tables in MsAccess that I have to have
accesible from an Oracle based app, but once it was set up it runs along
quite nicely. I'd still sooner have the tables sitting in their own
little Oracle schema, but if the product doesn't support it then I have
to make do...


Nov 12 '05 #4

P: n/a
Glen A Stromquist wrote:
Daniel Morgan wrote:
Andrea M. Segovia wrote:
Hello,

I am a newbie to Oracle databases....

We have a visualization front-end tool connected to an Oracle back-end
database on a Tru64 UNIX server. We also have clients with MS access
databases who would like to share their data using this visualization
tool but do not want to import their data into the Oracle server
back-end.

Is there any way we could use the Oracle backend to query the MS
Access databases and merge this data with other data in the Oracle
back-end for presentation by the visualization tool? Perhaps through
ODBC or some other standard?

The Oracle back-end is a a Tru64 UNIX server running Oracle 8i.
Any input would be appreciated.

Andrea Segovia
A/Project Manager


Your management needs to stop playing games with those that have the
MS Access databases and put all of the data into Oracle.

If you didn't have Oracle ... fine so you use MS Access. But once you
have Oracle there is no excuse for business data sitting on PCs where
there is no security, no guarantee of data integrity, and almost
undoubtedly no nightly backup.

All that money for Oracle and they are letting a some whiners make the
business decisions. Management ... needs to manage.

Move the d... data.

I agree thats the best solution overall Daniel, but in some cases (like
mine) it may not be practical or even doable. Many of our areas purchase
these little MSaccess back end "niche" programs that do only one thing
and the vendor's eyes start to glaze over when you ask them if they have
an Oracle or SQL server version. I can rant all I want about the lack of
security, no restore to point of failure etc.. but in the end the
business unit will purchase what fits their needs best and I have to
deal with it.
Fortunatly I only have one set of tables in MsAccess that I have to have
accesible from an Oracle based app, but once it was set up it runs along
quite nicely. I'd still sooner have the tables sitting in their own
little Oracle schema, but if the product doesn't support it then I have
to make do...


The vendors may not be able to do Oracle. Thats understandable. But
there is nothing that stops these systems from being backed up every
night to the Oracle database and then using the Oracle database as per
your original inquiry.

And I'd give you the same advice if the back-end was SQL Server or
Informix or any other industrial strength RDBMS. MS Access on a personal
PC just isn't a serious database for business applications.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #5

P: n/a
My enquiry was not meant to start a religious war....I also agree that
using a robust database on a server (preferably UNIX) is the better
way to go, but one has to work within the limitations imposed by the
environment.

In this case, the requirement is partially driven by accomodating
groups without the funding to spend on importing their data into the
Oracle environment as well as those groups with the type of
applications that Glenn mentioned. It is hoped that seeing what can be
done within the Oracle environment will encourage them to seek funding
for this....

As an aside, I doubt that your reply will encourage others new to
Oracle to post questions to the group for fear of being lambasted.
This forum is quite valuable as a learning tool, and while I agree
that pointing out the "ideal way" of doing things is useful, the
manner of your reply may be discouraging. You make the assumption that
the poster has the authority to correct the mistakes of the
organization and that is not a valid assumption in many cases. "User
and management education" is probably a better way to address this,
but in my experience, this does not happen overnight.

(Although new to Oracle, I have over 13 years systems and network
administration experience - and have slowly educated users,
developers, and management on various issues.)

Daniel Morgan <da******@x.washington.edu> wrote in message news:<1069883054.13946@yasure>...
Glen A Stromquist wrote:
Daniel Morgan wrote:
Andrea M. Segovia wrote:

Hello,

I am a newbie to Oracle databases....

We have a visualization front-end tool connected to an Oracle back-end
database on a Tru64 UNIX server. We also have clients with MS access
databases who would like to share their data using this visualization
tool but do not want to import their data into the Oracle server
back-end.

Is there any way we could use the Oracle backend to query the MS
Access databases and merge this data with other data in the Oracle
back-end for presentation by the visualization tool? Perhaps through
ODBC or some other standard?

The Oracle back-end is a a Tru64 UNIX server running Oracle 8i.
Any input would be appreciated.

Andrea Segovia
A/Project Manager

Your management needs to stop playing games with those that have the
MS Access databases and put all of the data into Oracle.

If you didn't have Oracle ... fine so you use MS Access. But once you
have Oracle there is no excuse for business data sitting on PCs where
there is no security, no guarantee of data integrity, and almost
undoubtedly no nightly backup.

All that money for Oracle and they are letting a some whiners make the
business decisions. Management ... needs to manage.

Move the d... data.

I agree thats the best solution overall Daniel, but in some cases (like
mine) it may not be practical or even doable. Many of our areas purchase
these little MSaccess back end "niche" programs that do only one thing
and the vendor's eyes start to glaze over when you ask them if they have
an Oracle or SQL server version. I can rant all I want about the lack of
security, no restore to point of failure etc.. but in the end the
business unit will purchase what fits their needs best and I have to
deal with it.
Fortunatly I only have one set of tables in MsAccess that I have to have
accesible from an Oracle based app, but once it was set up it runs along
quite nicely. I'd still sooner have the tables sitting in their own
little Oracle schema, but if the product doesn't support it then I have
to make do...


The vendors may not be able to do Oracle. Thats understandable. But
there is nothing that stops these systems from being backed up every
night to the Oracle database and then using the Oracle database as per
your original inquiry.

And I'd give you the same advice if the back-end was SQL Server or
Informix or any other industrial strength RDBMS. MS Access on a personal
PC just isn't a serious database for business applications.

Nov 12 '05 #6

P: n/a
On 27 Nov 2003 09:45:13 -0800, an****@mun.ca (Andrea M. Segovia)
wrote:
"User
and management education" is probably a better way to address this,
but in my experience, this does not happen overnight.


I think you are wrong. Most of the people responding here have been
suffering for years from people who don't have any experience and are
asked by management (which doesn't have a clue about databases either)
to design a database. Experience dictates those 'developers' usually
know better and don't *want* to be educated. They can point and click
and that is sufficient. On the other hand if they want to be educated
they leave the company before getting anywhere, after which they are
replaced by someone else inexperienced, leaving you with their mess.
To get through to these people the only thing which helps is getting
quite explicit. If that might be considered as 'rude', so be it.
--
Sybrand Bakker, Senior Oracle DBA
Nov 12 '05 #7

P: n/a
Andrea M. Segovia wrote:
My enquiry was not meant to start a religious war....
You didn't. The answer was fact based and written to solve the problem
you presented: Nothing more.
As an aside, I doubt that your reply will encourage others new to
Oracle to post questions to the group for fear of being lambasted.


Anyone that isn't trembling in trepidation at the sight of their own
shadow knows that I lambasted your management ... not MS Access and not
you. If that scares them then perhaps they should retreat to 3x5 cards.

You comments about the "cost" of going to Oracle are based on what? I
could move data from an Access database to Oracle at the rate of one
database every day or two without a single dollar going to Oracle
corporation for more software. Perhaps you should talk to your SysAdmins
and DBAs about the concept of licensing by CPU.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #8

P: n/a
Sybrand Bakker wrote:
On 27 Nov 2003 09:45:13 -0800, an****@mun.ca (Andrea M. Segovia)
wrote:

"User
and management education" is probably a better way to address this,
but in my experience, this does not happen overnight.

I think you are wrong. Most of the people responding here have been
suffering for years from people who don't have any experience and are
asked by management (which doesn't have a clue about databases either)
to design a database. Experience dictates those 'developers' usually
know better and don't *want* to be educated. They can point and click
and that is sufficient. On the other hand if they want to be educated
they leave the company before getting anywhere, after which they are
replaced by someone else inexperienced, leaving you with their mess.
To get through to these people the only thing which helps is getting
quite explicit. If that might be considered as 'rude', so be it.
--
Sybrand Bakker, Senior Oracle DBA


And I think you, Sybrand, are correct.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #9

P: n/a

"Glen A Stromquist" <gl*************@nospamyahoo.com> wrote in message
news:Wp5xb.173684$jy.73333@clgrps13...
I agree thats the best solution overall Daniel, but in some cases (like
mine) it may not be practical or even doable. Many of our areas purchase
these little MSaccess back end "niche" programs that do only one thing
and the vendor's eyes start to glaze over when you ask them if they have
an Oracle or SQL server version. I can rant all I want about the lack of
security, no restore to point of failure etc.. but in the end the
business unit will purchase what fits their needs best and I have to
deal with it.
Fortunatly I only have one set of tables in MsAccess that I have to have
accesible from an Oracle based app, but once it was set up it runs along
quite nicely. I'd still sooner have the tables sitting in their own
little Oracle schema, but if the product doesn't support it then I have
to make do...


Glenn,

It may be worth looking at relocating the "data" to an Oracle database (to
gain the security and backup benefits), but continue to use the MSAccess
application as the client.

Oracle's Migration Workbench for MSAccess has wizards which _could_ make the
migration quite a simple process.
Paul Dixon

Nov 12 '05 #10

P: n/a
an****@mun.ca (Andrea M. Segovia) wrote in message news:<75*************************@posting.google.c om>...
Hello,

I am a newbie to Oracle databases....

We have a visualization front-end tool connected to an Oracle back-end
database on a Tru64 UNIX server. We also have clients with MS access
databases who would like to share their data using this visualization
tool but do not want to import their data into the Oracle server
back-end.

Is there any way we could use the Oracle backend to query the MS
Access databases and merge this data with other data in the Oracle
back-end for presentation by the visualization tool? Perhaps through
ODBC or some other standard?

The Oracle back-end is a a Tru64 UNIX server running Oracle 8i.

Any input would be appreciated.

Andrea Segovia
A/Project Manager

Andrea,

A belated response, but just in case you don't have a solution, here
is what will work:

Oracle HS--> Unix based ODBC Driver for Access ----O--- MS ACCESS on
Windows.

You can obtain a Unix based ODBC Driver for ACCESS from
http://www.openlinksw.com

These drivers are available on a free 30 day evaluation basis.

Regards,
Kingsley Idehen
OpenLink Software
Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.