472,954 Members | 1,937 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,954 software developers and data experts.

ODBC problems

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_TBLSALESMAN.SALESMAN, OSCAR_TBLSALESMAN.NAME,
OSCAR_TBLSALESMAN.INITIALS, OSCAR_TBLSALESMAN.TERRITORY,
OSCAR_TBLSALESMAN.SALESMANTYPE, OSCAR_TBLSALESMAN.SALESFORCE,
OSCAR_TBLSALESMAN.SALESMANRSM, OSCAR_TBLSALESMAN.SALESMANRVP,
OSCAR_TBLSALESMAN.LOCATION, OSCAR_TBLSALESMAN.CUSTOMER,
OSCAR_TBLSALESMAN.TRANSMITTINGLOCATION
FROM OSCAR_TBLSALESMAN;

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.TBLSALESMAN.SALESMAN, OSCAR.TBLSALESMAN.NAME,
OSCAR.TBLSALESMAN.INITIALS, OSCAR.TBLSALESMAN.TERRITORY,
OSCAR.TBLSALESMAN.SALESMANTYPE, OSCAR.TBLSALESMAN.SALESFORCE,
OSCAR.TBLSALESMAN.SALESMANRSM, OSCAR.TBLSALESMAN.SALESMANRVP,
OSCAR.TBLSALESMAN.LOCATION, OSCAR.TBLSALESMAN.CUSTOMER,
OSCAR.TBLSALESMAN.TRANSMITTINGLOCATION
FROM OSCAR.TBLSALESMAN;

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

SELECT TBLSALESMAN.SALESMAN, TBLSALESMAN.NAME, TBLSALESMAN.INITIALS,
TBLSALESMAN.TERRITORY, TBLSALESMAN.SALESMANTYPE,
TBLSALESMAN.SALESFORCE, TBLSALESMAN.SALESMANRSM,
TBLSALESMAN.SALESMANRVP, TBLSALESMAN.LOCATION, TBLSALESMAN.CUSTOMER,
TBLSALESMAN.TRANSMITTINGLOCATION
FROM OSCAR.TBLSALESMAN;

And

SELECT TBLSALESMAN.SALESMAN, TBLSALESMAN.NAME, TBLSALESMAN.INITIALS,
TBLSALESMAN.TERRITORY, TBLSALESMAN.SALESMANTYPE,
TBLSALESMAN.SALESFORCE, TBLSALESMAN.SALESMANRSM,
TBLSALESMAN.SALESMANRVP, TBLSALESMAN.LOCATION, TBLSALESMAN.CUSTOMER,
TBLSALESMAN.TRANSMITTINGLOCATION
FROM TBLSALESMAN;

Then I tried removing the tablename portion:

SELECT SALESMAN, NAME, INITIALS, TERRITORY, SALESMANTYPE, SALESFORCE,
SALESMANRSM, SALESMANRVP, LOCATION, CUSTOMER, TRANSMITTINGLOCATION
FROM TBLSALESMAN;
Any help?
Thanks
Nov 12 '05 #1
2 6050
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!
Nov 12 '05 #2
> 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.


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.
Nov 12 '05 #3

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

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
1
by: rguti | last post by:
Anybody know when the new version of the ODBC Driver is gonna be out? I'm using 3.51.11-1, but I'm having problems transferring information from SQL Server. Thanks.
2
by: uli2003wien | last post by:
Dear group, we are dealing with some very specific problems with ODBC, where a connection from SQL-Server to Mysql works with ODBC-driver 3.51.10.00 and does NOT work with ODBC-driver...
3
by: Ian | last post by:
We are currently experiencing problems with our Access XP database. We are using access as the front-end and are connecting to Oracle 7.3 via ODBC. Our users are running Win 98 and Win Xp. We are...
0
by: Julia Baresch | last post by:
Everyone, I posted the message below back in February and didn't get any information. I also wasn't able to find any documentation in MS Help, web site, or other groups. I'm posting now with...
9
by: mcbill20 | last post by:
Hello all. I just installed Oracle 10g developer tools on a machine running XP Pro and Office XP. Before this I had just the Oracle 9 client installed. I the previous configuration, I was able to...
2
by: Crazy Cat | last post by:
Hi all, I am having trouble getting linked Oracle 9 server in MS SQL Server 2005 Express to work properly. My machine is running Windows XP. The Microsoft and Oracle OLE DB Providers have...
3
by: nolanmadson | last post by:
I'm creating a front-end for some Teradata user maintenance tables in MS Access. I've started having problems in occasionally not being able to insert or update records in these tables. I've been...
16
by: network-admin | last post by:
We have Problems with Access query on Oracle 10g Database with ODBC Connection. The Query_1 is such as select * from xtable where ycolumn <"S" Result = ODBC Faild...
8
by: Phil Reynolds | last post by:
We are using SQL Server as a back end to an Access front end on a LAN using ODBC linked tables. Users are periodically getting the "data has been changed by another user" error, and it's causing...
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

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.