How To Connect Oracle Using Odbc

Hai all.

I request your valuable help.

I have VB 6.0 and Oracle 8 personnel edition in my system. I would like access oracle database using ODBC. I tried by creating a user DSN through Windows Cotrol Panel. Then while accessing through VB it asks SQL Server login. SQL server is not there. There it terminates. Can someone tell me the exact procedure starting from the very begining, for connecting the Oracle database through ODBC and how to access the data.

Thanking You.

You also need to setup the connection using the Net8 assistant.

Basically all the ODBC Connection is, is a passthrough to the Net8 setup.

Once you do that you can use ADO to connect to the database using the ADO.Connection object. To get data from the database you can use the ADO.Recordset object.

Here is a link to a good site on connection strings:
http://www.able-consulting.com/ADO_Conn.htm

Hope this helps,

VB Code:

Option Explicit
 
Const DB_NAME = "yourdatasource"
Const DB_UID = "youruserID"
Const DB_PWD = "yourpassword"
 
Dim conAVB As ADODB.Connection
Dim yourTable As ADODB.Recordset
-----------------
 
'Create the connection
    Dim strSQL  As String
    Set conAVB = New ADODB.Connection
    
    On Error GoTo HandleErrors
    conAVB.ConnectionString = "Provider=MSDAORA;" & _
            "Password=" & DB_PWD & _
            ";User ID=" & DB_UID & _
            ";Data Source=" & DB_NAME & _
            ";Persist Security Info=True"
    conAVB.Open
 
    Set YourTable= New ADODB.Recordset
    strSQL = "Select * from yourTable"
    Set YourTable = conAVB.Execute(strSQL, , adCmdText)

it must be made clear that vbvbvb's suggestion is not an ODBC connection - MSDAORA is an OLEDB provider.

..which is the better way of connecting to an Oracle database over ODBC as it has one less layer, and doesn't require you to set up a system DSN.

FYI, there is another way to access Oracle data, that has even less layers than OLEDB - Oracle own OO4O. I never use it though....

Gaffer, is this the way you are talking about?

VB Code:

Private Session As Object 'OracleInProcServer.OraSession
Private Database As Object 'OracleInProcServer.OraDatabase
Private dbTable As Object 'OracleInProcServer.OraDynaset
Private Const ORADB_NOWAIT = &H2&
Private Const ORADYN_DEFAULT = &H0&
 
Const UserName = " ?"
Const Password = "?"
Const DatabaseName = "?"
-------------------
 
    Dim a$
    Dim strSQL As String
 
    Set Database = Nothing
    a$ = UserName & "/" & Password
    Set Session = CreateObject("OracleInProcServer.XOraSession")
    Set Database = Session.OpenDatabase(DatabaseName, a$, ORADB_NOWAIT)
    
    Set dbTable = Nothing
    strSQL = "SELECT * from yourtable"
    Set dbTable = Database.CreateDynaset(strSQL, ORADYN_DEFAULT)

That's the boy. As I said, I never used OO4O in a completed project as it's benefits seem negible. It's benefits are:

a) Direct Access to Oracle Call Interface (whereas ADO uses OLE DB as an extra layer). Probably not going to make much of a performance difference
b) It's built for Oracle Stored Procedures. But I always found that ADO give me everythign I need
c) It's built for Oracle - say what you want about ADO being multi databse compatible - it was really built for SQL Server. However, again, you can usually do your work competantly enough using ADO & Oracle

However, ADO & OLEDB is used by almost everyone now, is asynchronous (OO4O isn't), and OLEDB is much quicker than ODBC....