473,241 Members | 1,467 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Oracle connection problems; Odbc call failed

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 access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.

I am trying to connect to an Oracle 9 database on a VMS server. Here's
the info:

VMS machine:
-IP Address 192.168.0.250
-Database global name: db1.mcbill

PC:
-IP Address: 192.168.0.202
-Win XP Pro
-Office XP

When I run the linked table manager and then type username, password
and server I get an Odbc call failed error. Here is what I tried:

username: scott
password: tiger

For the server, I've tried the server's IP address, db1, db1.mcbill,
db********@192.168.0.250, etc.

Can anyone help?

BTW, I know this is not a connectivity issue as I can connect from this
PC using Oracle forms, SQLPlus and Toad.

Here is my tnsnames.ora file:

# TNSNAMES.ORA Network Configuration File:
c:\oracle\ora10g\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1.mcbill)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill

Nov 13 '05 #1
9 16967
There are some connection string examples here:

http://www.connectionstrings.com/
http://www.able-consulting.com/MDAC/...BC_DSNLess.htm

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
mc******@yahoo.com wrote:
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 access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.

I am trying to connect to an Oracle 9 database on a VMS server. Here's
the info:

VMS machine:
-IP Address 192.168.0.250
-Database global name: db1.mcbill

PC:
-IP Address: 192.168.0.202
-Win XP Pro
-Office XP

When I run the linked table manager and then type username, password
and server I get an Odbc call failed error. Here is what I tried:

username: scott
password: tiger

For the server, I've tried the server's IP address, db1, db1.mcbill,
db********@192.168.0.250, etc.

Can anyone help?

BTW, I know this is not a connectivity issue as I can connect from this
PC using Oracle forms, SQLPlus and Toad.

Here is my tnsnames.ora file:

# TNSNAMES.ORA Network Configuration File:
c:\oracle\ora10g\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1.mcbill)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill

Nov 13 '05 #2
mc******@yahoo.com wrote:
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 access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.


Are you using Access? Why are you worrying about constructing the
connect string? The linked table or ODBC DSN connection dialog for pass
through queries does this for you.

Does the DSN or DSNs you're using still work on other client PCs? You
did say SqlPlus worked, so what about deleting the DSN on this machine
and reconstructing it. Next try connecting to the DSN in Access again.

Have the 10g tools replaced the Oracle ODBC driver (I assume you're not
using the MS one) with a later version that might not be compatible with
the server installation? ODBC drivers in Oracle don't have a lot of
problem between versions, but I have had instances where updating a
driver on a client machine did away with some problems.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #3
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.
Here is a sample ADO module for a dsnless connection (from a VB
project):
~~~~~~~~~~~~~ BEGIN CODE
Option Explicit
'
' public connection objects
Public conn As ADODB.Connection ' connection object
Public rsA As ADODB.Recordset ' static recordset object (no
updates)
Public rsB As ADODB.Recordset ' dynamic recordset object
' both recordset objects use optimistic locking
' and client side cursors
'
Public Function OpenConn(strUser As String, strPwd As String, strSvc
As String)
' open db connection
Set conn = New ADODB.Connection
'
conn.Provider = ADODB_PROVIDER ' = OraOLEDB.Oracle
conn.Open strSvc, strUser, strPwd
'
End Function
'
Public Function OpenRsA(connLive As ADODB.Connection, strSQL As
String)
' open recordset A, static
Set rsA = New ADODB.Recordset
'
rsA.CursorLocation = adUseClient
rsA.Open strSQL, connLive, adOpenStatic, adLockOptimistic
'
End Function
'
Public Function OpenRsB(connLive As ADODB.Connection, strSQL As
String)
' open recordset B, dynamic
Set rsB = New ADODB.Recordset
'
rsB.CursorLocation = adUseClient
rsB.Open strSQL, connLive, adOpenDynamic, adLockOptimistic
'
End Function
'
Public Function ShutConnections()
' close the connection and
' disinstantiate the connection objects
'
Set rsA = Nothing
Set rsB = Nothing
'
If Not conn Is Nothing Then
conn.Close
End If
'
Set conn = Nothing
'
End Function
'
~~~~~~~~~~~~~ END CODE
On 24 Mar 2005 03:22:24 -0800, mc******@yahoo.com wrote:
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 access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.

I am trying to connect to an Oracle 9 database on a VMS server. Here's
the info:

VMS machine:
-IP Address 192.168.0.250
-Database global name: db1.mcbill

PC:
-IP Address: 192.168.0.202
-Win XP Pro
-Office XP

When I run the linked table manager and then type username, password
and server I get an Odbc call failed error. Here is what I tried:

username: scott
password: tiger

For the server, I've tried the server's IP address, db1, db1.mcbill,
db********@192.168.0.250, etc.

Can anyone help?

BTW, I know this is not a connectivity issue as I can connect from this
PC using Oracle forms, SQLPlus and Toad.

Here is my tnsnames.ora file:

# TNSNAMES.ORA Network Configuration File:
c:\oracle\ora10g\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1.mcbill)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill


Nov 13 '05 #4
Elaine wrote:
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.


At the risk of hijacking the thread, I'd really appreciate any thoughts
on the following, related to Oracle/ADO.

I tried using ADO when I first went to A2003 last Fall, after many happy
years with A97 (sniff!) 8)

All of my Access 97 FE apps on Oracle databases use pass through queries
and my code constructs and modifies the Oracle SQL for these as
required. I use the pass through query objects in Access to act as row
sources for list and combo boxes and for record sources for forms, be
they datasheet or form view.

I could not figure out how to do this sort of thing with ADO. All I
could do with ADO was generate recordset objects.

Can you do the sort of thing I'm describing with ADO?

Thanks very much for any comment.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #5
Tim Marshall wrote:
Elaine wrote:
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.

At the risk of hijacking the thread, I'd really appreciate any thoughts
on the following, related to Oracle/ADO.

I tried using ADO when I first went to A2003 last Fall, after many happy
years with A97 (sniff!) 8)

All of my Access 97 FE apps on Oracle databases use pass through queries
and my code constructs and modifies the Oracle SQL for these as
required. I use the pass through query objects in Access to act as row
sources for list and combo boxes and for record sources for forms, be
they datasheet or form view.

I could not figure out how to do this sort of thing with ADO. All I
could do with ADO was generate recordset objects.

Can you do the sort of thing I'm describing with ADO?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're talking about an .adp file, right? I use Views for the
ComboBoxes, ListBoxes. If the Combo/List box is dynamic (criteria
changes based on user input) I use stored procedures. When the criteria
changes I use VBA to change the RowSource:

RowSource: exec sp_name param1, param2, 'param3', etc.

For forms I use Views. If the user wants to change the criteria I just
change the form's filter.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkNd2IechKqOuFEgEQKW7wCeJvIYz9KeiY5GRJ43gEoaJO UHIioAnRyQ
g1KrtnWnPv5Djs11YaIZOhAZ
=o1sH
-----END PGP SIGNATURE-----
Nov 13 '05 #6
MGFoster wrote:
Tim Marshall wrote:
Elaine wrote:
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
All of my Access 97 FE apps on Oracle databases use pass through
queries and my code constructs and modifies the Oracle SQL for these
as required. I use the pass through query objects in Access to act as
row sources for list and combo boxes and for record sources for forms,
be they datasheet or form view.

You're talking about an .adp file, right?


Nope, a "normal" mdb. I'm not sure what an .adp is to be honest and
ignorant... 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #7
Tim Marshall wrote:
MGFoster wrote:
Tim Marshall wrote:
Elaine wrote:

Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
All of my Access 97 FE apps on Oracle databases use pass through
queries and my code constructs and modifies the Oracle SQL for these
as required. I use the pass through query objects in Access to act
as row sources for list and combo boxes and for record sources for
forms, be they datasheet or form view.


You're talking about an .adp file, right?

Nope, a "normal" mdb. I'm not sure what an .adp is to be honest and
ignorant... 8)


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In that case, just forget ADO and continue what you'd been doing in
Acc97 in Acc2003. Hey, if it ain't broke, don't fix it. ;-)

An .adp is an Access project, which uses an OleDB connection to a
provider (aka DB engine), usually MS SQL Server.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkN4kIechKqOuFEgEQIFGgCgtvJZCn16ulH0QJngHy31n+ FGAT4AoM0C
tOFtpNLEwNnppHYvl00WmbfL
=den/
-----END PGP SIGNATURE-----
Nov 13 '05 #8
Thanks for the info. However, maybe I used the wrong terminology. When
I referred to the connect string, I meant the string that I would type
into the "Server:" field when the popup login window for "Microsoft
ODBC for Oracle Connect" would appear.

In the list of data sources for the linked tables, the only reference
to Oracle that I saw was the ODBC Connection for Oracle. Should I be
using something else?

Bill

Nov 13 '05 #9
Tim,

You can use ADO to do anything which DAO does, but it's different.
Look at the samples, and search the web for some other clear samples.
Oracle's otn.oracle.com has some decent code samples. Go there.

Couple of things:
1) Even though you *can* mix ADO and DAO, I wouldn't use them in
the same procedures - really gets ugly

2) Get a book to guide you. May be out of print, but the OReilly
title _ADO: ActiveX Data Objects_ is pretty good for walking you
through it. Or maybe the _Access Cookbook_ by Ken Getz.

ADO lets you move forward and backward through recordsets, define
recordsets on the fly, insert, update or delete rows, in short,
everything you can do with DAO, plus some other cool stuff you can't
do with DAO.

Frankly, I am using Java more these days, but much hard to do it with
GUIs than Access is.

HTH,
Elaine

On Thu, 24 Mar 2005 17:04:38 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:
Elaine wrote:
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.


At the risk of hijacking the thread, I'd really appreciate any thoughts
on the following, related to Oracle/ADO.

I tried using ADO when I first went to A2003 last Fall, after many happy
years with A97 (sniff!) 8)

All of my Access 97 FE apps on Oracle databases use pass through queries
and my code constructs and modifies the Oracle SQL for these as
required. I use the pass through query objects in Access to act as row
sources for list and combo boxes and for record sources for forms, be
they datasheet or form view.

I could not figure out how to do this sort of thing with ADO. All I
could do with ADO was generate recordset objects.

Can you do the sort of thing I'm describing with ADO?

Thanks very much for any comment.


Nov 13 '05 #10

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

Similar topics

5
by: SerGioGio | last post by:
Hello, I am going nuts. I am trying to connect to my local ORACLE instance using ODBC. It used to work few weeks ago, but it fails now. Connection with: - SQL*plus: connection works! -...
4
by: Tig | last post by:
Hi all. I have a need to connect to an Oracle 7.3.3.5 database. I have a user who successfully connects to it with her Oracle 7.3 client. I have an Oracle 9.2 client installed on my machine....
3
by: premmehrotra | last post by:
I am using Access 2000 and Oracle 9.2.0.x on a Windows 2000. I have setup Oracle 9.2 ODBC Driver (I have not yet figured how to set Microsoft's Oracle ODBC driver). I am exporting a table from...
0
by: totierne | last post by:
comp.databases.ms-access, I want to know how to use Oracle views with session variables in Access. The parameterised views in access, are migrated to views with per session variables. The...
3
by: Andrew McGregor | last post by:
Hi, I am trying to get a VB.NET application to connect to a local Oracle 9i Lite database. What is the correct form for a connect string? cn = New...
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...
2
by: Ben | last post by:
Hi, I have a problem connecting to Oracle using and ODBC connection in a ASP.Net web page. The TNS Names works fine because when I create a DSN it works, and it works in SQL Plus. Here are...
6
by: BillCo | last post by:
I've reached the limit of my knowledge here and I'm starting to go mad - any help would be greatfully recieved!!!! I'm having a strange problem with making pass through queries to an oracle db...
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...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.