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

[Dren]

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

[TcM]

Didn't try the software. But seems a promising.. and I might use it myself sometime.

+rep to you!

[woddlez]

Awesome O_o

Btw im probably gonna be a leecher, because all i need is a few examples to borrow, i will add credits btw, and ill also +rep you because this is awesome lol

-Woddles

[OliWilding]

This should help with my A level coursework nicely

[Egz0N]

haven't seen this one .. +rep to you bro ..