When you connect to an Oracle database with Access and ADO, is there a way
to get information on the Oracle table relationships?
Jeff 7 12304
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1093194494.325919@yasure... Query the data dictionary views. Run this query for a list of them.
SELECT view_name FROM all_views WHERE view_name LIKE 'ALL%';
But why connect to Oracle with MS Access? An overwhelming desire to use the worst possible tool to do the job?
HAHAHAHA. No, us low ranking po' folk in the military don't have Oracle
installed let alone get Oracle training, or ANY database training for that
matter. All government desktop PCs come with Microsoft Access installed and
I had to teach myself to use it. I have a couple of stand-alone
applications I have to use that connect to an Oracle database, but the
applications leave ALOT to be desired because I still have to build my own
lists of data and compile statistics by hand over and over. If I can
connect to the Oracle database with Access and ADO, I can build my own
queries and reports once and be done with it at the click of a button
whenever it's needed. Whether or not Access and ADO are the worst possible
tools, it's still a hell of alot faster than doing it by hand the way I do
it now. The Oracle database I tried to connect to turned out to have about
2000 tables with a jillion fields. I need to figure out what the
relationships are before I can build my own queries.
Jeff
You can start out with something like the following - select * from
dict where table_name like '%COLUMN%';
Note that most views are available in three flavors (e.g. user_tables,
all_tables, dba_tables).
I'd recommend looking at User_tab_columns and user_constraints (where
constraint_type='R').
And if you are really lucky, you could try user_tab_comments and
user_col_comments.
GQ
"Jeff" <id***@thinkso.com> wrote in message news:<Pw7Wc.92$4o.40@fed1read01>... "Daniel Morgan" <da******@x.washington.edu> wrote in message news:1093194494.325919@yasure... Query the data dictionary views. Run this query for a list of them.
SELECT view_name FROM all_views WHERE view_name LIKE 'ALL%';
But why connect to Oracle with MS Access? An overwhelming desire to use the worst possible tool to do the job?
HAHAHAHA. No, us low ranking po' folk in the military don't have Oracle installed let alone get Oracle training, or ANY database training for that matter. All government desktop PCs come with Microsoft Access installed and I had to teach myself to use it. I have a couple of stand-alone applications I have to use that connect to an Oracle database, but the applications leave ALOT to be desired because I still have to build my own lists of data and compile statistics by hand over and over. If I can connect to the Oracle database with Access and ADO, I can build my own queries and reports once and be done with it at the click of a button whenever it's needed. Whether or not Access and ADO are the worst possible tools, it's still a hell of alot faster than doing it by hand the way I do it now. The Oracle database I tried to connect to turned out to have about 2000 tables with a jillion fields. I need to figure out what the relationships are before I can build my own queries.
Jeff
"Jeff" <id***@thinkso.com> wrote in message news:<Pw7Wc.92$4o.40@fed1read01>... "Daniel Morgan" <da******@x.washington.edu> wrote in message news:1093194494.325919@yasure... Query the data dictionary views. Run this query for a list of them.
SELECT view_name FROM all_views WHERE view_name LIKE 'ALL%';
But why connect to Oracle with MS Access? An overwhelming desire to use the worst possible tool to do the job?
HAHAHAHA. No, us low ranking po' folk in the military don't have Oracle installed let alone get Oracle training, or ANY database training for that matter. All government desktop PCs come with Microsoft Access installed and I had to teach myself to use it. I have a couple of stand-alone applications I have to use that connect to an Oracle database, but the applications leave ALOT to be desired because I still have to build my own lists of data and compile statistics by hand over and over. If I can connect to the Oracle database with Access and ADO, I can build my own queries and reports once and be done with it at the click of a button whenever it's needed. Whether or not Access and ADO are the worst possible tools, it's still a hell of alot faster than doing it by hand the way I do it now. The Oracle database I tried to connect to turned out to have about 2000 tables with a jillion fields. I need to figure out what the relationships are before I can build my own queries.
Hmmm, when I was a gummint mil contractor a few years ago, it seemed
there was a lot of online training available. Perhaps, really bad
training, but there you go. Find out where the online training
available to you is, and you might find a scrollable list that
actually includes both unix and oracle. One thing you might check out
is perl, which has oracle extensions as well as numerous places online
with examples. You might also discover the gummint has pretty
extensive site licensing, although you may need to discover how to
find a demigod called a "DBA" to find out how to use that stuff. But
for free, register on technet.oracle.com and you can download an
amazing amount of stuff, as well as tutorials and other information.
Not to mention your help desk might be able to give you licensed
copies of stuff. If you can access an Oracle database, you must be
authorized, right? There are a number of tools that help you figure
out the relationships, too, no need to torture yourself (especially in
the future, when you have to unlearn all the bad habits) with access.
TOAD, or even Oracle's own SQL Worksheet are better just for starters.
You surely must be able to get official Oracle client software for
accessing Oracle servers?
Assuming you aren't Libyan, or whatever.
jg
--
@home.com is bogus. http://en.wikipedia.org/wiki/Data_haven
Oracle client software? Why didn't I think of that? Probably because
Access users don't think in those terms. That sounds like something that
might help me. Does that allow you to run queries and view table
relationships after logging into an X.WORLD connection?
"Joel Garry" <jo********@home.com> wrote in message
news:91**************************@posting.google.c om... Hmmm, when I was a gummint mil contractor a few years ago, it seemed there was a lot of online training available. Perhaps, really bad training, but there you go. Find out where the online training available to you is, and you might find a scrollable list that actually includes both unix and oracle. One thing you might check out is perl, which has oracle extensions as well as numerous places online with examples. You might also discover the gummint has pretty extensive site licensing, although you may need to discover how to find a demigod called a "DBA" to find out how to use that stuff. But for free, register on technet.oracle.com and you can download an amazing amount of stuff, as well as tutorials and other information. Not to mention your help desk might be able to give you licensed copies of stuff. If you can access an Oracle database, you must be authorized, right? There are a number of tools that help you figure out the relationships, too, no need to torture yourself (especially in the future, when you have to unlearn all the bad habits) with access. TOAD, or even Oracle's own SQL Worksheet are better just for starters. You surely must be able to get official Oracle client software for accessing Oracle servers?
Assuming you aren't Libyan, or whatever.
jg -- @home.com is bogus. http://en.wikipedia.org/wiki/Data_haven
Thanks, I'll play with that.
"GQ" <db********@yahoo.com> wrote in message
news:aa**************************@posting.google.c om... You can start out with something like the following - select * from dict where table_name like '%COLUMN%'; Note that most views are available in three flavors (e.g. user_tables, all_tables, dba_tables). I'd recommend looking at User_tab_columns and user_constraints (where constraint_type='R'). And if you are really lucky, you could try user_tab_comments and user_col_comments.
GQ
"Jeff" <id***@thinkso.com> wrote in message
news:<Pw7Wc.92$4o.40@fed1read01>... "Daniel Morgan" <da******@x.washington.edu> wrote in message news:1093194494.325919@yasure... Query the data dictionary views. Run this query for a list of them.
SELECT view_name FROM all_views WHERE view_name LIKE 'ALL%';
But why connect to Oracle with MS Access? An overwhelming desire to
use the worst possible tool to do the job?
HAHAHAHA. No, us low ranking po' folk in the military don't have Oracle installed let alone get Oracle training, or ANY database training for
that matter. All government desktop PCs come with Microsoft Access installed
and I had to teach myself to use it. I have a couple of stand-alone applications I have to use that connect to an Oracle database, but the applications leave ALOT to be desired because I still have to build my
own lists of data and compile statistics by hand over and over. If I can connect to the Oracle database with Access and ADO, I can build my own queries and reports once and be done with it at the click of a button whenever it's needed. Whether or not Access and ADO are the worst
possible tools, it's still a hell of alot faster than doing it by hand the way I
do it now. The Oracle database I tried to connect to turned out to have
about 2000 tables with a jillion fields. I need to figure out what the relationships are before I can build my own queries.
Jeff
"Jeff" <id***@thinkso.com> wrote in message news:<pfxWc.1585$4o.24@fed1read01>... Oracle client software? Why didn't I think of that? Probably because Access users don't think in those terms. That sounds like something that might help me. Does that allow you to run queries and view table relationships after logging into an X.WORLD connection?
There's so much, I can't begin to tell you. Enterprise manager can
show dependencies, table layouts and contents. The downside is, there
is so much I can't begin to tell you.
A lot of people like http://www.quest.com/toad/ (I have no opinion one
way or another, no relation to quest, I know many mil sites use their
stuff). Look at cdo.marketplace in general for downloadable tools.
comp.databases.oracle is defunct, don't crosspost between cdos and
there. Here is a primer for cdos: http://members.cox.net/oracleunix/readme-cdos.htm
jg
--
@home.com is bogus.
....persists for 4 or more hours, seek medical help immediately. http://www.sfgate.com/cgi-bin/articl...0/MN243061.DTL
"Jeff" <id***@thinkso.com> wrote in message news:<lH3Wc.14$4o.0@fed1read01>... When you connect to an Oracle database with Access and ADO, is there a way to get information on the Oracle table relationships?
Jeff
The following query lists all relationships:
select a.owner||'.'||a.table_name "Referenced Table",
b.owner||'.'||b.table_name "Referenced by", b.constraint_name "Foreign
Key"
from all_constraints a, all_constraints b
where b.constraint_type = 'R'
and a.constraint_name = b.r_constraint_name;
....And the following one lists all columns that compose each foreign
key:
SELECT CONSTRAINT_NAME, TABLE_NAME, POSITION, COLUMN_NAME
FROM ALL_CONS_COLUMNS
ORDER BY CONSTRAINT_NAME, TABLE_NAME, POSITION
/
Good luck. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jeremy Weiss |
last post by:
I'm trying to build a database that will handle the monthly billing needs of
a small company. I'm charting everything out and here's what I see:
table for customers
sub table to track payments...
|
by: Dan |
last post by:
Hi, just ran into my first instance of a backend Access97 database not
compacting. I'm getting the "MSACCESS.EXE has generated errors.." message on
compact.
I've narrowed it down to the largest...
|
by: Ed |
last post by:
I am running Access 2002 and just ran the built in Access wizard for
splitting a database into a back end (with tables) and front end (with
queries, forms, modules, etc.).
After running the...
|
by: Neil Ginsberg |
last post by:
I have ODBC linked tables to a SQL 7 database in an A2K database. The linked
tables do not have the password stored in them, so the first time the user
accesses them, they need to enter the SQL...
|
by: Gandalf |
last post by:
I'm creating relationships between tables using VBA and ADOX. I can
create one-to-one relationships with an inner join, but I can't figure
out how to create these relationships with an outer join...
|
by: Hi5 |
last post by:
Hi,
I am working on a project in access 2000, Whilst I already have a table
called client and seems to be related with other tables ,I tried making
TBL client as lookup too, I ended up having...
|
by: Bri |
last post by:
Hi,
First let me explain the process I have going on, then I'll address the
problems I'm having:
1) Insert records in a temp table using a query
2) Using a query that joins the temp table with...
|
by: phil-news-nospam |
last post by:
Is there really any advantage to using DIV elements with float style
properies, vs. the old method of TABLE and TR and TD?
I'm finding that by using DIV, it still involves the same number of...
|
by: mark_aok |
last post by:
Hi all,
I have a situation where I have a split database. At the back end, I
need to
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table...
|
by: anjee |
last post by:
Hello,
Is it possible to create multiple foreign keys on a field in a table
from values in two separate tables where the field value can be from
one table OR the other?
For example, I have an...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
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,...
| |