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.

 

[Pat Hartman]

The following function connects to two separate Oracle databases. One connection uses a variable userID and fixed password and the other uses all fixed values. The function also checks a value stored in a local access table that tells whether to log onto the production database or a test database. The connection string for the test database is stored in the table. This method gave us a lot more flexibility for testing. The logon to the second database never needed to change because we did not have update permission to any of the tables. We just had read permission so it didn't matter if we used the production tables all the time.

Public Function ConnectToOracle()
Dim WgtDB As Database
Dim strConnect As String
Dim F As Form
Dim R As Recordset

Set WgtDB = CurrentDb()

Set F = Forms!frmLogon
gUserID = Nz(F!txtUSER_ID, "Empty")
gPassword = Nz(F!txtPSWD, "Empty")
gNetWareID = Nz(F!txtNetWareID, "Empty")

strConnect = "ODBC;UID=" & gUserID & ";PWD=security;DSN=weight;Database=WGT"

Set R = WgtDB.OpenRecordset("tblMode", dbReadOnly) 'get the directory path
R.MoveFirst
If Not R.EOF Then
If R!T_P_Switch = "T" Then
strConnect = R!Connect_Str
End If
End If

Set WgtDB = OpenDatabase("", False, False, strConnect)
strConnect = "ODBC;UID=FARMS_EXTRACT;PWD=mike;DSN=farms;Database=farms"
Set WgtDB = OpenDatabase("", False, False, strConnect)
WgtDB.Close

End Function

 

[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"

 

[Pat Hartman]

You may have taken the code too literaly. Do you have a form named - frmLogon - that prompts for the userID and Password? Also, you have left out the DATABASE= parameter.

 

[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.