ODBC problems

[neptune]

I currently link a table to an Oracle db. Anytime I open a query
based off it, I have to input username & password. Is there a way to
do this automatically?

I'd prefer to use linked tables, but I've read a pass-through query
can pass the passwords automatically. The directions seem
straightforward , but I can't get one to work. The select query from
the ODBC linked table looks like:

SELECT OSCAR_TBLSALESM AN.SALESMAN, OSCAR_TBLSALESM AN.NAME,
OSCAR_TBLSALESM AN.INITIALS, OSCAR_TBLSALESM AN.TERRITORY,
OSCAR_TBLSALESM AN.SALESMANTYPE , OSCAR_TBLSALESM AN.SALESFORCE,
OSCAR_TBLSALESM AN.SALESMANRSM, OSCAR_TBLSALESM AN.SALESMANRVP,
OSCAR_TBLSALESM AN.LOCATION, OSCAR_TBLSALESM AN.CUSTOMER,
OSCAR_TBLSALESM AN.TRANSMITTING LOCATION
FROM OSCAR_TBLSALESM AN;

When I convert it to a pass-through I get the error message:

ODBC – call failed.
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911:invalid character
(#911)

I know the underscore is invalid for Oracle so I replaced it with a
period

SELECT OSCAR.TBLSALESM AN.SALESMAN, OSCAR.TBLSALESM AN.NAME,
OSCAR.TBLSALESM AN.INITIALS, OSCAR.TBLSALESM AN.TERRITORY,
OSCAR.TBLSALESM AN.SALESMANTYPE , OSCAR.TBLSALESM AN.SALESFORCE,
OSCAR.TBLSALESM AN.SALESMANRSM, OSCAR.TBLSALESM AN.SALESMANRVP,
OSCAR.TBLSALESM AN.LOCATION, OSCAR.TBLSALESM AN.CUSTOMER,
OSCAR.TBLSALESM AN.TRANSMITTING LOCATION
FROM OSCAR.TBLSALESM AN;

I get the same error message, so I tried taking out the Oscar section

SELECT TBLSALESMAN.SAL ESMAN, TBLSALESMAN.NAM E, TBLSALESMAN.INI TIALS,
TBLSALESMAN.TER RITORY, TBLSALESMAN.SAL ESMANTYPE,
TBLSALESMAN.SAL ESFORCE, TBLSALESMAN.SAL ESMANRSM,
TBLSALESMAN.SAL ESMANRVP, TBLSALESMAN.LOC ATION, TBLSALESMAN.CUS TOMER,
TBLSALESMAN.TRA NSMITTINGLOCATI ON
FROM OSCAR.TBLSALESM AN;

And

SELECT TBLSALESMAN.SAL ESMAN, TBLSALESMAN.NAM E, TBLSALESMAN.INI TIALS,
TBLSALESMAN.TER RITORY, TBLSALESMAN.SAL ESMANTYPE,
TBLSALESMAN.SAL ESFORCE, TBLSALESMAN.SAL ESMANRSM,
TBLSALESMAN.SAL ESMANRVP, TBLSALESMAN.LOC ATION, TBLSALESMAN.CUS TOMER,
TBLSALESMAN.TRA NSMITTINGLOCATI ON
FROM TBLSALESMAN;

Then I tried removing the tablename portion:

SELECT SALESMAN, NAME, INITIALS, TERRITORY, SALESMANTYPE, SALESFORCE,
SALESMANRSM, SALESMANRVP, LOCATION, CUSTOMER, TRANSMITTINGLOC ATION
FROM TBLSALESMAN;


Any help?
Thanks

[Rich P]

Re: ODBC problems

For password/ID issues with ODBC connections - if you could relink your
table to the Oracle DSN there is a "save Password" checkbox on the Link
dialog box. Check that for it to remember your password. Note: In
order to create a DSN (or any other type of connection with an Oracle
DB, the Oracl Client needs to be installed - if you can see data with
your current link, then the Oracle client is installed - the
Microsoft/Oracle driver does not come into this picture). The Oracle
link is made to the Oracle service which you configure in the Oracle
Client.

For querying an Oracle table, you need to be up on PL Sql (Oracle sql).
Access uses Jet-Sql, MS Sql Server uses Transact Sql, Oracle uses PL
Sql. They are all fairly similar except for the really little things.
Of course, Transact Sql and PL sql support way more functionality than
Jet sql because these guys support industrial sized DB's where Access is
basically a front end system with tables that server as data buffers and
limited RDBMS functionality (on the industrial level).

Here is a sample PL sql statemet that you can run in Access:

"SELECT COUNT(fld1) FROM oraSvc1.oraTbl1 WHERE Datefld >=
TO_DATE('01-01-04', 'DD-MM-YY')"

Here oraSvc1 is a namespace (name) for a Service to a specific Oracle DB
and oraTbl1 is a table in the Oracle DB. I want to retrieve records
from this table where a date field in the table (Datefld) has values
greater than or equal to #1/1/04#. Note how PL sql delimits dates with
single quotes. Additionally, you have to cast the date value using the
PL Sql TO_DATE function which takes 2 arguments.


Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[neptune]

Re: ODBC problems

> For password/ID issues with ODBC connections - if you could relink your[color=blue]
> table to the Oracle DSN there is a "save Password" checkbox on the Link
> dialog box. Check that for it to remember your password.[/color]

That was simple. Thanks Rich. I knew the Oracle SQL was different
and was trying to convert the Access SQL, but I don't need to now.