Experts, could you help?

  I have trying to do a ODBC Oracle Connection using the logon screen created by VB forms and command button and textboxes.
I have a command button named contain the following codes:

set db=opendatabase("",false,false,connect)

I'm supposed to use my logon screen to capture user id and password,
but the moment I key in an invalid oracle user id and password, oracle logon screen will be invoked.What I want is that my logon screen will be invoked (to let user try again) instead of the oracle logon.So is there anyway we can suppress that oracle logon.Please help.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

anthonycConnect With a Mentor Commented:
vbDriverNoPrompt works in VB4 with DAO.  

set db = opendatabase("databasename", dbDriverNoPrompt, false, "ODBC;DNS=..."

You would have to have the password checked before the password to be given to the oracle checker. (I.E. Have it download the password database, then check the input against it.)
ssptktAuthor Commented:
Hi ndnet,
    it rather impossible for what you have suggested. Still I deeply appreciate your input. Thank You.

Best wishes,
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

I realize it would be hard, but it is the only solution without contacting Oracle. As soon as you submit the first password, the Oracle program takes over. One last   idea:(This goes in the code for the submit button)

On Error Goto wrong 'goes at very top

wrong: ' goes at very bottom
load frmLogin 'frmLogin is the login form

The only problem is that the Oracle Window may open.

  For not invoke Oracle logon windows and show your own window, you must apply a DriverNoPrompt action and do a error trapping.

  If you are using RDO, eg.
  Dim rd As RDOConnection
  Set rd = New RDOConnection

  On Error Goto ErrHandler
  ' Your code to getting UserID from User
  ' eg. sUID = InputBox("Input User ID to logon:")
  rd.Connect = "UID=xx;PWD=;DSN=xxx;"
  rd.CursorDriver = rdUseOdbc
  rd.EstablishConnection rdDriverNoPrompt
  Exit Sub

  If Err.Number = xxx Then   ' Check whether error is cause by
     Resume GetUser          ' invalid user and pwd
  End If                    

If you are using DAO, you must open database thru ODBCDirect Workspace:

  Dim wrk As WorkSpace
  Dim db as Database
  set wrk = CreateWorkspace("ODBCWorkspace", "admin", _
            "", dbUseODBC)
  Workspaces.Append wrk

  On Error Goto ErrHandler    ' Same as example above
  ' Getting User ID
  set db = wrk.OpenDatabase("", dbDriverNoPrompt, False, _
  :  ' Just same as example above....


 For specify DriveNoPrompt, ODBC driver won't prompt you the remote database logon screen. So you get to know that is error while opendatabase thru error handler.

ssptktAuthor Commented:
Hi Yowkee,
   So sorry I did not mention the VB version which I'm using, it's VB4.0. I'm quite positive that it does not support the feature of DriverNoPrompt and I'm not using RDO.
   Please let me know if you have other ways of solving the problems. I would be most grateful. Thank You and have a nice weekend.
ssptkt, have you tried adding UID=xx;PWD=xx to the connect string?
ssptktAuthor Commented:
Yes I did. I have tried everything I could. Real demoralising.

  One way to implement it is thru ODBC API. Assume you are using VB4 32 bits:

Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal hEnv As Long, pHdbc As Long) As Long
Declare Function SQLAllocEnv Lib "odbc32.dll" (pHenv As Long) As Long
Declare Function SQLConnect Lib "odbc32.dll" (ByVal hDbc As Long, ByVal szDSN As String, ByVal cbDSN As Long, ByVal szUID As String, ByVal cbUID As Long, ByVal szAuthStr$, ByVal cbAuthStr As Long) As Long
Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hDbc As Long) As Long
Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hDbc As Long) As Long
Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal hEnv As Long) As Long

Const SQL_NTS = -3

Sub GetUser()

    Dim hEnv As Long
    Dim rc As Long
    Dim hDbc As Long
    Dim sUid As String
    Dim sPwd As String
    rc = SQLAllocEnv(hEnv)
    rc = SQLAllocConnect(hEnv, hDbc)
       ' Get User ID to sUID, Password to PWD
       ' Put your routine/form here, I use get userid as example
       sUid = InputBox("UserID")
       sPwd = "temp"
       rc = SQLConnect(hDbc, "RiskMonSrv", SQL_NTS, _
                       sUid, Len(sUid), sPwd, Len(sPwd))
    Loop While (rc <> SQL_SUCCESS) Or (rc <> SQL_SUCCESS_WITH_INFO)
    SQLDisconnect hDbc
    SQLFreeConnect hDbc
    SQLFreeEnv hEnv
End Sub

Note: I am in a hurry, so didn't any error handler in the procedure. The other things, please verify the API declaration if
facing any problem while testing. Sorry, I didn't spend much time to verify it. Remember that it is always a good pratice to free the resource after you don't use it(SQLDisconnect, SQLFreeConnect, SQLFreeEnv...)


The 2nd parameter of SQLConnect is DSN.
Would you be interested in using Oracle Objects for OLE?  That is what I use here to connect to Oracle and it is very easy to connect etc...

If you want to go that route I will post the Module that I created here that I use in about 50 different programs.

  I also mentioned using DAO with dbDriverNoPrompt in my comment. I didn't have VB4.0 and what I get from VB5 help is that dbDriverNoPrompt only support in ODBCDirect workspace. Is it work for VB4 too?
ssptktAuthor Commented:
Hi anthonoyc,
   I would like to confirm with you whether is it vbDriverNoprompt or dbDriverNoprompt and by the way is vb or dbDriverNoprompt suppose to be a driver, method or property? How and where can I determine/check if dbDriverNoprompt is residing on my VB4.0. And do I have to specially preset anything else on install anything else.Please advise

Thanks and have a nice weekend,
Ken (98)

the way you use it is

set db = opendatabase("DSNNAME", dbDriverNoPrompt, False, "ODBC;...")
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.