Access VBA: How to write code to test ODBC username/password?

[VAer]

going back to your first post



you required the user to supply the bits in red.

so modify to

No, this is NOT what I am asking. Actually, I should use acLink as example code. But that is not the point. I am not asking code to import or link, I am asking if there is code to test connection (neither import or link).

Let me clarify my question again:

1. I build Access application, and I use my own Username/Password and the code acLink to Sybase table to Access Application already. However, if I close/save the file, the table connection is lost. When I re-open the file,a window will pop up for Username/Password again(see attached photo). So every time when I open the file and use the application, I need to provide Username/Password again. Network: password expires every a period of time anyway, so the password keeps changing. So every time when using the application, I need to provide CURRENT Username/Password.

2. Now when I deliver the product to colleagues, when user opens the file and run the program, it will ask for Username/Password too (see attached photo). That is the reason I build Login form to ask user to provide Username/Password.

3. There are many programs inside Access application, when user enters Username/Password through login form, I want the code to test if user provides valid Username/Password or not, since many programs inside the application require valid User/Password. If user provides correct username/password, then login form disappear and new form (with many buttons) appears, then user can click the button to run program to interact with back end Sybase database; If user provides incorrect Username/Password, then login form stays there, and returns error message then Exit Sub.

[VAer]

I assumed you had already tested a login with a username/password submitted by code and that was failing. If not, concatenate reference to form controls as suggested by Ajax and see if the Sybase will accept.

No, the table HelloWorld was successfully linked, see above photo. I am not sure how to explain my question, I have tried to explain as clearly as I can, many times. I mean test Username/Password, it is not about acImport or acLink, it is just test, not doing any specific task. Testing Username/Password when user enters the information through login form; then using Username/Password to run any program user chooses to run.

[CJ_London]

I

mean test Username/Password

test against what?

[VAer]

Itest against what?

I mean I have log in form, two textbox, user can enter any text values in those two textbox, how can I know if those two textbox values are correct or not. Let us say, you have Citi bank account, you try to log in your online account, if you go to citibank website and enter incorrect user/password, the website will show error message and you are not able to enter your account. If you enter correct username/password, you will enter your online account homepage, and you can perform some tasks there (transfer money, view statements, etc). So how can the website know if you enter correct username/password or incorrect username/password? That is also exactly what I want my code to do when user enters username/password, I want my code to know if username/password is correct or incorrect. If it is correct, then login form will be unloaded and a different form will be loaded, there are some programs in the different form, user can run the programs when clicking the command button. If the username/password is incorrect, the login form will show error message, sorry, user will not be brought to the different form, therefore, the user is not able to run any programs.

It seems we are not on the same page, in the previous reply to your message, I have explained it as clearly as I can, maybe I am poor at explaining my questions. Maybe I should not use the word test, since when the file is closed, the connection is lost, but the table still appears there. A window pops up and ask for Username/Password for the table previously linked.

Maybe the code structure should be like this:
1) User provides Username/Password via login form.
2) Program tries to use the Username/Password to connect to back end database.
3) If connection status = successful, then the Username/Password is correct; if connection status = fail, then the Username/Password is incorrect.

[June7]

Citibank is checking the username/password against records in a table. If it finds a match then allows user to continue. You want to check against records you do not have access to.

So yes, those steps sound logical.

[VAer]

Citibank is checking the username/password against records in a table. If it finds a match then allows user to continue. You want to check against records you do not have access to.

So yes, those steps sound logical.

But I think I can still go around it, I can assume the Username/Password is correct, and try to use the Username/Password randomly ran a query from back end database, if error message, then GoTO displaying "Incorrect Username/Password".

[June7]

Don't disagree with that. You know the username/password will fail regularly because password periodically changes. In which case you discard the old and save the new.

[VAer]

Don't disagree with that. You know the username/password will fail regularly because password periodically changes. In which case you discard the old and save the new.

What are you talking about? The purpose of login is asking user to provide current password. No need to save password in the file, just temporarily save the password when the file is open(user may run some programs in the Access application). The program can remove the password when exit the application. Or user can simply close the file without Save the file.

[June7]

Yes, save to a variable for the current work session. And if you aren't saving for longer term use then fine. Now try it and if you encounter issue post question.

[VAer]

Yes, save to a variable for the current work session. And if you aren't saving for longer term use then fine. Now try it and if you encounter issue post question.

How to save to a variable? Login submit button can save the information to variable, but how can other separate programs know the variable value? Save to GLOBAL variable?

I have not started building it, just thinking about code structure in mind.

[June7]

What 'other separate programs'? You already have code to pass username/password to the Sybase login. Perhaps similar code relevant to other programs.

[VAer]

What 'other separate programs'? You already have code to pass username/password to the Sybase login. Perhaps similar code relevant to other programs.

I have never said "passing to Sybase login", I think that is more about my question, how to let Sybase know if the username/password is correct.

Am I that poor at explaining? I have explained many times, and you still don't follow my question. Maybe I should explain again.....

For example, I have two forms in Access Application, one is Form Login, the other form Interface is for other programs, let us say the form Interface has five command buttons, each command button runs a query from back end Sybase tables. When user opens Access file, login form is loaded, user enters Username/Password; (now Access needs to know if the Username/Password is correct for Sybase connection, this is my question in this whole thread); If Access determines the Username/Password is correct, then form Login is unloaded, and form Interface is loaded, user can run any of the five command buttons, those command button programs need username/password to run query from Sybase, since Username/Password is determined to be correct before loading form Interface, so the command button programs just use the Username/Password. But if Access determines Username/Password is incorrect, then form Login will stay and Access returns error message "Incorrect Username/Password, please try again."

[June7]

As far as I can see for this scenario, the only way to know if the username/password is correct is to make Sybase login effort (i.e. pass the values to Sybase) - step 2 in your earlier post 19.

[Minty]

The only way to achieve this is to perform a operation that would link to the target server at the point of the initial login.

So try and create a link to any table in your target database at that point using the data the user entered, If it fails then you know the userid/password is incorrect, reject the entry and start over.

[VAer]

The only way to achieve this is to perform a operation that would link to the target server at the point of the initial login.

So try and create a link to any table in your target database at that point using the data the user entered, If it fails then you know the userid/password is incorrect, reject the entry and start over.

I know, this is also mentioned in post #21. But "perform a operation" may cause code inefficient (no matter running a query or linking table, it takes time). I am trying to ask if there is code for purely testing connection.

For example:

SybaseConnectionStr = "...... UID=User2;PWD=www; ......." (I don't know how to write this part)
SybaseConnectionStr.Open (I don't know how to write this part)
If Connection Status = Successful Then (I don't know how to write Connection Status = Successful)
Accept Username/Password
Form Login unloaded
Form Interface loaded
Else
Reject Username/Password
Return Error Message
Form Login stays
Exit Sub
End If