Oracle connection problems; Odbc call failed

[mcbill20]

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\ora10 g\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_CONNECT ION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill

[MGFoster]

There are some connection string examples here:

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

--
MGFoster:::mgf0 0 <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\ora10 g\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_CONNECT ION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill

[Tim Marshall]

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

[Elaine]

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.Connectio n ' 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(strUse r As String, strPwd As String, strSvc
As String)
' open db connection
Set conn = New ADODB.Connectio n
'
conn.Provider = ADODB_PROVIDER ' = OraOLEDB.Oracle
conn.Open strSvc, strUser, strPwd
'
End Function
'
Public Function OpenRsA(connLiv e As ADODB.Connectio n, strSQL As
String)
' open recordset A, static
Set rsA = New ADODB.Recordset
'
rsA.CursorLocat ion = adUseClient
rsA.Open strSQL, connLive, adOpenStatic, adLockOptimisti c
'
End Function
'
Public Function OpenRsB(connLiv e As ADODB.Connectio n, strSQL As
String)
' open recordset B, dynamic
Set rsB = New ADODB.Recordset
'
rsB.CursorLocat ion = adUseClient
rsB.Open strSQL, connLive, adOpenDynamic, adLockOptimisti c
'
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\ora1 0g\NETWORK\ADMI N\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_CONNEC TION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill

[Tim Marshall]

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

[MGFoster]

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkNd2IechKq OuFEgEQKW7wCeJv IYz9KeiY5GRJ43g EoaJOUHIioAnRyQ
g1KrtnWnPv5Djs1 1YaIZOhAZ
=o1sH
-----END PGP SIGNATURE-----

[Tim Marshall]

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

[MGFoster]

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkN4kIechKq OuFEgEQIFGgCgtv JZCn16ulH0QJngH y31n+FGAT4AoM0C
tOFtpNLEwNnppHY vl00WmbfL
=den/
-----END PGP SIGNATURE-----

[mcbill20]

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

[Elaine]

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****@PurpleP andaChasers.Moe rtherium> 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.