Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: ODBC Connection Status

    Hi! I have a program that has an ODBC connection to a Sybase driver. My issue is with users that have different versions of sybase which aren't accounted for yet in my coding.

    Is there a way to check the status of the connection and if no connection is made or there is an error with the connection, to move on to the next string (which would identify a different driver)? Hope this isn't a dumb question

    Coding is below...for users with a driver other than 'SYBASE ASE ODBC Driver' an error message is returned (insufficient information to connect to the data source).

    Thanks!

    Set db = New adodb.Connection
    db.ConnectionString = "Driver={SYBASE ASE ODBC Driver};" & _
    "Srvr=Mpr_2;" & _
    "Uid=orangepunter;" & _
    "Pwd=adzstik6;" & _
    "Database=amg;"
    db.Open

  2. #2
    Join Date
    Nov 2004
    Posts
    108

    Lightbulb error handler

    adding an error handler should work
    to err is human ; to really mess things up requires a computer

  3. #3
    Join Date
    Nov 2004
    Posts
    3

    re: error handler

    thanks for your response! I am pretty new to VB and am not sure where to start with the error handler. any suggestions? thanks again.

  4. #4
    Join Date
    Nov 2004
    Posts
    108

    Post Search on MSDN for on error

    at the beginning of the procedure

    Code:
    On error goto myErrorHandler
    at the end of the procedure
    Code:
      conn.close
      set conn = nothing
    Exit Sub
    myErrorHandler:
      'code to handle the error
      .
      .
      .
      err.clear
      Resume Next
    End Sub


    MSDN
    Code:
    Sub OnErrorStatementDemo()
       On Error GoTo ErrorHandler   ' Enable error-handling routine.
       Open "TESTFILE" For Output As #1   ' Open file for output.
       Kill "TESTFILE"   ' Attempt to delete open 
                ' file.
       On Error Goto 0   ' Turn off error trapping.
       On Error Resume Next   ' Defer error trapping.
       ObjectRef = GetObject("MyWord.Basic")   ' Try to start nonexistent
                ' object, then test for 
    'Check for likely Automation errors.
       If Err.Number = 440 Or Err.Number = 432 Then
          ' Tell user what happened. Then clear the Err object.
          Msg = "There was an error attempting to open the Automation object!"
          MsgBox Msg, , "Deferred Error Test"
          Err.Clear   ' Clear Err object fields 
       End If   
    Exit Sub      ' Exit to avoid handler.
    ErrorHandler:   ' Error-handling routine.
       Select Case Err.Number   ' Evaluate error number.
          Case 55   ' "File already open" error.
             Close #1   ' Close open file.
          Case Else
             ' Handle other situations here... 
       End Select
       Resume   ' Resume execution at same line
                ' that caused the error.
    End Sub
    to err is human ; to really mess things up requires a computer

  5. #5
    Join Date
    Nov 2004
    Posts
    3

    One more thing...

    Got it! thanks for your help. Seems to be working now but I'm getting greedy and want to add another piece to my program.

    My program now connects a user through one of two drivers based on whether or not they get an error on the first. If a user has no driver setup, I am still getting the same error message. The error is occuring within the error handler statement. I tried nesting an on error statement within the handler to end the procedure but it doesn't seem to work. I also tried a select case based on the error number, but didn't have any luck with that either.

    Do you have any suggestions to remedy this? I've tried looking on the MSDN site, but haven't found anything that works with this case.

    basically this is what I want to do:
    if connection 1 fails,
    then connection 2.
    if connection 2 fails,
    exit procedure.

  6. #6
    Join Date
    Nov 2004
    Posts
    108

    Post your code

    you could also let the user decide

    try the ODBC Login form

    or a logic like
    Code:
    Dim connStr(2) as string
    .
    .
    .
    set conn = nothing
    on error resume next
    for i = lbound(connStr) to ubound(connStr)
         conn.open connStr(i)
         if err.count > 0  then
              if i = ubound(connStr) then
                   MsgBox "Connection failed.  Error messege:  " & err.description
                   if msgbox("Do you want to set it yourself?") = vbYes then
                        frmODBCLogin.show 1
                   end if
              end if
              err.clear
         end if
    next
    on error goto 0
    to err is human ; to really mess things up requires a computer

  7. #7
    Join Date
    May 2004
    Posts
    97
    Quote Originally Posted by OrangePunter
    Got it! thanks for your help. Seems to be working now but I'm getting greedy and want to add another piece to my program.

    My program now connects a user through one of two drivers based on whether or not they get an error on the first. If a user has no driver setup, I am still getting the same error message. The error is occuring within the error handler statement. I tried nesting an on error statement within the handler to end the procedure but it doesn't seem to work. I also tried a select case based on the error number, but didn't have any luck with that either.

    Do you have any suggestions to remedy this? I've tried looking on the MSDN site, but haven't found anything that works with this case.

    basically this is what I want to do:
    if connection 1 fails,
    then connection 2.
    if connection 2 fails,
    exit procedure.

    Set db = New adodb.Connection
    on error goto errhandler
    db.ConnectionString = "Driver={SYBASE ASE ODBC Driver};" & _
    "Srvr=Mpr_2;" & _
    "Uid=orangepunter;" & _
    "Pwd=adzstik6;" & _
    "Database=amg;"
    db.Open
    exit sub

    errorhandler:
    if errorcount=2 then
    msgbox whatever
    else
    Set db = New adodb.Connection
    db.ConnectionString = "Driver={NEXT ODBC Driver};" & _
    "Srvr=Mpr_2;" & _
    "Uid=orangepunter;" & _
    "Pwd=adzstik6;" & _
    "Database=amg;"
    errorcount = errorcount+1
    resume
    end if


    Well, you get the idea even if you will definately want to add some additional code to that.

    Or even better, read the registry at HKLM>Software>Odbc>Odbc.ini>Odbc Data Sources, then select the entries where the data matches the driver type you want.

Posting Permissions

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