Solved

connect vb to oracle

Posted on 2002-06-24
13
324 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 70

Expert Comment

by:Éric Moreau
ID: 7106118
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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
 
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 70

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

820 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