Solved

connect vb to oracle

Posted on 2002-06-24
13
322 Views
Last Modified: 2010-05-02
how can i connect vb to oracle using code?
my front end is vb?
then please suggest me answer.
0
Comment
Question by:vijay_bp
  • 4
  • 3
  • 2
  • +4
13 Comments
 
LVL 3

Accepted Solution

by:
Elmo_ earned 35 total points
ID: 7103388
Vijay,

Here are a couple of examples

I hope they help.

Cheers,

Ed.

    ' DAO Example (Data Access Objects)
    Dim wstemp As Workspace
    Dim dbtemp As Database
    Dim rstemp As Recordset
   
    Set wstemp = DBEngine.Workspaces(0)
    Set dbtemp = wstemp.OpenDatabase("", False, False, "ODBC;DSN=Oracle;USR=scott;PWD=tiger")
    Set rstemp = dbtemp.OpenRecordset(myquery.Text, dbOpenDynaset, dbSQLPassThrough)
         
    howmany = 0
    Combo1.Clear
    Do Until rstemp.EOF
       MsgBox rstemp(0)
       rstemp.movenext
       howmany = howmany + 1
    Loop

    '------------------------------------------------------------------------------------------
     
    ' DAO Example (Data Access Objects)
    Dim contemp As New rdoConnection
    Dim rstemp As rdoResultset
    Dim envtemp As rdoEnvironment
   
    Set envtemp = rdoEngine.rdoEnvironments(0)
    envtemp.CursorDriver = rdUseServer
   
    ' or rdUseOdbc, rdUseNone, rdUseIfNeeded, rdUseClientBatch
    With contemp
        .Connect = "ODBC;DSN=Oracle;USR=scott;PWD=tiger"
        .EstablishConnection rdDriverNoPrompt, False, rdoForwardOnly
        ' or rdoStatic, rdoKeyset, rdoDynamic
    End With
   
    Set rstemp = contemp.OpenResultset("select ...") ' Your SQL here
   
    howmany = 0
    With rstemp
    Do Until .EOF Or howmany > 2000
        MsgBox .rdoColumns(0)
        ' Give a message box of the 1st column
        .movenext
        howmany = howmany + 1
    Loop
       
     '------------------------------------------------------------------------------------------
    'ADO Example
    Dim conn As ADODB.Connection
    Dim rs As Recordset
    conn.Open "...", "...", "..."
    '           ^DSN   ^User  ^Password
    Set rs = conn.Execute("SELECT * FROM theTable")
    Do While Not rs.EOF
       MsgBox rs(i).Value
       rs.movenext
    Loop

0
 
LVL 5

Expert Comment

by:rkot2000
ID: 7104425


INFO: Visual Basic Accessing an Oracle Database Using ADO (Q176936)

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q176936

HOWTO: Retrieve Resultsets from Oracle Stored Procedures (Q174679)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q174679
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7106118
0
 

Expert Comment

by:paulll
ID: 7147673
To use ADO to connect to an Oracle database, you need to install Oracle Client.  MS makes an Oracle Provider but it will not open all the field types that Oracle has from versions 8i and up.
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7173809
vijay_bp,

Could you please close out this question?  You have been supplied with answers that should accomplish what you have asked for.


To date you have asked four questions and accepted answers for none of these.  If you do not close out your questions.  I will ask a moderator to do so.


Cheers,

Ed.
0
 

Expert Comment

by:paulll
ID: 7174263
Install Oracle client for the Oracle version you need to access.  Get it from Oracle.  Install as any user type (User, Programmer, etc.).

If an Oracle database is not set up with Oracle client install, search your orant/network/admin directory for "tnsnames.ora"

You need an entry such as:
TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server.orabox.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

Where "TEST" is the server name, you must know the server name.  DNS will not necessarily take care of this if Oracle is on a UNIX box, etc.
server.orabox.com is the address refecting the IP address, you must know the address.
1521 is the PORT, you must know the port.

In project references, choose "Microsoft ActiveX Data Objects 2.5" in Win2000 or
"Microsoft ActiveX Data Objects 2.1" and "Microsoft ActiveX Data Objects Recordset 2.1" in Win95/98

You need to install DCOM in Win95/98 also.  The Install Wizard will automatically include it.
-------------------------------------------------------
Private ConDB As ADODB.Connection
Private rs As ADODB.Recordset
Private ProviderType, ServerName As String, SQL_String As String
Private User As String, UserPassword As String

ProviderType = "Provider=OraOLEDB.Oracle" & User & UserPassword & ";Data Source=" & ServerName & ";DistribTx=0"
ConDB.Open ProviderType

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open SQL_String, ConDB, adOpenKeyset, adLockOptimistic, adCmdText

This will open an Oracle database if you have rights for the tables with the User and UserPassword set correctly.  I have never ever seen it fail.

Paul
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:Elmo_
ID: 7174291
To Paulll,

You should NEVER propose an Answer when there are already valid solutions for the questioner to review.

Even when experts give answers they still only submit them as comments.

These practices are listed in the EE Guidlines.

Please review them.

Cheers,

Ed.
0
 

Expert Comment

by:paulll
ID: 7174375
Sorry, I thought I'd try to be thorough and get him finished. I wondered why I almost never see any answers, just comments.  I'll stick to comments just to be safe.

Paul
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7174470
Much Appreciated.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7180150
Please maintain:

Questions Asked 4
Last 10 Grades Given  
Question Grading Record 0 Answers Graded / 0 Answers Received
0
 
LVL 1

Expert Comment

by:kodiakbear
ID: 7522849
Dear paulll
I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See: <http://www.experts-exchange.com/jsp/communityNews.jsp>
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

EXPERTS: I will return in seven days to close this question.
Please leave your thoughts and recommendations here

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Thanks !

kodiakbear
Experts Exchange Moderator
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7962908
Hi vijay_bp,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept Elmo_'s comment(s) as an answer.

vijay_bp, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 5

Expert Comment

by:Netminder
ID: 8023293
Per recommendation, force-accepted.

Netminder
EE Admin
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now