Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    20

    Unanswered: connection access/oracle

    hi guys, i want to connect my vba application to oracle db, i try with this code, but i have problems.
    my oracle is a 7.3..

    Public Sub ApriConn2()
    ConnStr = "ODBC;DSN=ISC.ISCWEB;DATABASE=DITTALDM;"
    ConnStr = ConnStr & "UID=" & sigla & ";"
    ConnStr = ConnStr & "PWD=" & sigla & ";"
    'strCnn = "driver={Microsoft ODBC For Oracle}; server=ISC;" & _
    '"uid=DITTALDM;pwd=DITTALDM:database=DITTALDM"
    Set cnn = New ADODB.connection
    cnn.Open strconn
    End Sub

    thanks
    fr

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    Oracle Database as of 7.3 where limited to a maximal 4 character name

    does your database in your tnsnames.ora is really named to DITTALDM???

  3. #3
    Join Date
    Nov 2003
    Posts
    20
    the code is wrong, but corrected it but I haven't the solution yet! now my code is:
    Public Sub ApriConn2()

    ConnStr = "DSN=DITTALDM;" & _
    "TNS=ISC;" & _
    "UID=DITTALDM;" & _
    "PWD=DITTALDM;"
    Set cnn = New ADODB.connection
    If (cnn = Null) Then
    MsgBox ("errore")
    Else
    cnn.Open ConnStr
    End If
    End Sub


    My Data source Name is DITTALDM
    My TNS is ISC
    My UID is DITTALDM
    My PWD is DITTALDM

    question but if my file is in the server where is the db oracle, i needed the TNS or not

    what do you think about my code?
    bye

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    I once did it this way and it worked; DB was of release 8i - not 7.3

    Option Explicit
    Public DBConnect As ADODB.Connection

    Public Function DBCon()
    On Error GoTo Err_Handler
    Set DBConnect = New ADODB.Connection
    With DBConnect
    ' frmDBUsr.CancelForm = False
    'Load frmDBUsr
    frmDBUsr.Show
    ' If .CancelForm Then GoTo Exit_Routine
    ' msdaora als provider
    If frmDBUsr.orcldriver = True Then
    .Provider = "OraOLEDB.Oracle;Data Source=DBY;User ID=" & frmDBUsr.selusr & ";Password=<pwd>;"
    Else
    .Provider = "msdaora;Data Source=DBY;User ID=" & frmDBUsr.selusr & ";Password=<pwd>;"
    End If
    '.Properties("Prompt") = 2
    .Open

    End With

    I kept the Data Source and tnsname.ora SID names in sync & needed an oracle client installed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •