Module connection

    'CREATE A FUNCTION THAT RETURNS THE STRING CONNECTION FOR YOUR DATABASE.
    Private Function connection() As OleDb.OleDbConnection
        Return New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
                                            & Application.StartupPath & "\payrolldb.accdb; " & "JET OLEDB:Database Password = admin421")
    End Function
    'CREATE A STRING VARIABLE TO STORE THE QUERY ON IT.
    Public sql As String
    'SET A CLASSES THAT IS NEEDED.
    Public da As New OleDb.OleDbDataAdapter
    'SET A DATATABLE THAT NEEDS TO BE FILLED.
    Public dt As New DataTable

    Public Sub EmployeeCUD(ByVal sql As String)
        Try
            'OPENING A CONNECTION.
            connection.Open()
            'SET THE DATA COMMAND AND THE DATABASE CONNECTION THAT ARE USED TO FILL THE DATASOURCE.
            da = New OleDb.OleDbDataAdapter(sql, connection)
            'CREATE A NEW DATATABLE
            dt = New DataTable
            'ADDING THE DATA TO THE DATATABLE.
            da.Fill(dt)
        Catch ex As Exception
            'CATCH ANY ERRORS.
            MsgBox(ex.Message)
            Return
        End Try
        'CLOSING THE CONNECTION.
        connection.Close()
    End Sub
    Public Sub EmployeeDTG(ByVal sql As String, ByVal dtg As DataGridView)
        Try
            'OPENING A CONNECTION.
            connection.Open()
            'SET THE DATA COMMAND AND THE DATABASE CONNECTION THAT ARE USED TO FILL THE DATASOURCE.
            da = New OleDb.OleDbDataAdapter(sql, connection)
            'CREATE A NEW DATATABLE
            dt = New DataTable
            'ADDING THE DATA TO THE DATATABLE.
            da.Fill(dt)
            dtg.DataSource = dt
        Catch ex As Exception
            'CATCH ANY ERRORS.
            MsgBox(ex.Message)
            Return
        End Try
        'CLOSING THE CONNECTION.
        connection.Close()
    End Sub
    Public Sub EmployeeTXT(ByVal sql As String)
        Try
            'OPENING A CONNECTION.
            connection.Open()
            'SET THE DATA COMMAND AND THE DATABASE CONNECTION THAT ARE USED TO FILL THE DATASOURCE.
            da = New OleDb.OleDbDataAdapter(sql, connection)
            'CREATE A NEW DATATABLE
            dt = New DataTable
            'ADDING THE DATA TO THE DATATABLE.
            da.Fill(dt)

        Catch ex As Exception
            'CATCH ANY ERRORS.
            MsgBox(ex.Message)
            Return
        End Try
        'CLOSING THE CONNECTION.
        connection.Close()
    End Sub
    Public Sub userlogin(ByVal username As TextBox, ByVal pass As TextBox)
        Try
            'OPENING A CONNECTION.
            connection.Open()
            'SET THE DATA COMMAND AND THE DATABASE CONNECTION THAT ARE USED TO FILL THE DATASOURCE.
            da = New OleDb.OleDbDataAdapter("SELECT * FROM tbluser WHERE username ='" & username.Text & _
                                            "' and upass = '" & pass.Text & "'", connection)
            'CREATE A NEW DATATABLE
            dt = New DataTable
            'ADDING THE DATA TO THE DATATABLE.
            da.Fill(dt)
            Dim maxrow As Integer = dt.Rows.Count
            Dim type As String = dt.Rows(0).Item("utype")
            If maxrow > 0 Then
                MsgBox("You log on as " & type, , "Login as ")
                If type = "Administrator" Then
                    With Form1
                        .btnlogin.SetBounds(6, 90, 134, 23)
                        .btnmanageuser.Visible = True
                        .btnlist.Visible = True
                        .btnadd.Visible = True
                        .GroupBox1.Visible = True
                        .btnlogin.Text = "Logout"
                        .lblusername.Visible = True
                        .PictureBox1.Visible = False
                        .lblusername.Text = dt.Rows(0).Item("uname") & "::" & type

                    End With
                Else
                    With Form1
                        .btnlogin.SetBounds(6, 61, 134, 23)
                        .btnmanageuser.Visible = True
                        .btnlist.Visible = True
                        .btnadd.Visible = True
                        .GroupBox1.Visible = True
                        .lblusername.Visible = True
                        .PictureBox1.Visible = False
                        .btnlogin.Text = "Logout"
                        .lblusername.Text = dt.Rows(0).Item("uname") & "::" & type
                    End With
                End If
                LoginForm1.Close()
            Else
                MsgBox("account does not exist!", MsgBoxStyle.Exclamation, "Failed to login")
            End If
        Catch ex As Exception
            'CATCH ANY ERRORS.
            '  MsgBox(ex.Message)
            MsgBox("account does not exist!", MsgBoxStyle.Exclamation, "Failed to login")

        End Try
        'CLOSING THE CONNECTION.
        connection.Close()
    End Sub

#Region "REP"

    Public acscmd As New OleDb.OleDbCommand
    Public acsda As New OleDb.OleDbDataAdapter
    Public acscon As OleDb.OleDbConnection = connection()
    Public acsds As New DataSet
    Public strsql As String
    Public strreportname As String
    Public Sub reports(ByVal sql As String, ByVal rptname As String)
        acsds = New DataSet
        strsql = sql
        acscmd.CommandText = strsql
        acscmd.Connection = acscon
        acsda.SelectCommand = acscmd
        acsda.Fill(acsds)
        strreportname = rptname
        Dim strreportpath As String = Application.StartupPath & "\reports\" & strreportname & ".rpt"
        If Not IO.File.Exists(strreportpath) Then
            MsgBox("Unable to locate file:" & vbCrLf & strreportpath)
        End If
        Dim reportdoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
        reportdoc.Load(strreportpath)
        reportdoc.SetDataSource(acsds.Tables(0))
        report.CrystalReportViewer1.ShowRefreshButton = False
        report.CrystalReportViewer1.ShowCloseButton = False
        report.CrystalReportViewer1.ShowGroupTreeButton = False
        report.CrystalReportViewer1.ReportSource = reportdoc
    End Sub
#End Region

End Module
