Restrictions in running Oracle calls via VBA in Excel? 1

[MCubitt]

When I run VBA from a macro in Excel, which runs a small Oracle statement, it connects, retrieves the data and closes the connection.

However, someone else tries it and they get:
Run-time error '440':
Unable to make connection, ORA-12154: TNS:could not resolve service name.

Yet they manage to connect to the DB using our ERP system.

Any ideas?





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[fredericofonseca]

yes.

Oracle services not started on the computer
sqlnet not configured correctly on said computer.



The ERP system may not be using the same connection type or name, thus works while yor VBA does not.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MCubitt]

thx

I guess using SQLplus to connect should be about as close as I can get, right?


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[fredericofonseca]

I was not being sarcastic at all.

You did not give enough information.

You said another user. Ok. Same machine? other machine? what OS used? what Excel version used? What oracle version used?

ORA-12154: TNS:could not resolve service name. is a message that normally happens with the services are not running correctly, the SQLNet is not correctly configured, or the connection string used is not correct.

You also don't say if you are using RDO/ADO/DAO or other type of connection. And are you using the Microsoft data provider or the Oracle provider?

And about SQLplus there was no mention to it had been used by that same user, so Maybe I could have said
"try with SQLPlus and see if it works." Even that may not prove anything.

As for ideas.

Get the smallest piece of code you can think that does the full openconnection/execute sql/get at least one returned recordset.

Get it to work on your user, and then try it on the other user. If this works then your code is at fault.

If it does not then enable full trace on Oracle netconfig, and then look at that file to see if you can figure out what is happening.

you may need to look at the trace file created with your user (which works) and the other user and see where do they start to differ.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MCubitt]

Fred,

Sorry my tag line may have confused you.. it's my signature - not a comment!

Anyway, thanks for your list of questions, quite rightly asked.

It is the same Excel file, different user, different machine.

Same OS same Excel version, Win 2000 and Excel 2000.

The method used for connecting is OO40..

Set objsession = _
  CreateObject("oracleinprocserver.xorasession")
  Set objdatabase = _
  objsession.opendatabase("IFSL", "user01/pass01", 0&)

  Set oradynaset = _
  objdatabase.dbcreatedynaset(selstr, 0&)

Now, the IFSL.. must that be an ODBC connection? I have a system DSN set up called IFSL, but the user MAY not (not checked yet).

I think this is probably it, but I though OO40 used miracles of modern science to connect, not ODBC calls. (as you can gather, my technical knowledge of PC - DB interconnectivity is limited!)







There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Hi again.

I checked the user's PC and they have no sych system DSN. I then renamed mine from IFSL to IFSLa but the spreadsheet still works.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

I even changed the DSN to be txt rather than Oracle and it still worked, so I am starting to think this is not an ODBC call.

But my knowledge is too limited... the tnsnames.ora and sqlnet.ora will be the same on my and the other PC.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

I logged into teh working PC as another user and it worked, but the SYSTEM DSN is shared so I guess I need to work on the user's PC. Always a problem.





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Excuse my muttering aloud.. I foud on orafaq.net (

http://www.orafaq.net/usenet/comp.databases.oracle.misc/1998/11/25/1003.htm):

I'm sorry to say that you're wrong. oo4o doesn't use ODBC.

So not ODBC!



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[fredericofonseca]

Hum. Never uses OO40 before (and I do use Oracle for a long time).

OO40 is not ODBC. It uses the OCI library (as all other products do, including OLEDB and ODBC drivers).


Any particular reasong why you are using OO40?


If using SQL Plus on that user and if you issue the connect command as

e.g. sqlplus /NOLOG
SQL > connect user01/pass01@IFSL

it should work AS LONG as the tnsnames.ora is correctly configured (correct values required.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MCubitt]

Well, you might be right, tns maybe wrong, the ERP system might handle variances I guess.

I am using OO40 because I wanted an easy way to run SQL and retrieve data from Oracle to Excel. OO40 was my Google'd advice

Thanks


Applications Support
UK

 

[fredericofonseca]

If you just need to do plain SQL, with or without PL/SQL then stick with ADO.

You will find a lot more code examples to deal with Oracle this way.

This is what I use by the way.


(In Dublin - Ireland)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MCubitt]

Doesn't that require an ODBC DSn to be defined on each machine that may run the Excel program? Something I am hoping to avoid.



Applications Support
UK

 

[fredericofonseca]

Not really.

You can use a dsnless connection.

see

http://www.able-consulting.com/ADO_Conn.htm

for examples.
oledb


* OLE DB Provider for Active Directory Service
* OLE DB Provider for Advantage
* OLE DB Provider for AS/400 (from IBM)
* OLE DB Provider for AS/400 and VSAM (from Microsoft)
* OLE DB Provider for Commerce Server
* OLE DB Provider for DB2
* OLE DB Provider for DTS Packages
* OLE DB Provider for Exchange
* OLE DB Provider for Excel
* OLD DB Provider for Internet Publishing
* OLE DB Provider for Index Server
* OLE DB Provider for Microsoft Jet
* OLE DB Provider for Microsoft Project
* OLE DB Provider for MySQL
* OLE DB Provider for ODBC Databases
* OLE DB Provider for OLAP Services
* OLE DB Provider for Oracle (from Microsoft)
* OLE DB Provider for Oracle (from Oracle)
* OLE DB Provider for Pervasive
* OLE DB Provider for Simple Provider
* OLE DB Provider for SQLBase
* OLE DB Provider for SQL Server
* OLE DB Provider for SQL Server via SQLXMLOLEDB
* OLE DB Provider for Sybase Adaptive Server Anywhere
* OLE DB Provider for Sybase Adaptive Server Enterprise
* OLE DB Provider for Text Files
* OLE DB Provider for UniData and UniVerse
* OLE DB Provider for Visual FoxPro

* OLE DB Provider for Active Directory Service

oConn.Open "Provider=ADSDSOObject;" & _
"User Id=myUsername;" & _
"Password=myPassword"

For more information, see: Microsoft OLE DB Provider for Microsoft Active Directory Service

To view Microsoft KB articles related to Data Link File, click here

* OLE DB Provider for Advantage

oConn.Open "Provider=Advantage OLE DB Provider;" & _
"Data source=c:\myDbfTableDir;" & _
"ServerType=ADS_LOCAL_SERVER;" & _
"TableType=ADS_CDX"

For more information, see: Advantage OLE DB Provider (for ADO)

* OLE DB Provider for AS/400 (from IBM)

oConn.Open "Provider=IBMDA400;" & _
"Data source=myAS400;" & _
"User Id=myUsername;" & _
"Password=myPassword"

For more information, see: A Fast Path to AS/400 Client/Server

* OLE DB Provider for AS/400 and VSAM (from Microsoft)

oConn.Open "Provider=SNAOLEDB;" & _
"Data source=myAS400;" & _
"User Id=myUsername;" & _
"Password=myPassword"

For more information, see: ConnectionString Property

To view Microsoft KB articles related to OLE DB Provider for AS/400 and VSAM, click here

* OLE DB Provider for Commerce Server

For Data Warehouse

oConn.Open "Provider=Commerce.DSO.1;" & _
"Data Source=mscop://InProcConn/Server=mySrvName:" & _
"Catalog=DWSchemaatabase=myDBname:" & _
"User=myUsernameassword=myPassword:" & _
"FastLoad=True"

' Or

oConn.Open "URL=mscop://InProcConn/Server=myServerName:" & _
"Database=myDBname:Catalog=DWSchema:" & _
"User=myUsernameassword=myPassword:" & _
"FastLoad=True"

For Profiling System

oConn.Open "Provider=Commerce.DSO.1;" & _
"Data Source=mscop://InProcConn/Server=mySrvName:" & _
"Catalog=Profile Definitionsatabase=myDBname:" & _
"User=myUsernameassword=myPassword"

' Or

oConn.Open _
"URL=mscop://InProcConnect/Server=myServerName:" & _
"Database=myDBname:Catalog=Profile Definitions:" & _
"User=myUsernameassword=myPassword"

For more information, see: OLE DB Provider for Commerce Server, DataWarehouse, and Profiling System

To view Microsoft KB articles related to OLE DB Provider for Commerce Server, click here

* OLE DB Provider for DB2 (from Microsoft)

For TCP/IP connections

oConn.Open = "Provider=DB2OLEDB;" & _
"Network Transport Library=TCPIP;" & _
"Network Address=xxx.xxx.xxx.xxx;" & _
"Initial Catalog=MyCatalog;" & _
"Package Collection=MyPackageCollection;" & _
"Default Schema=MySchema;" & _
"User ID=MyUsername;" & _
"Password=MyPassword"

For APPC connections

oConn.Open = "Provider=DB2OLEDB;" & _
"APPC Local LU Alias=MyLocalLUAlias;" & _
"APPC Remote LU Alias=MyRemoteLUAlias;" & _
"Initial Catalog=MyCatalog;" & _
"Package Collection=MyPackageCollection;" & _
"Default Schema=MySchema;" & _
"User ID=MyUsername;" & _
"Password=MyPassword"

For more information, see: ConnectionString Property, and Q218590

To view Microsoft KB articles related to OLE DB Provider for DB2, click here

* OLE DB Provider for DTS Packages

The Microsoft OLE DB Provider for DTS Packages is a read-only provider that exposes Data Transformation Services Package Data Source Objects.

oConn.Open = "Provider=DTSPackageDSO;" & _
"Data Source=mydatasource"

For more information, see: OLE DB Providers Tested with SQL Server

To view Microsoft KB articles related to OLE DB Provider for DTS Packages, click here

* OLE DB Provider for Exchange

oConn.Provider = "EXOLEDB.DataSource"
oConn.Open = "

http://myServerName/myVirtualRootName"

For more information, see: Exchange OLE DB Provider, Messaging, Calendaring, Contacts, and Exchange using ADO objects

To view Microsoft KB articles related to OLE DB Provider for Exchange, click here

* OLE DB Provider for Excel

Currently Excel does not have an OLE DB Provider.

However, you can use the ODBC Driver for Excel.

Or use the OLE DB Provider for JET to read and write data
in an Excel workbook.

* OLE DB Provider for Index Server

oConn.Open "Provider=MSIDXS;" & _
"Data source=MyCatalog"


For more information, see: Microsoft OLE DB Provider for Microsoft Indexing Service

To view Microsoft KB articles related to OLE DB Provider for Index Server, click here

* OLE DB Provider for Internet Publishing

oConn.Open "Provider=MSDAIPP.DSO;" & _
"Data Source=

http://mywebsite/myDir;"

& _
"User Id=myUsername;" & _
"Password=myPassword"

' Or

oConn.Open "URL=http://mywebsite/myDir;" & _
"User Id=myUsername;" & _
"Password=myPassword"

For more information, see: Microsoft OLE DB Provider for Internet Publishing and Q245359

To view Microsoft KB articles related to OLE DB Provider for Internet Publishing, click here

* OLE DB Provider for Microsoft Jet

For standard security

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\myDb.mdb;" & _
"User Id=admin;" & _
"Password="

If using a Workgroup (System Database)

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw", _
"myUsername", "myPassword"

Note, remember to convert both the MDB and the MDW to the 4.0
database format when using the 4.0 OLE DB Provider.


If MDB has a database password

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDBatabase Password=MyDbPassword", _
"myUsername", "myPassword"

If want to open up the MDB exclusively

oConn.Mode = adModeShareExclusive
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\myDb.mdb;" & _
"User Id=admin;" & _
"Password="

If MDB is located on a network share

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\myServer\myShare\myPath\myDb.mdb"

If MDB is located on a remote machine

- Or use an XML Web Service via SOAP Toolkit or ASP.NET
- Or upgrade to SQL Server and use an IP connection string
- Or use an ADO URL with a remote ASP web page
- Or use a MS Remote or RDS connection string


If you don't know the path to the MDB (using ASP)

<% ' ASP server-side code
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(".") & "\myDb.mdb;" & _
"User Id=admin;" & _
"Password="
%>

This assumes the MDB is in the same directory where the ASP page is running. Also make sure this directory has Write permissions for the user account.


If you don't know the path to the MDB (using VB)

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\myDb.mdb;" & _
"User Id=admin;" & _
"Password="

This assumes the MDB is in the same directory where the application is running.

For more information, see: OLE DB Provider for Microsoft Jet, Q191754, and Q225048

Note: Microsoft.Jet.OLEDB.3.51 only gets installed by MDAC 2.0. Q197902
Note: MDAC 2.6 and 2.7 do not contain any of the JET components. Q271908 and Q239114

To view Microsoft KB articles related to OLE DB Provider for Microsoft JET, click here



You can also open an Excel Spreadsheet using the JET OLE DB Provider

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mySpreadsheet.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""

Where "HDR=Yes" means that there is a header row in the cell range
(or named range), so the provider will not include the first row of the
selection into the recordset. If "HDR=No", then the provider will include
the first row of the cell range (or named ranged) into the recordset.

For more information, see: Q278973



You can also open a Text file using the JET OLE DB Provider

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""

'Then open a recordset based on a select on the actual file

oRs.Open "Select * From MyTextFile.txt", oConn, _
adOpenStatic, adLockReadOnly, adCmdText

For more information, see: Q262537

* OLE DB Provider for Microsoft Project

oConn.Open "Provider=Microsoft.Project.OLEDB.9.0;" & _
"Project Name=c:\somepath\myProject.mpp"

For more information, see: Microsoft Project 2000 OLE DB Provider Information

To view Microsoft KB articles related to OLE DB Provider for Microsoft Project, click here

* OLE DB Provider for mySQL

oConn.Open "Provider=MySQLProv;" & _
"Data Source=mySQLDB;" & _
"User Id=myUsername;" & _
"Password=myPassword"

For more information, see: API - OLE DB and Snippet

* OLE DB Provider for ODBC Databases

WARNING: This OLE DB Provider is considered obsolete by Microsoft!

For Access (Jet)

oConn.Open "Provider=MSDASQL;" & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

For SQL Server

oConn.Open "Provider=MSDASQL;" & _
"Driver={SQL Server};" & _
"Server=myServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

For more information, see: Microsoft OLE DB Provider for ODBC

To view Microsoft KB articles related to OLE DB Provider for ODBC, click here

* OLE DB Provider for OLAP Services

Microsoft OLE DB for Online Analytical Processing (OLAP) is a set of
objects and interfaces that extends the ability of OLE DB to provide
access to multidimensional data stores.

For ADOMD.Catalog

oCat.ActiveConnection = _
"Provider=MSOLAP;" & _
"Data Source=myOLAPServerName;" & _
"Initial Catalog=myOLAPDatabaseName"

For ADOMD.Catalog (with URL)

oCat.ActiveConnection = _
"Provider=MSOLAP;" & _
"Data Source=

http://myServerName/;"

& _
"Initial Catalog=myOLAPDatabaseName"

For Excel PivotTable

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "OLEDB;" & _
"Provider=MSOLAP;" & _
"Location=myServerDataLocation;" & _
"Initial Catalog=myOLAPDatabaseName"
.MaintainConnection = True
.CreatePivotTable TableDestination:=Range("A1"), _
TableName:= "MyPivotTableName"
End With

For more information, see: OLE DB for OLAP, Catalog Object, PivotTable, Connecting Using HTTP

To view Microsoft KB articles related to OLE DB Provider for OLAP Services, click here

* OLE DB Provider for Oracle (from Microsoft)

oConn.Open "Provider=msdaora;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword"

For more information, see: Microsoft OLE DB Provider for Oracle

To view Microsoft KB articles related to OLE DB Provider for Oracle, click here

* OLE DB Provider for Oracle (from Oracle)

For Standard Security

oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword"

For a Trusted Connection

oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=/;" & _
"Password="
' Or

oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"OSAuthent=1"

Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.


odbc
oConn.Open "Driver={Oracle ODBC Driver};" & _
"Dbq=myDBName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

Where: The DBQ name must be defined in the tnsnames.ora file



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MCubitt]

Fred,

Thanks for that info, I did give it a go and could not get that to work.. but I think I'll retain the OO40 since it is probably the preferred method for Oracle from VBA...

From Oracle9i Application Developer's Guide - Fundamentals
Release 2 (9.2):
"OO4O provides key features for accessing Oracle databases efficiently and easily in environments ranging from the typical two-tier client/server applications, such as those developed in Visual Basic or Excel, to application servers deployed in multitiered application server environments such as web server applications in Microsoft Internet Information Server (IIS) or Microsoft Transaction Server (MTS)."

I just have to fathom out why it doesnt work on one machine but does another!





Applications Support
UK

 

[MCubitt]

Okay, too many random errors with OO40 and clearly a lack of understanding from the "public"!

Therefore, the tried and tested ADO might be a better solution.

However, I cannot get started there!

My 1st line errors:
Set oConn = Server.CreateObject("ADODB.Connection")

Run-time error '424';
Object required

Grr!!!




Applications Support
UK

 

[fredericofonseca]

what's that "Server."??

this works with me
Sub ax()
Dim ad As Object
Set ad = CreateObject("ADODB.CONNECTION")

End Sub


And do you also have a particular reason to use late binding instead of early binding?
If not then use early binding.
If you do, I would still advise you to use early binding while developing as you will benefit from having all methods/properties available while coding (help wise).




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MCubitt]

To be honest, as you may have gathered, I am coming into this blind.

I am copying other people's examples, etc B'cos I have no time to completely understand it (never a good thing!)

Anyway, thank you! It worked!

Private Sub CommandButton1_Click()
Dim oconn As Object

Set oconn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")
connStr = "Driver={Microsoft ODBC for Oracle}; Server=dbname; UID=user; PWD=pass"
oconn.Open connStr
oRs.Open "SELECT * FROM fnd_user_tab where identity like 'I%'", oconn, 1, 1, 1
For i = 0 To oRs.fields.Count - 1
MsgBox (oRs.fields(i).Name & ":" & oRs.fields(i).Value)
Next i
oRs.Close
Set oRs = Nothing
End Sub

Now I can progress!



Applications Support
UK

 

[MCubitt]

Just a quick extra Q if I may...

The 1,1,1 .. I suspect they link to the "Bind" you mention.

Briefly, what do they each represent, and what should I use, for a purely READ ONLY query (getting list of values for populating combo box)

Thank you


Applications Support
UK

 

[MCubitt]

I managed to work it out!




Applications Support
UK