How to create a Log In form that uses a Remote Database

To do this your users will need MySQL Connector/ODBC 3.51, a little driver needed to establish your connection. Then you will use ADO(ActiveX Data Objects) to surf in the database.

How to do this?
First the program will look for the user. If the user doesn’t exist it shows the error. If the user exists it will move to that record and check if the passwords are the same. If the second condition is true then it will show the LOGGED form. To secure passwords I suggest you using the MD5 Class to convert passwords in Base64 Hashes.

How to code this?
First add the following code in a module to simplify things:

Option Explicit

Dim conn As ADODB.Connection

Dim MySQL1 As ADODB.Recordset

Dim username As String

Dim passwd As String

Dim serverIP As String

Dim db As String


Public Function connectMysql(username As String, passwd As String, serverIP As String, db As String, conn As ADODB.Connection, MySQL1 As ADODB.Recordset)

   Set conn = New ADODB.Connection

   Set MySQL1 = New ADODB.Recordset

   conn.CursorLocation = adUseClient

   conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & serverIP & ";UID=" & username & ";PWD=" & passwd & ";DATABASE=" & db & ";"

    On Error GoTo Err:

   conn.Open

    On Error GoTo Err:

   

   Exit Function

Err:

   MsgBox "Error connecting to the Server, try again please!    ", vbCritical

End Function

Now go to the Project’s References Dialog(Click the “Project” menu then “References”) and find “Microsoft ActiveX Data Objects 2.7 Library”, tick it and click OK. Create your login form and declare the following variables:

Option Explicit

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim username As String

Dim passwd As String

Dim serverIP As String

Dim db As String

Dim ssql As String

Add the following code to the Form_Load() sub:

Call connectMysql("UserHere", "PassHere", "ServerNameHere", "DBNameHere", conn, rs)


ssql = "SELECT * FROM TableNameHere"

rs.Open ssql, conn, adOpenStatic, adLockOptimistic

You MUST change UserHere with your MySQL Username, PassHere with your MySQL User Password, ServerNameHere with your Server’s IP Address or domain, DBNameHere with your Database name and in the next line TableNameHere with your table in wich the users are stored.

In the table there must be 2 fields “User” and “Pass”. Add users and their passwords on this table and you will be able to log with them immediately.

Now to the “LogIn” button add the following code:

If txtUser = "" Then

    MsgBox "Please enter the Username!  ", vbCritical

Else

   

    rs.MoveFirst

    rs.Find "[User] like '*" & txtUser & "*'"

    

    If rs.EOF Then

        MsgBox "The Username that you have entered doesn't exist in the database!  ", vbCritical

    Else

        If txtPass = rs![Pass] Then

            LOGGED.Show

        Else

            MsgBox "The password is incorrect, please retype it!   ", vbCritical

        End If

    End If


End If

If you still have trouble understanding something, download the attachment and study it.

PS: You can run your MySQL Server on your PC if you have a public IP or if your clients are in LAN and don’t forget to install the MySQL Connector/ODBC 3.51 Driver to the clients.

Wish you all the best,
Dren