ODBC connection via a Login form in Access

[lamha]

I'm using Access 97. I have a login form in Access. After the user enter his username and password, the LOGIN command button will connect to an ODBC in Oracle server and let him view all the Reports that I made in Access.
I don't know how to write the code for setting up the connection. Please help. Thanks for your help in advance.

 

[lamha]

I copied some of your code down,but it doesn't work well. The code seemed to run fine, but when I want to view some data in frmSearch, it prompt a dialog box asking for my user name and password again. Why?
Dim KingstonDB As Database
Dim strConnect As String
Dim F As Form
Dim gUserID As String
Dim gPassword As String

Set F = Forms!frmLogIn
gUserID = Nz(F!txtUserName, "empty")
gPassword = Nz(F!txtPassword, "empty")
strConnect = "ODBC;UID=" & gUserID & ";PWD=" & gPassword & ";DSN=tekdb"
Set KingstonDB = OpenDatabase("", False, True, strConnect)
KingstonDB.close

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSEARCH"
DoCmd.OpenForm stDocName, , ,stLinkCriteria
DoCmd.close acForm, "frmLogIn"

 

[lamha]

Sorry, I left out something in the code. It was:
strConnect = "ODBC;DSN=tekdb;ConnectString=tekdb.oswego.alcan.com;UID=" & gUserID & ";PWD=" & gPassword & ""
I donot have a form that prompt for username and password. It is made in Oracle. It pops up whenever you want to access data in an Oracle server.
I want to make it user-friendly by making a LogIn form to prevent this pop-up form comes up when the user search for an item to view a Report.

 

[lamha]

I found out that it does connect to the ODBC database, but it prompts for username and password after that is because the UID and PWD are not being verified.
I tried taking UID and PWD out, or typed in some junk for these two parts, or typed in the corrected input, or hard code them. They still work the same way, which is open the other form and prompt for UID and PWD.