Word connecting to Oracle 1

[ricaforrica]

Greetings!

I'm looking for a way of connecting Word with an Oracle database. The idea would be to press a button in a Word report and validate username and password from a table.

I've already inserted a table successfully with the InsertDatabase and MicrosoftQuery, but I need to implement a tranparent process to the end-user. So, only when they will press the button, the connection will be made. I believe a macro OnClick would be the better solution...

I tried using DAO objects with the following code:

Dim ws As Workspace
Dim db As Database
Dim LConnect As String

On Error GoTo Err_Execute

'Use {Microsoft ODBC for Oracle} ODBC connection
LConnect = "ODBC;UID=xxx;PWD=xxx;SERVER=server"

'Point to the current workspace
Set ws = DBEngine.Workspaces(0)

'Connect to Oracle
Set db = ws.OpenDatabase("", False, True, LConnect)
db.Close
OracleConnect = True

Err_Execute:
MsgBox "Connecting to Oracle failed."
OracleConnect = False

When I execute this, a dialog is opened so that I can choose the dataSource (DSN). I want the all process to execute without the user noticiing it, so I tried to define the DataSource:

LConnect = "ODBC;DSN=test;UID=xxx;PWD=xxx;SERVER=server"

But I get this error:

Runtime error 3151
ODBC--connection to 'test' failed

Why do I have to choose a datasource? The connection to the database isn't enough?

I also tried with Oracle Objects for OLE (OO4O), with the following code:

Dim OraSession As Object
Dim OraDatabase As Object
Dim OraDynaset As Object

On Error GoTo Err_Execute

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("server", "user/pass", 1&)

MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName
MsgBox "OO4O Version: " & OraSession.OIPVersionNumber
MsgBox "Oracle Version: " & OraDatabase.RDBMSVersion

Err_Execute:
MsgBox "Connecting to Oracle failed."


When I press the button nothing happens!

Can someone give me a hand on this???
Thanks in advance!

Ricardo Pinto

 

[TonyJollans]

Hi Ricardo,

I haven't looked at the code but can you give a bit more detail about the button. How have you (tried to) connect it to the code? What is the procedure called and where is it? And, to be sure we're talking about the same thing) is it an ActiveX button from the control Toolbox, an icon on a toolbar, or a MacroButton Field, or what?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fstrahberger]

Hi,

try this to connect to an oracle instance

'---------------------------------------------------------
' Include "Microsoft ActiveX DataObjects 2.7"
' with Extras --> Addins
'---------------------------------------------------------
Sub TestIt()
    '--------------------------------------
    ' create and open connection
    '--------------------------------------
    Dim oCn As New ADODB.Connection
    oCn.Open "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=xxx;uid=xxx;pwd=xxx;"
    '---------------------------------------
    ' open recordset
    '---------------------------------------
    Dim oRs As New ADODB.Recordset
    Set oRs = oCn.Execute("SELECT xxx FROM xxx")
    While Not oRs.EOF
        Debug.Print oRs.Fields(0).Value
        oRs.MoveNext
    Wend
    '---------------------------------------
    ' cleanup
    '---------------------------------------
    oCn.Close
    Set oRs = Nothing
    Set oCn = Nothing
End Sub

You will also need to define the oracle instance in the file tnsnames.ora.

 

[fredericofonseca]

http://www.carlprothman.net/Default.aspx?tabid=81

will give some further info.


But the following works

Option Explicit
Sub Main()
Dim DBConn As ADODB.connection
Dim DBRS As ADODB.Recordset
Dim dbconnstring As String

Set DBConn = New ADODB.connection
DBConn.CursorLocation = adUseClient
DBConn.Mode = adModeReadWrite
dbconnstring = "Provider=OraOLEDB.Oracle;DataSource=ORCL;User Id=frederico;Password=frede1;database=factucli;"
DBConn.Open dbconnstring
Set DBRS = New ADODB.Recordset
DBRS.Open "select * from paises", DBConn
Debug.Print DBRS.Fields.Count


End Sub



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[ricaforrica]

Hello Tony,

What I'm using is an ActiveX button from the control Toolbox, but I believe it could be a MacroButton Field, as well.

I placed the code above on the CommandButton1_Click() method and I'm using Visual Basic to connect to the database.

Hope I clarified your doubts!
Regards,

Ricardo

 

[ricaforrica]

Hello fstrahberger!

I tried out your code, but I get the following error:

"Run-time error '-2147467259 (80004005)'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-12535: TNS: Operation timed out"

Do you know what might be causing this?

I don't understand when you say: ' Include "Microsoft ActiveX DataObjects 2.7" with Extras --> Addins
I included the reference to ADO, but where do I define these Addins?

Finally, the definition of the instance in tnsnames.ora:
I'm connecting to "CONNECTSTRING=mydatabase;uid=xxx;pwd=xxx;" and I have in my tnsnames file the following:

MYDATABASE.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = portnumber))
)
(CONNECT_DATA = (SID = mydatabse))
)

Do I need anything else (I' using this database with my application and compiling the forms in Oracle Forms, so I believe the connection is working)???

Thanks for your help!

Ricardo

 

[ricaforrica]

Hello Frederico!

I also tried out your code, but I get the following error:

"Run-time error '3706'
Provider cannot be found. It may not be properly installed"

Must I add a specific reference to execute this???

In the following instruction

"Provider=OraOLEDB.Oracle;DataSource=ORCL;User Id=gh_pc;Password=gh_pc;database=hsdemo;"

what is the meaning of the datasource???

Thanks for your support!

Ricardo

PS: Português?? Brasileiro??

 

[TonyJollans]

Hi Ricardo,

It can't be both an ActiveX button AND a macrobutton!

Assuming it's an ACtiveX, the routine appears to have the right name - is it in the right place? When you say that nothing happens do you mean that the macro doesn't run at all?

Gone back and looked at the code - do you not even get a message box in the last bit of code?

Also, I'm a bit out of practice with Access but are you mixing DAO and ADO objects?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[ricaforrica]

Thanks Tony for your quick answers

I believe the macro is running, as I got a response with some of the examples I tried. Only with OO4O nothing happened... not even a message box!

Well, I think I tried both ADO and DAO, as I got some examples with both providers. For now, as long as it works, I could use any of them.

And hey!: I'm not using Access! It'a MSWord document
Hope to hear from you soon!

Ricardo.

 

[fredericofonseca]

what version of the Oracle client have you installed on your computer?

regarding the error

OraOLEDB.Oracle is the OLEDB from Oracle. If you have a old version of Oracle you may not have this one and you should then look at the link I supplied and use the ODBC connection string instead.

ORCL is your Oracle instance name. This is the default one, but again by following the link I provided you will see where it is defined. Yours may be different so speak with your DBA if you are unsure.

You can also verify this through the Net manager software supplied with your Oracle instalation.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[ricaforrica]

Hello everybody!

Thanks for your help! I connected to the database successfully...


I hope to post often in this forum!
Best regards,

Ricardo Pinto.