Solved

Experts, could you help?

Posted on 1998-06-04
14
192 Views
Last Modified: 2008-02-01
Hi
  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 connect.it contain the following codes:

connect="ODBC;....DSn......"
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.
0
Comment
Question by:ssptkt
  • 4
  • 4
  • 2
  • +3
14 Comments
 
LVL 2

Expert Comment

by:ndnet
ID: 1462560
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.)
0
 

Author Comment

by:ssptkt
ID: 1462561
Hi ndnet,
    it rather impossible for what you have suggested. Still I deeply appreciate your input. Thank You.

Best wishes,
Ken
0
 
LVL 2

Expert Comment

by:ndnet
ID: 1462562
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.
0
 
LVL 4

Expert Comment

by:yowkee
ID: 1462563
ssptkt,

  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
GetUser:
  ' 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

ErrHandler:
  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
GetUser:
  ' Getting User ID
  set db = wrk.OpenDatabase("", dbDriverNoPrompt, False, _
           "ODBC;....")  
  :
  :  ' 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.

Regards.  
0
 

Author Comment

by:ssptkt
ID: 1462564
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.
0
 
LVL 2

Expert Comment

by:marti
ID: 1462565
ssptkt, have you tried adding UID=xx;PWD=xx to the connect string?
0
 

Author Comment

by:ssptkt
ID: 1462566
Yes I did. I have tried everything I could. Real demoralising.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:yowkee
ID: 1462567
ssptkt,

  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
Const SQL_SUCCESS = 0
Const SQL_SUCCESS_WITH_INFO = 1

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)
    Do
       ' 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...)

Regards.

0
 
LVL 4

Expert Comment

by:yowkee
ID: 1462568
The 2nd parameter of SQLConnect is DSN.
0
 
LVL 7

Expert Comment

by:tward
ID: 1462569
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.
0
 
LVL 6

Accepted Solution

by:
anthonyc earned 120 total points
ID: 1462570
vbDriverNoPrompt works in VB4 with DAO.  

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

0
 
LVL 4

Expert Comment

by:yowkee
ID: 1462571
anthonyc,

  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?
0
 

Author Comment

by:ssptkt
ID: 1462572
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)

 
0
 
LVL 6

Expert Comment

by:anthonyc
ID: 1462573
the way you use it is

set db = opendatabase("DSNNAME", dbDriverNoPrompt, False, "ODBC;...")
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA loop through headers using value 3 57
MS Date Picker 64 bit 32 bit issue 12 50
VBA: Select SQL query based on a config Sheet v2 11 38
MsgBox 4 48
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now