473,703 Members | 2,297 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
10 3526
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
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.wash ington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #3
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
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.wash ington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #5
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.was hington.edu> wrote in message news:<106988305 4.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
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
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.wash ington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #8
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.wash ington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #9

"Glen A Stromquist" <gl************ *@nospamyahoo.c om> wrote in message
news:Wp5xb.1736 84$jy.73333@clg rps13...
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4239
by: Amy Tseng | last post by:
Hi, I am having a problem accessing SQL Server 2000 via UNIX. I am accessing SQL Server 2000 from Solaris using Sybase Open Client (CT-Lib). Here is the error message: CT-LIBRARY error: ct_connect(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect
1
16146
by: majk | last post by:
hey! Im looking for a db tutoral which covers accessing a Microsoft SQL database on windows. I will be using VC++ if it matters. NO details just a basic introduction! Oracle DB access using C++ is also of interest! Thanks!
13
3315
by: BigDaDDY | last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going to be kicked back in my chair eating popcorn and watching football 10 years from now, while all you clowns are scrambling to rewrite all your code because Microsoft upgraded all their crap and nothing you wrote 10 years earlier works. It doesn't take a rocket scientist to figure out that Microsoft is unreliable. Try opening an Excel 95 spreadsheet you wrote in...
1
9196
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this will work for 9i and even 10g ) No one had what I needed, so I wrote it myself. I Rule. This code isn't going for efficiency, and isn't trying to be dynamic. It doesn't create the table structure in Oracle, that's up to you. (I
6
4340
by: Jeff | last post by:
Has anyone had any luck accessing an Oracle database from a web service? I have a C# DLL with various code to query an Oracle database. If I call the methods in this DLL from a Windows Forms application everything works just fine. However, if I call the methods in this DLL from a web service, I receive the following error when the database connection is opened: Error: ORA-12154: TNS:could not resolve service name Does anyone know...
4
1986
by: Mark D Powell | last post by:
We are running SQL Server 2000 SP3. We have linked servers in use that we use to access Oracle tables. Recently the claim has been made that you can access Oracle tables from within SQL Server without using a Linked Server. I searched Books Online using keywords: linked, remote, and Oracle and did not find anything. A search of the newsgroup archives found only entries related to using Linked Servers.
2
4077
by: Mark Brown | last post by:
I'm trying to access an Oracle database vb.net. I found several examples. I tried following each one, but I get the same error no matter what I try. I don't really understand what the error means (I've never tried accessing an Oracle database before). The error I get is: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I installed the oracle dll and added it as reference (I get the same error...
8
14305
by: =?Utf-8?B?RGF2aWQrKw==?= | last post by:
Hi, I have been developing Web Services in VS2005. Usually I use the built in server in VS2005 to test and develop the Web Service. However I now want to make this Web Service available to other computers (and applications) in our local company network. When the service runs it accesses an Oracle database and pulls some data out. It works fine when running in VS2005 server. To allow the Web Service to
3
5637
by: Nathan Sokalski | last post by:
When I attempt to access a Microsoft Access database from my website, I recieve the following error: Server Error in '/' Application. -------------------------------------------------------------------------------- Security Exception Description: The application attempted to perform an operation not allowed by the security policy. To grant this application the required permission please contact your system administrator or change the...
0
8669
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9251
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9121
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7867
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4432
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3123
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2450
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2069
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.