databse connection in macro

[#1]

Hi,

I want to connect to oracle database in macro. Can anybody please tell me how do i do this??

Thanks in advance!!!

[#2]

hi kavita,

try to using vb script

Dim strCon

strCon = "Driver={Oracle in Oracle in Orahome10g}; " & _

"CONNECTSTRING=(DESCRIPTION=" & _

"(ADDRESS=(PROTOCOL=TCP)" & _

"(HOST=SERVERNAME)(PORT=1521))" & _

"(CONNECT_DATA=(SERVICE_NAME=TEST))); uid=username;pwd=password;"

Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")

Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")

oCon.Open strCon

Set oRs = oCon.Execute("SELECT 1 from dual")

While Not oRs.EOF

WSCript.Echo oRs.Fields(0).Value

oRs.MoveNext

Wend

oCon.Close

Set oRs = Nothing

Set oCon = Nothing

[#3]

Hi Dushan,

Thanks a lot for your reply..

"Oracle in Oracle in Orahome10g"--> is this driver name ??

And where do you give the IP address of database server?

[#4]

Hi,

yes it is and HOST=SERVERNAME. there you can Place your Server IP.

Best rgrds,

Dushan

[#5]

hi,

replace DRIVER, SERVERNAME, SERVICE_NAME, username and password.

Best rgrds,

Dushan

[#6]

Hi Dushan,

I have written following statement but there is some problem in what I have written can you please help me to correct it as I'm new to this.

strCon = "Driver={Oracle in OraDb 10g_home1}; " & "CONNECTSTRING=(DESCRIPTION=" & "(ADDRESS=(PROTOCOL=TCP)" & "(HOST=127.0.0.1)(PORT=1521))" & "(CONNECT_DATA=(SERVICE_NAME=DB11G))); uid=usr;pwd=pwd;"

[#7]

hi,

what s the error u are having?

[#8]

Edit macro dialog box opens every time I try running the macro by clicking on it.

[#9]

hi,

select allow system access and click ok

[#10]

Yes I have done it. Still its not working.