Tutorial :Setting Up an ODBC Data Source for Oracle using vbscript



Question:

I am trying to connect to oracle database from excel. This is working fine when I manually create the DSN, but now I want to create DSN programmtically using VB. Please help me I am new to vbscripting.


Solution:1

Here is some sample code:

Dim objConn  Set objConn = CreateObject("ADODB.Connection")    Dim connString  connString = "YOUR ORACLE CONNECTION STRING HERE!"    objConn.Open connString    Dim objRS  Dim strSQL  strSQL = "SELECT * FROM YourTable"    Set objRS = objConn.Execute(strSQL)  If objRS.EOF Then      ' No Records Returned  Else      Do               ' Do what you want with your output          objRS.MoveNext      Loop Until objRS.EOF  End If  End With    objRS.Close  Set objRS = Nothing      objConn.Close  Set objConn = Nothing  

Use the Oracle Connection Strings page to find your connection string. This is VBScript, for VB you may have to make a few changes such as defining your variable types.

Dim strSQL As String  


Solution:2

Here is an example of how you can use Oracle Objects for OLE Automation with Excel:

http://download.oracle.com/docs/cd/B28359_01/win.111/b28378/clients.htm#CIHFAHJJ

Example from the link above:

...   ' Declare variables   Dim OraSession As OraSession   Dim OraDatabase As OraDatabase   Dim OraDynaset As OraDynaset   Dim OraFields As OraFields    ' Create the OraSession Object. The argument to CreateObject is the   ' name by which the OraSession object is known to the OLE system.   Set OraSession = CreateObject("OracleInProcServer.XOraSession")     ' Create the OraDatabase Object by opening a connection to Oracle.  Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)     ' Create the OraDynaset Object.   Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)     ' You can now display or manipulate the data in the dynaset. For example:   Set OraFields = OraDynaset.fields   OraDynaset.movefirst   Do While Not OraDynaset.EOF       gMsgBox OraFields("ename").Value       OraDynaset.movenext   Loop   

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »