Re: Connecting to Oracle RDBMS from VBasic

From: <effler_at_yahoo.com>
Date: 2000/04/12
Message-ID: <8d1bs8$sc6$1@nnrp1.deja.com>#1/1

This is my Code
it works fine in VB5

'Global vars
Dim wrkMain As Workspace
Dim conPubs As Connection
Dim dbspUBS As Database
Dim ODBC_Verbindung_aufgebaut As String

---

'establish ODBC Connection
Public Sub ODBC_Verbindung_aufbauen(Connect, UID, PWD As String) Dim ORA_Nummer As String
Dim odbcCONNECT, msg, message As String
Dim i As Integer
On Error GoTo ODBC_Verbindung_aufbauen_error odbcCONNECT = "ODBC;DATABASE=" & Connect & ";UID=" & UID & ";PWD=" & PWD & ";DSN=ODBC-Source"

    ' ODBCDirect Workspace-Objekt erstellen.     Set wrkMain = CreateWorkspace("", "Admin", "", dbUseODBC)     ' Connection-Objekt basierend auf den Informationen     ' in der Verbindungszeichenfolge öffnen.     Set conPubs = wrkMain.OpenConnection("mtv", dbDriverNoPrompt, False, odbcCONNECT)

    Set dbspUBS = conPubs.Database
ODBC_Verbindung_aufbauen_exit:
ODBC_Verbindung_aufgebaut = True
Exit Sub
ODBC_Verbindung_aufbauen_error:

    MousePointer = vbDefault
    For i = 0 To DAO.Errors.Count - 1

        hv1 = InStr(1, DAO.Errors(i).Description, "ORA")
        If hv1 > 0 Then
            hv2 = Len(DAO.Errors(i).Description) - hv1
            ORA_Nummer = Mid(DAO.Errors(i).Description, hv1, 9)
            Select Case ORA_Nummer
            Case "ORA-28000"
                msg = "ORA-28000: Passwort gesperrt / Wenden Sie sich
an den Bonsai-Administrator"
            Case "ORA-01017"
                msg = "ORA-01017: Nutzername oder Passwort falsch / die
Anmeldung verweigert"
            Case "ORA-28001"
                msg = "ORA-28001: Passwort abgelaufen / Ändern Sie Ihr
Passwort"
            Case "ORA-12154"
                msg = "ORA-12154: Servicename falsch / Wenden Sie sich
an den Bonsai-Administrator"
            Case "ORA-01005"
                msg = "ORA-01005: Kein Passwort angegeben / Anmeldung
verweigert"
            Case "ORA-01004"
                msg = "ORA-01004: Kein  Benutzername angegeben /
Anmeldung verweigert"
            Case ElseBenutzername
                msg = msg & Right(DAO.Errors(i).Description, hv2 + 1) &
Chr(13)
            End Select
        End If

    Next i
    message = MsgBox(msg, vbOKOnly)
    ODBC_Verbindung_aufgebaut = False
Exit Sub
End Sub

---

'a example action at the database (i have Ora on NT and on LINUX)

Sub example_query()
Dim ssql,username As String
Dim rs As Recordset

ODBC_Verbindung_aufbauen "Cinnect-String", UserID, Passwd

 ssql = "select user from dual"
   Set rs = conPubs.OpenRecordset(ssql, dbOpenSnapshot)

   username =rs.user
rs.Close

ODBC_Verbindung_abbauen
End Sub

---

'close ODBC-Connection
Public Sub ODBC_Verbindung_abbauen()

    dbspUBS.Close
    wrkMain.Close
    ODBC_Verbindung_aufgebaut = False
End Sub

---

In article <8cvqpm$68h$1_at_nnrp1.deja.com>,   vk02720_at_my-deja.com wrote:
> In article <8bin5a$k1n$1_at_soap.pipex.net>,
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote:
> > What do you mean by all the necessary files are in place?
> >
> I meant Oracle ini file tnsnames.ora is set up so that client machine
> can connect to Oracle server

>
> > If you have an Oracle ODBC driver (either MS or Oracle) then you can
 use
> > ODBC

>

> What function should I use to connect ?
> For connecting to local Access DB, I use OpenDatabase()
> Can the same be used for an ODBC data base.
> I have a ODBC data source set up which I use to link tables from
 Oracle
> server to local Access DB which I then use with OpenDatabase()
> But I would like to connect to remote Oracle server directly
> >
> > If as other have suggested you have oo40 you can use Oracle Objects
> >
> > Or (my preferred solution) you can use ado and an oledb driver. This
 last
> > solution is likely to mean that there is lots of sample code you can
> > steal.... sorry use for inspiration. It also means porting the app
 across to
> > sql server or whatever is made much much easier.
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > <vk02720_at_my-deja.com> wrote in message
 news:8bcclh$rqh$1_at_nnrp1.deja.com...
> > > How to connect to an Oracle server running on a Unix machine from
> > > Visual Basic running on a client NT machine ? All the appropriate
> > > files/settings are in place and sqlplus can be used to connect
 from
 the
> > > client NT machine.
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 12 2000 - 00:00:00 CDT