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

[VAer]

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Source Table Name", "ABC", False, True

Before importing ODBC database table, I have a Access Form for Username/Password. I also have a table User_T for Username/Password.



If the username/password is wrong, then return error message, and Exit Sub. If username/password is correct, then clear any data in existing table User_T, and add one record for entered username/password. That being said, there will no more than one record in the table User_T at any given time points. Every time I need my code to interact with ODBC database, I will use stored information in the table User_T.

How can I write code to test username/password? How should I know if the username/password is correct or not.

Thanks.

[June7]

You need a table of all users with their username/password. How else would you verify this login info?

[VAer]

You need a table of all users with their username/password. How else would you verify this login info?

This is not exactly I am asking. I don't store information for all users, just temporary store information for current user. As mentioned above, there will not be more than one record in any given time points. This is not application to store username/password for all users, IT department has the information for all organization users. However, in order to perform some data analysis on ODBC database, the Access application needs to know the user's username/password. I mean I need to verify username/password first, then INSERT username/password in the access table (if the log in information is correct).

I have two text boxes in log in form, then there will be two values (Username.Value & Password.Value), I need to verify these two values and see if it can access to ODBC database.

[isladogs]

Is ActiveDirectory in use across your network. If so, you can use that as your check without any need to store user details even temporarily

[June7]

Sorry, duplicate.

[June7]

Yes, if you can access the network ActiveDirectory, I suppose can use that to validate username. Assuming you want to allow anyone who can login to the network to use the db. However, I doubt you can do a match on the password. If you could then there is no security for the network.

[VAer]

Yes, if you can access the network ActiveDirectory, I suppose can use that to validate username. Assuming you want to allow anyone who can login to the network to use the db. However, I doubt you can do a match on the password. If you could then there is no security for the network.

Yes, I do want everyone who can log in to use the access. I am not IT and not very familiar with the term ActiveDirectory, but I guess ActiveDirectory is used in my organization.

I want everyone using the Access file to provide username/password through login form. The purpose is not validating the username/password, the purpose of validation is because the program needs the information to link back end Sybase tables to Access file (see first post code). The program will need username and password.

[VAer]

Is ActiveDirectory in use across your network. If so, you can use that as your check without any need to store user details even temporarily

See the above reply. The program will need username and password later on. The purpose of application is not validating username and password, the purpose of the application is to use correct username and password to link Sybase database tables to Access files. Different programs in the access file requires linking different tables, depending on what tasks the user wants to perform.

[June7]

So you want user to enter their network USERNAME and password and use that to automate validation for Sybase link? Again, if that were possible with code there would be no security because that is what hackers do.

Can capture USERNAME with Environ("USERNAME"). Automating grabbing or matching password is another issue.

My only experience with linking to other db (Oracle) required IT to set up permissions then I seem to remember having to load another software to each user machine that allowed the ODBC Data Source Administrator to set link.

[VAer]

So you want user to enter their network USERNAME and password and use that to automate validation for Sybase link? Again, if that were possible with code there would be no security because that is what hackers do.

Can capture USERNAME with Environ("USERNAME"). Automating grabbing or matching password is another issue.

My only experience with linking to other db (Oracle) required IT to set up permissions then I seem to remember having to load another software to each user machine that allowed the ODBC Data Source Administrator to set link.

I think you misunderstand my question. Okay, look at first post, the code requires valid Username/Password, if a colleague wants to use the Application, then a colleague needs to provide username/password, since the programs(such as first post code) inside the application requires valid Username/Password.

Again, the ultimate purpose is not validating username/password, the ultimate purpose to interact with back end database (such as running queries), interacting with back end database requires username/password. It is just because there is security, then user needs username/password to interact with back end database. However, most Sybase database tables are open to all employees, only read permission set up by IT department, I guess. So programs of running query and pulling data out should be fine, but the program of modifying data could be limited to small group of people.

I have built Excel-based Application for team members, xltm file extension, it also has login form asking for Username/Password. Without the information, some programs cannot proceed. It is not front end application's purpose to have username/password, it is the back end database requiring username/password to perform some tasks. Even running a query from back end database, it requires username/password. What is purpose of building Application? It is not just building the log in form, only log in and log out, the purpose of building application is to perform some job tasks. It just happens those job tasks requiring data from back end database.

There are whole lots of applications requiring username/password to log in. Commercial product Hyperion query used in my workplace, it is also front end product, which requires users to enter Username/Password. If you use Oracle software in your work computer, does it ask you to log in? Self-built small Access application will act just like that.

The purpose of application is for colleagues to use, to perform some tasks. Let me give you one simple example, in order to perform some data analysis for a report, it requires to pull out data from 5 back end database tables. Then I may go ahead and build an Excel-based program to automatically pull 5 tables into 5 separate Excel spreadsheets. Without username/password, how can Exel VBA pull the data out? Back end database requires username/password.

[June7]

Therein lies the conundrum. Passing username/password by code instead of keyboard is rejected by backend.

My Oracle setup required IT to set specific user permissions in backend then I had to install another software to facilitate the link. Then yes, opening Access db required input of username/password provided by IT for link to establish.

[VAer]

Therein lies the conundrum. Passing username/password by code instead of keyboard is rejected by backend.

My Oracle setup required IT to set specific user permissions in backend then I had to install another software to facilitate the link. Then yes, opening Access db required input of username/password provided by IT for link to establish.

I am not IT and I am not familiar with that (how to require input from IT). I would not bother IT department, I have built quite some small Excel-based VBA programs requiring Username/Password, no colleague complained about entering Username/Password. I am sure IT guys don't want to be bothered too, they are busy, and it is not their regular task to provide whatever link for the application. Database is open to all employees, it is not like very important data. Every employee should have read permission.

But I do know there are many workplace small applications/programs (built by different colleagues; within my organization) requiring Username/Password.

[June7]

The Oracle backend managed by IT dept and Access to it is strictly controlled. Excel is not an enterprise db app and whether or not a workbook is password protected is up to the user. IT certainly does not care about that.

Sybase db open to all employees, but still have to input username/password? Again, doing that programmatically might not be permitted.

I used to be able to programmatically copy Access file to user workstation and then IT upgraded operating system and that capability lost.

[CJ_London]

going back to your first post

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Source Table Name", "ABC", False, True

you required the user to supply the bits in red.

so modify to

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=" & User2 & ";PWD=" & www & ";LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Source Table Name", "ABC", False, True

So now you need to capture values for user2 and www - so create an unbound form with controls called User2 and www, plus a button to run the import

as far as validation is concerned the code will fail with a wrong user or password so just include some error trapping code

On Error goto errctrl

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=" & User2 & ";PWD=" & www & ";LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Source Table Name", "ABC", False, True
exit sub

errctrl:
msgbox "Wrong username or password, please try again"

[June7]

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.