# Creating a login form with Visual Basic with .mdb database connection



## Codeman (Aug 28, 2010)

Hi Guys , wonder if you could help me , ive looked everywhere and cant seem to get any answers so thought id ask instead.

Im after creating a program in visual basic and i want different people to be able to login to access the forms im creating , im wanting to have the user names and passwords to be held in a .mdb database (for ease and accessability on my part) , im also wanting for certain users (admin) to have the ability to add users to the database .

If a normal user logs on then it opens form_normal but if an admin logs in then it opens form_admin

I hope some of you brainy people can help cus its driving me silly now lol

Thanks in advance
Mark


----------



## calvin-c (May 17, 2006)

It depends a lot on whether you want to database or the program to control access. Having the program control access to the tables is easier but also less secure. I use SQL Server so that's not really a problem-the users don't have any access to the database except via the program. If you can do this in Access, or if you're not too concerned about security then it's not too hard-you set up a user table with the user ID, password, and whether or not they have admin rights. The program (I use VB.Net) displays a login form and you check what they enter against the user table, calling different forms depending on their rights.


----------



## Codeman (Aug 28, 2010)

Hi mate , cheers for the reply , I'm wanting the program to control it but the database to hold the usernames as it's probably going to hold more info the more I get into it .

How easy is it cus I'm struggling here lol

Mark


----------



## calvin-c (May 17, 2006)

I can't exactly post a sample database as I'm using SQL Server, but I can certainly describe a simple one. In the database create a table 'users' with columns 'userID','password','rights'. As I said, you'll probably need to directly enter the 1st user who would be an admin. I'd start that with no password but add one later, before you release the program. Or you could encode the encryption module first, use it to generate the encrypted password & then manually enter that into the password column. I labeled the column rights as you can code various rights into it if you want, or you can label that column as 'admin' and add other columns for additional rights. For now I'll assume you code the rights, i.e. 0 = ordinary user, 9=admin, with 1-8 reserved for future use.

The program will start by displaying a form with 2 textboxes (UserIDBox, PwdBox) & 2 buttons (Enter, Cancel), and appropriate labels. The user enters his ID & password and presses Enter. Cancel, obviously closes the form-or you can omit it & let the user close by clicking the X but I prefer an explicit Cancel button.

Enter_Click will connect to the database & execute the following SQL statement:
"Select password, rights from users where userID='" & UserBox.Text & "'"
There are various ways of executing a SQL statement depending on the database, etc. Start by Googling "connection strings" to find out how to connect to your database.

The return from the SQL statement can be handled in different ways. I tend to use SQLReader instances which return one record at a time, from which I copy the specific column item. (In many cases I'll use the SQLReader in a loop but since this SQL should return either one or no records that isn't necessary.)

Check to be sure a record was returned. (I'm assuming you've already checked to be sure you successfully connected to the database.) If no record was returned then the userID is invalid. If it was returned then the record contains the encrypted password. Copy that into an appropriate variable like 'DBPwd'.

Now, encrypt PwdBox.Text using the same encryption method used when you stored the password. How complicated that gets depends on how secure you want to be. You can even store the password unencrypted if you like. Very simple encryption methods are +1 and -1. In +1 you add 1 to each character in a modular fashion. (You're familiar with modular arithmetic?) That works best for case-insensitive passwords but can be used with case-sensitive ones and even those that include symbols. More secure systems convert the password into a character or byte array & create an MD5 hash from it. Complex encryption isn't something I'll cover here, so I'll present the rest as if the password is stored without encryption-just remember that if you do use encryption you'll call your encryption method before comparing PwdBox.Text to DBPwd.

OK, you have DBPwd. If your system is case-sensitive all you need to do is compare them:
If PwdBox.Text = DBPwd Then 'Successful Login'. If it's case-insensitive then just convert PwdBox.Text to upper (or lower) case before the comparison.

If the login is successful then copy the rights value from the SQL statement into an appropriate variable 'Rights' and call the next form based on that:
Select Case Rights
Case 0
'call ordinary user form'
Case 9
'call admin user form'
Case Else
'if you hit this case you've either got a bug in your program or somebody's been messing with the users table, so generate an error message & handle it however you like. I usually write my error messages to a log file & default to the lowest rights:
Rights = 0
'call ordinary user form
End Select

And that's pretty much it for the login form. You might have a change password form accessible from the user form, or maybe you'll only allow admins to change passwords. I'm not really interested in doing further design work but once you have some specific code, if it isn't working, post it here & I (or somebody else) will see if we can help.


----------



## Rollin_Again (Sep 4, 2003)

Sounds like homework to me. 

Is this project being done in a stand-alone version of VB or are you planning on using the built in VBA that comes with MS Office?

Rollin


----------



## calvin-c (May 17, 2006)

I considered that possibility when deciding just how much help I was willing to offer. Out of curiosity, do you think I did too much of the work for him?


----------

