Access and Oracle backends

[Guest]

I'm being asked to create an Access frontend (interface) that will connect to
an Access backend and an Oracle backend. I have searched and can't find
anything to tell me what is involved here. Can this be done from one
frontend? Do connections get dropped? How are they re-established? I read
something about pass-through queries and I'm not sure what they are. I'm not
sure at all how to approach this. Please advise.

 

[Pat Hartman \(MVP\)]

It is simpler than you might imagine. To connect to ANY ODBC datasource you
need to have the appropriate ODBC driver installed. When you install
Access, some will be installed if you chose the option to install drivers.
This will install SQL Server for example. For Oracle, you need to install
its ODBC driver separately. Once you have the driver installed, you can
create a DSN to connect to the database. To do that open the Data Sources
(ODBC) dialog from Control Panel/Administrative Tools. Create a system DSN
so that all users of the PC will have access to the data source. You will
need to know the name of the server and may need to supply a userID and
password and you may need to supply the name of a specific database. The
dialog for creating DSNs varies with the ODBC and I don't have an Oracle one
to look at so try to work it out and if you can't, post back with
descriptions (or pictures) of the dialog and what it is asking for.

Now that you have a DSN, you can go back into Access and from File/Get
External Data/Link you can choose ODBC from the files of type (last entry in
the combo) and then select the DSN you created earlier. You should then get
a dialog with a list of tables. You can select one or more tables and then
press the OK (or whatever it is) button to finish the linking. Once the
tables are linked, you can treat them pretty much the same as you would
treat linked or local Jet tables.

There are some good practices you need to be aware of when working with
server side data so download the optimizing client/server applications
article from the knowledgebase to get you started.

 

[Aaron Kempf]

Access Data Projects don't support Oracle.. so you should move to SQL Server

 

[Larry Linson]

Access Data Projects don't support Oracle.. so you should move to SQL
Server

Access Databases, with Oracle ODBC drivers, play nicely with Oracle.

I've never seen a company with Oracle as their corporate standard database
which would remotely consider converting their corporate standard in order
that they could use a particular user interface approach, but perhaps Mr.
Kempf's experience differs.

Larry Linson
Microsoft Access MVP

 

[Aaron Kempf]

who gives a crap about oracle users?

they can screw themselves, they choose a crap database and then they have to
pay for developers tools


when Microsoft started winning all of the TPC awards; Oracle should have
just give up the good fight.

Oracle lost this was 10 years ago.



and i don't care-- if I was at your comapny; I'd get you 'oracle dorks'
fired and replace it with a real database


I used to work for this startup; we had 6 Oracle DBA / Developers

and little things like reporting?? they couldn't provide simple numbers like
'how many visitors did we have last month'

how in the hell do you have a company with 40 employees-- SIX of them work
on Oracle.. and they can't provide little metrics like that?

 

[Aaron Kempf]

I've never seen a company that DECIDES to move to oracle after a careful
evaluation of SQL Server


Do you know anything about Analysis Services?
Oracle-- or for that matter, any database-- without Analysis Services is
'worthless as best'


WORTHLESS without Analysis Services

do you here me, kid??

 

[Aaron Kempf]

I disagree.

if you use linked tables, then you've got to use SQL passthroghs.
and if you use SQL Passthroughs; then you've got to do a whole ton of coding

so-- screw MDB and screw Oracle

go to www.microsoft.com/sql and see if you can find a real database that
fits into your budget

 

[Tony Toews [MVP]]

do you here me, kid??

That should be hear not here.

I've had the pleasure of meeting Larry in person a number of times at
Microsoft. Larry is no kid. He's been in the computer business for
at least forty years.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Pat Hartman \(MVP\)]

YOU may need to use pass-through queries but I don't. I can use bound forms
and querydefs in an .mdb and my query gets "passed through" to the server
(whether it be SQL Server, DB2, Oracle, Sybase, MySQL, or whatever) and as
long as I use selection criteria, ONLY the requested rows are returned. You
could prove this to yourself if you were interested in truth and knowledge
rather than argument by using the SQL Profiler to actually view the SQL
strings that Access passes to the server.