VBA And Oracle

[iuridicus 0]

Hello everyone,I need to connect directly my Excel VBA macro to Oracle DB using ODBC. I can do it by creating a query table and when executed, copying required data from the worksheet, where they have been loaded, to an array in memory. I would like to know a easiest and more efficient way to do it. In the manual there is no functionable info about odbc connections.Thanks

[marsden 0]

Hi iuridicus,

I searched google for such a thing and came up with this article about connecting excel to an oracle database.

Developers can use the OO4O In-Process Automation Server to connect to Oracle database servers and execute SQL or PL/SQL procedures through COM Automation Objects. You can also use the In-Process Server by creating a COM object for a session from its interface, "OracleInProcServer.XOraSession," and accessing subobjects, as shown in this example using Visual Basic:

Set objSession = CreateObject("OracleInProcServer.XOraSession")Set objDatabase = objSession.OpenDatabase("","scott/tiger",0) 

With a database object, you can query records from the database or execute DDL or DML statements directly. For example, here's a VBA script that you can run from inside Microsoft Excel to fetch all the data from the EMP table into worksheet cells:

Sub GetEmployees()	' Use OO4O	Set objSession = CreateObject("OracleInProcServer.XOraSession")	Set objDatabase = objSession.OpenDatabase("", "scott/tiger", 0)	Sql = "select * from emp"	Set oraDynaSet = objDatabase.DBCreateDynaset(Sql, 0)	If oraDynaSet.RecordCount > 0 Then		oraDynaSet.MoveFirst		For x = 0 To oraDynaSet.Fields.Count - 1			Cells(1, x + 1) = oraDynaSet.Fields(x).Name			Cells(1, x + 1).Format = Bold		Next		For y = 0 To oraDynaSet.RecordCount - 1			For x = 0 To oraDynaSet.Fields.Count - 1				Cells(y + 2, x + 1) = oraDynaSet.Fields(x).Value			Next			oraDynaSet.MoveNext		Next	End If	Set objSession = Nothing	Set objDatabase = NothingEnd Sub 

While it's possible to fetch data from an external datasource into an Excel spreadsheet through the Data/External Datasource function, this macro has much more direct control, allowing you to specify exactly how the data is read into the spreadsheet; and, because the macro doesn't use as many layers to get to the Oracle database, it should be faster. It also provides additional functionality, including the ability to store and retrieve blobs (such as images).

Here is the link to that article.

<<http://forums.xisto.com/no_longer_exists/;

Hope this helps.

[iGuest 3]

Great Help! The connection works just as well with Ms Word-I guess obviously because most general VBA statement work across all MS Suite applications...word, excel, powerpoint... The beauty is that the Scott/tiger username/password combination can connect to any oracle db so long as the dBA has not deliberately disabled the use privileges. therefore you do not have to disclose higher privileged username and password on the VBA code.-David

[iGuest 3]

Name:are S PaiEmail:rspai9@gmail.ComOpinion/Suggestion *:I used the sub GetEmployees() on another table successfully. I added a line to restrict the rows to 500 viz. "If y>500" in the loop. This worked fine. Next I increased the rows to 1000. The code successfully retrieved 788 rows and stopped. I tried several times. Each time it stops at 788 rows saying that it is an object or application defined error. When debug button in the error message is clicked it stops at this line: Cells(y + 2, x + 1) = oraDynaSet.Fields(x).ValuePlease throw light on this. Thanks in advance for the help.

[yordan 10]

The beauty is that the Scott/tiger username/password combination can connect to any oracle db so long as the dBA has not deliberately disabled the use privileges.

That was the case some years ago. Today, the scott/tiger demo is not automatically installed. It is not installed but can be installed in the Oracle 9 systems : the database administrator has to manually create the scott/user and start the demobld.sql script in order to build the demo.In Oracle 10 installations, the demo is even not present on the Oracle 10 cd. If the administrator wants to create this demo, he has to type "grant connect to scott identified by tiger" in a sql window, and then he has to ftp the demobld.sql from an old computer, connect scott/tiger, and "start demobld".
If the database administrator does all these things, that means he really wants you to connect to his database. And, by the way, you can connect only with scott's privileges, you can only see your own tables ; and if you do a mistake, you are the only damaged guy.
So, the scott/tiger username/password allows you to access only to sites where people like me allow you to go, in order to help you play with Oracle before being grown enough and be independant.

[iGuest 3]

Issue with connection to Oracle 10g database.

VBA And Oracle

 

Hi

 

I have a tool that connects to an Oracle database. Previously this database was an Oracle 9i database but it has now been upgraded to 10g.

 

This has made my code to fail. I understand that installing a new driver on my PC will make the code to work again (ofcourse I will have to use this driver in my code)

 

Also quite interesting, there are 2 databases (both have been upgraded). I can still connect to one but I am having problems connecting to the other. (very dodgy since the code worked perfectly well for several months before the upgrade)

 

Any clues or solutions to make this work?

 

Thanks

 

-reply by Murtaza

[iGuest 3]

Problem with querying from Excel 2007 macro to Oracle.

VBA And Oracle

 

Hello,

 

My company is migrating to Excel 2007 and I have macros created in Excel 2003 that query Oracle 10i. I ran the macro in 2007 and the query no longer runs. I get an "ORA-12154: tns: could not resolve the connect identifier specified"

 

Thanks Here is the code I have used for years.

Sub actual_data_query()

 

Dim conADOConnection As Connection

Dim strConnect As String

Dim rstData As New Recordset

Dim strSQL As String

 

' the connection string

strConnect = "Provider=MSDAORA.1;Password=xxxxx;User ID=lxxxx;Data Source=xxxxx;Persist Security Info=True"

 

'select data tab

data.Activate

Cells.Select

Selection.Clear

Range("A2").Select

 

strSQL = "SELECT ...

 

 

'open the connection and run the query

 

rstData.Open strSQL, strConnect, adOpenForwardOnly

 

'loop through the data

 

With rstData

 

 

-reply by larry

[iGuest 3]

Problem in inserting into table via macro

VBA And Oracle

 

Hi,

 

I am trying to insert a row into my existing table via macro but I am unable to do so. When I run a select statement the same gets executed sucessfully but doesnt inserts a row. Please can you help?

 

Yash

 

-reply by Yashodhan Sarpotdar

[iGuest 3]

Problem in inserting into table via macro

VBA And Oracle

 

Hi,

 

I am trying to insert a row into a table via macro, but unable to do so. When I try to run select statement it executed sucessfully but, when I try to insert a row the same is not done. Can anybody please help?

 

Yash

[iGuest 3]

Oracle Connection if Supported by Oracle 10g clientVBA And Oracle

Good day

 May asked if statement below is applicable in ORACLE 10g client?, Thanks

Set objSession = CreateObject("OracleInProcServer.XOraSession")Set objDatabase = objSession.OpenDatabase("","scott/tiger",0)

-question by frederick zacarias