Page 1 of 4 123 ... LastLast
Results 1 to 15 of 57
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    556
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Database Maintenance (2000/SR1)

    This isn't really a programming issue or even specifically an Access issue, more of a bookkeeping item.
    I work at a small production plant, about 35 production workers and 10 office and management. I put together a database to keep track of employee information, name, address, job qualifications, training, vacations, attendance, etc. It works well, many thanks to this group for helping me in the development.
    Here is my question, we operate 24/7, so we have 3 different supervisors, plus an administrative assistant, as well as a production manager and plant manager, that all have access to the database to update information. As I stated earlier, we are a small plant, so everybody does many different jobs. If Joe Blow changes his phone number, he might tell his supervisor, the supervisor updates the database and everything is fine, except nobody else knows the information was changed, so if they hear that the phone number changed, then they are wondering if it was updated. As you can imagine, there are a lot of other items that can change. (I do have it set up so that the real important stuff like attendance and vacations, tracks the entries by date and user, also, only the administrator can delete any of those types of items.)
    I talked to the users about some way to keep track of changes, as well as informing everyone else of any updates. What they came up with is to have some cards made up, where the information would be filled in, as well as a spot to note who made the changes and when. I really liked the database, because it got us away from paper and now we are talking about putting in a new paper system to keep track of our computer system.
    I am curious as to how anyone else handles this type of information flow. Any help or guidelines would be appreciated.

  2. #2
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Maintenance (2000/SR1)

    Hi, Is your database a secured database using the Access workgroup admisistrator? If so what I use on my user forms is 2 fields that display the date of the last update and the user name of who did the last update. If you think this would work for you post back and I will look up the code.

    Carla

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    556
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Database Maintenance (2000/SR1)

    Hi Carla,

    Yes, it is secured and I do have fields set up to track who added information in what I consider "critical" records. What I am looking for is a simple way to keep track of "incidental" changes, an e-mail address or cell phone number in an employees file and I didn't really want to add tracking for everything, I don't think. Trying to keep it as simple as I can, while at the same time allowing many different people to update information. That may be the mistake on my part. Thanks for your help.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Maintenance (2000/SR1)

    I'm going to add to his question by asking this...is there a way to set up a macro that upon data entry of an existing record that something along these lines is sent to another table, maybe called "Database Updates"? Say the phone number changes, the macro would automatically make an entry in the Database update table with today's date, and a string signifying the primary key and the field changed? I'm not that good with script or anything along those lines, however I'll be watching to see what anyone else comes up with.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Database Maintenance (2000/SR1)

    Adding to the suggestion by Carla, as long as you don't let people work at the table level, you can update the who and when changed fields using code on the forms. Then you can create a simple form or report that identifies all the changes made in the last week or two. We almost always put the two fields in our table design - it helps identify people with training problems as well as tracking the rate of change. If you want to get more sophiticated, you might think about using a SQL Server back-end. SQL Server has a feature called triggers that will let you track changes even at the table level, and we actually use that capability to archive data prior to the change so if someone goofs and changes the wrong record, you can go back and restore the original data.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Maintenance (2000/SR1)

    Wendell,
    What SQL Server do you recommend? And is my suggestion possible or just bunk? If it is possible, and not too much of a pain, could you give me an example code, as I'm still not yet familiar with all of VBA's options...Thanks!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Database Maintenance (2000/SR1)

    In <post#=320697>post 320697</post#>, you'll find code for a simplistic audit trail. It only records whether a record has been added, modified or deleted. See Creating an Audit Log on Allen Browne's website for a more complete audit trail.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Maintenance (2000/SR1)

    Hans, I tried inserting that code, creating a table (tblChanges), and I followed all your instructions, but I get the following message: "Item cannot be found in the collection corresponding to the requested name or ordinal." Help?!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Database Maintenance (2000/SR1)

    We prefer SQL Server 2000 - 2005 isn't too far around the corner. The MSDE that ships with Access 2000 is SQL Server 7 - it works reasonably well, but the throttling mechanism they used makes it pretty much worthless if you have more than 5 users on a ADP based system. If you use ODBC with it, it rolls over at users, sometimes just 2. Access 2002 and 2003 ship with an MSDE based on 2000, and it holds up fairly well up to 5 users in either scenario as they used a different approach to throttling it.

    As to you question about macros, you can't really do much with them in auditing. With VBA you can, but you basically have to revert to unbound forms, and do everything in VBA. That makes the development process much less efficient - you might as well use regular Visual Basic. With SQL Server I can continue to use bound forms, and even if somebody gets in at the table level I can still see and archive their changes. In addition, SQL Server is much more robust, has a logging feature, built-in backup capability and a host of other advantages as a back-end - but it does cost more.
    Wendell

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Maintenance (2000/SR1)

    Could you provide me any more guidance to applying the code Hans suggests first in his post?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Database Maintenance (2000/SR1)

    You will need to provide the line where the code fails in order to be able to help with it. It could however be that you are running Access 2000, while his code was running Access 2002. He uses ADO in his code to write out the fact that a given table was changed, and unless you've updated to the latest version of ADO, you may have issues with that. ADO was introduced in Access 2000, and was frankly a bit flakey and pretty limited. You could also choose to do the same thing using DAO. He uses a character flag to indicate the kind of edit that was made. Again this only works if you are making changes via a form, and the code has to be in modules behind the form, using the event names indicated.
    Wendell

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Maintenance (2000/SR1)

    Ohhhh, well I'm using windows 2000. I don't know what line the code fails on though. also, 1) I didn't know it had to be in a module, and 2) I really dont want to do it on a form, I want to do it in a table (Is that possible?). Also - what script would I use to get a user's name from a networked computer? I'm trying this:

    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Function fOSUserName() As String
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If ( lngX > 0 ) Then
    fOSUserName = Left$(strUserName, lngLen - 1)
    Else
    fOSUserName = vbNullString
    End If
    End Function


    But when I try to set fOSUserName() as the control source in one of my text boxes, it gives me #Error.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Database Maintenance (2000/SR1)

    You can NOT keep track of changes at the table level in Access. There are no triggers/events that occur when a user modifies data directly in a table or query. You can only track changes in a form. That's why Wendell suggested SQL Server - this DOES have triggers at the table level.

    The function is correct, and it should work in any Windows version, but you must set the control source of the text box to <code>=fOSUserName()</code>. The = is obligatory.

  14. #14
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Maintenance (2000/SR1)

    Hmmm, well I don't have the sort of influence to get an SQL server just so I can track changes....I guess everyone will have to suck it up with read-only tables/queries, and edit ALL info in form view. Hans, the code you gave us, is it incomplete? As in, do I have to put in the parts for Deletions/Modifications?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  15. #15
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Maintenance (2000/SR1)

    Hans, I just tried the =fOSUserName(), and still bunk. It gives me the error #Name? You said the code was good, so now I've got no clue. I used an excerpt from your earlier code to get the windows user name, GetUserName(), and it worked beautifully, on my personal computer. However, when I tried to go to a networked computer, no luck. That's when I tried the networked code, and that too failed. We're running Windows 2000 Professional, if that means anything...thanks!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Page 1 of 4 123 ... LastLast

Posting Permissions

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