Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2015
    Posts
    1

    Answered: I need help reorganizing a database in Access 2010

    Hello,
    I need help reorganizing an Event Reporting Database. I am currently working in Access, looking to transfer the data over to SQL server, but for right now I would like to organize it in Access 2010 first. Some of the ideas that I have thought of I googled to try and figure out how to do them successfully and I am having trouble getting some of them to work. I can attach the Access file if needed. Here are some of the ideas that I have thought of.

    Allow users to log in and display events only submitted to them via Active Directory.
    • This will add security and organization
    • Admins will be able to view all events
    • When risk management checks an event, they can choose to elevate it to a supervisor, or investigate themselves.
    • Status of events (Investigating, Inv Complete, Resolved)
    • Events can be sorted from newest->oldest
    • Add an option for priority of event (low, medium, high) to be determined by supervisor of the event.
    • Allow filtering of events by category and sub-category, priority, date.
    • Form validation
    • Consider migrating database to SQL (may be out of my scope, and may take considerable amount of time)
    • Change the PDF into a web form.
    • Normalize the database.
    • Email will have link directly to the corresponding event. No need to type in the event number.
    • ***Form only works in IE***



    Column / Field changes

    1. Supervisor responsible for Investigation of Event
    a) Field should automatically be filled with username, changed to Person responsible.
    2. Date and time received should be automatically saved and filled.
    3. Date investigation completed will be automatically filled when event is marked as completed and submitted.

    Column type changes:

    Time of Incident datatype changed to Date
    DOB changed to Date
    Type of incident broken down into separate tables with sub-categories
    Use Now() to add a timestamp to new and updated records.
    Update to Access 2010.
    Convert to ACCDB format and encrypt it with a password.
    Backup the database and set a backup schedule if needed.


    If someone could help me figure out how to do some of these in Access that would be greatly appreciated. I am willing to work with you, and I am also willing to learn.
    Thanks

  2. Best Answer
    Posted by Sinndho

    "Here are some general ideas.

    1. If it's not already done, split the database: Front-end is the user's interface and contains the forms, reports, queries, modules and macros (if possible, try to refrain from using macros, except perhaps AutoExec), Back-end contains tables. Each user has a local copy of the F-E, while the B-E is on a network share. This will also help you when you decide to port the data to a SQL Server.

    2. Assign priorities to your list of changes. These priorities should be based both on the importance of a feature in the list and on some logical flow (some changes will be easier if other changes are alreday implemented).

    3. Don't try to change everything at once. Work step by step and carefully test and validate a change before moving to the next.

    4.When you normalize the tables, don't go beyond the 3rd normal form, unless there is an absolute necessity: Boyce–Codd normal form, 4rth normal form and over often necessitate to de-normalize the data when using the system. This can be a pain in the neck when working with a SQL Server and a greater one when working with Access.

    5. Practically, getting the user's Id from Active Directory is easy:
    Code:
    Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Public Function GetNTUser() As String
    '
    ' Retrieve the ID of the currently connected user
    '
        Dim strUsername As String
        Dim lngUserNameSize As Long
        
        strUsername = String$(255, 0)
        lngUserNameSize = Len(strUsername)
        GetUserName strUsername, lngUserNameSize
        strUsername = Left$(strUsername, lngUserNameSize - 1)
        GetNTUser = strUsername
        
    End Function
    In a simple security sybsystem, you could have a table that contains the names of all users allowed to work with the database with a column that specifies the level of privilege (Long Interger: each bit specifies a privilege). GetNTUser() is called immedialely when the database (F-E) is open, it's returned value is compared with the contents of the Users table and a level of privilege is granted to the user (it can be a public variable of, better, a Class. Here's an example:
    a) To create a (very) simple table to store the users and their privilege:
    Code:
    Sub CreateTbl_Users()
    
        Const c_SQL As String = "CREATE TABLE Tbl_Users (SysCounter COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
                                "User_Name TEXT(64) NOT NULL, User_Privilege INTEGER NOT NULL, Inactive BIT NOT NULL);"
                            
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        ' Create the table.
        '
        CurrentDb.Execute c_SQL, dbFailOnError
        
        ' Access DDL statements do not allow to specify a default value for
        ' the columns of a table when creating it --> must use DAO.
        '
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("Tbl_Users")
        Set fld = tdf.Fields("User_Privilege")
        fld.DefaultValue = 0
        tdf.Fields.Refresh
        Set fld = tdf.Fields("Inactive")
        fld.DefaultValue = 0
        tdf.Fields.Refresh
        Set fld = Nothing
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    b) A class (Cls_Users) to identify the logged user and retrieve its privileges (if any):
    Code:
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Private m_strUserName As String
    Private m_lngPrivilege As Long
    
    Private Function GetNTUser() As String
    '
    ' Retrieve the ID of the currently connected user
    '
        Dim strUsername As String
        Dim lngUserNameSize As Long
        
        strUsername = String$(255, 0)
        lngUserNameSize = Len(strUsername)
        GetUserName strUsername, lngUserNameSize
        strUsername = Left$(strUsername, lngUserNameSize - 1)
        GetNTUser = strUsername
        
    End Function
    
    Private Function GetPrivilege(ByVal UserName As String) As Long
    
        Const c_Criteria = "User_Name = '@N' AND Inactive = False"
        
        Dim strCriteria As String
        
        strCriteria = Replace(c_Criteria, "@N", UserName)
        If DCount("*", "Tbl_Users", strCriteria) = 0 Then
            MsgBox "You are not allowed to use this application.", vbInformation, "Access denied"
            Application.Quit
        End If
        GetPrivilege = DLookup("User_Privilege", "Tbl_Users", strCriteria)
    
    End Function
    
    Public Property Get Privilege() As Long
    
        Privilege = GetPrivilege(m_strUserName)
        
    End Property
    
    Private Sub Class_Initialize()
        
        If DCount("*", "MSysObjects", "Name = 'Tbl_Users'") = 0 Then
            MsgBox "Tbl_Users is missing.", vbInformation, "Cannot contunue"
            Application.Quit
        End If
        m_strUserName = GetNTUser
        GetPrivilege m_strUserName
        
    End Sub
    c) the code to use this security schema:
    Code:
    ' Declaration section of a standard module.
    '
    Public m_clsUsers As Cls_Users
    
    Public Function Startup()
    
        Set m_clsUsers = New Cls_Users
        If m_clsUsers.Privilege = 0 Then
            MsgBox "You don't have been granted any privilege in this application.", vbInformation, "No privileges"
            Application.Quit
        End If
        
    End Function
    You can then call StartUp() from the AutoExec macro or from the Form_Open() event handler of the startup form.

    When you need to check the level of privilege of the user before performing an action, you can use:
    Code:
         Const Priv_OpenReport As Long = 16
    
        If (m_clsUsers.Privilege And Priv_OpenReport) <> 0 Then DoCmd.OpenReport ...
    "


  3. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here are some general ideas.

    1. If it's not already done, split the database: Front-end is the user's interface and contains the forms, reports, queries, modules and macros (if possible, try to refrain from using macros, except perhaps AutoExec), Back-end contains tables. Each user has a local copy of the F-E, while the B-E is on a network share. This will also help you when you decide to port the data to a SQL Server.

    2. Assign priorities to your list of changes. These priorities should be based both on the importance of a feature in the list and on some logical flow (some changes will be easier if other changes are alreday implemented).

    3. Don't try to change everything at once. Work step by step and carefully test and validate a change before moving to the next.

    4.When you normalize the tables, don't go beyond the 3rd normal form, unless there is an absolute necessity: Boyce–Codd normal form, 4rth normal form and over often necessitate to de-normalize the data when using the system. This can be a pain in the neck when working with a SQL Server and a greater one when working with Access.

    5. Practically, getting the user's Id from Active Directory is easy:
    Code:
    Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Public Function GetNTUser() As String
    '
    ' Retrieve the ID of the currently connected user
    '
        Dim strUsername As String
        Dim lngUserNameSize As Long
        
        strUsername = String$(255, 0)
        lngUserNameSize = Len(strUsername)
        GetUserName strUsername, lngUserNameSize
        strUsername = Left$(strUsername, lngUserNameSize - 1)
        GetNTUser = strUsername
        
    End Function
    In a simple security sybsystem, you could have a table that contains the names of all users allowed to work with the database with a column that specifies the level of privilege (Long Interger: each bit specifies a privilege). GetNTUser() is called immedialely when the database (F-E) is open, it's returned value is compared with the contents of the Users table and a level of privilege is granted to the user (it can be a public variable of, better, a Class. Here's an example:
    a) To create a (very) simple table to store the users and their privilege:
    Code:
    Sub CreateTbl_Users()
    
        Const c_SQL As String = "CREATE TABLE Tbl_Users (SysCounter COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
                                "User_Name TEXT(64) NOT NULL, User_Privilege INTEGER NOT NULL, Inactive BIT NOT NULL);"
                            
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        ' Create the table.
        '
        CurrentDb.Execute c_SQL, dbFailOnError
        
        ' Access DDL statements do not allow to specify a default value for
        ' the columns of a table when creating it --> must use DAO.
        '
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("Tbl_Users")
        Set fld = tdf.Fields("User_Privilege")
        fld.DefaultValue = 0
        tdf.Fields.Refresh
        Set fld = tdf.Fields("Inactive")
        fld.DefaultValue = 0
        tdf.Fields.Refresh
        Set fld = Nothing
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    b) A class (Cls_Users) to identify the logged user and retrieve its privileges (if any):
    Code:
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Private m_strUserName As String
    Private m_lngPrivilege As Long
    
    Private Function GetNTUser() As String
    '
    ' Retrieve the ID of the currently connected user
    '
        Dim strUsername As String
        Dim lngUserNameSize As Long
        
        strUsername = String$(255, 0)
        lngUserNameSize = Len(strUsername)
        GetUserName strUsername, lngUserNameSize
        strUsername = Left$(strUsername, lngUserNameSize - 1)
        GetNTUser = strUsername
        
    End Function
    
    Private Function GetPrivilege(ByVal UserName As String) As Long
    
        Const c_Criteria = "User_Name = '@N' AND Inactive = False"
        
        Dim strCriteria As String
        
        strCriteria = Replace(c_Criteria, "@N", UserName)
        If DCount("*", "Tbl_Users", strCriteria) = 0 Then
            MsgBox "You are not allowed to use this application.", vbInformation, "Access denied"
            Application.Quit
        End If
        GetPrivilege = DLookup("User_Privilege", "Tbl_Users", strCriteria)
    
    End Function
    
    Public Property Get Privilege() As Long
    
        Privilege = GetPrivilege(m_strUserName)
        
    End Property
    
    Private Sub Class_Initialize()
        
        If DCount("*", "MSysObjects", "Name = 'Tbl_Users'") = 0 Then
            MsgBox "Tbl_Users is missing.", vbInformation, "Cannot contunue"
            Application.Quit
        End If
        m_strUserName = GetNTUser
        GetPrivilege m_strUserName
        
    End Sub
    c) the code to use this security schema:
    Code:
    ' Declaration section of a standard module.
    '
    Public m_clsUsers As Cls_Users
    
    Public Function Startup()
    
        Set m_clsUsers = New Cls_Users
        If m_clsUsers.Privilege = 0 Then
            MsgBox "You don't have been granted any privilege in this application.", vbInformation, "No privileges"
            Application.Quit
        End If
        
    End Function
    You can then call StartUp() from the AutoExec macro or from the Form_Open() event handler of the startup form.

    When you need to check the level of privilege of the user before performing an action, you can use:
    Code:
         Const Priv_OpenReport As Long = 16
    
        If (m_clsUsers.Privilege And Priv_OpenReport) <> 0 Then DoCmd.OpenReport ...
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •