Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Question Unanswered: Problems creating an activity log

    I am attempting to create an activity log to keep track of the users that log into the database I have created and record what forms and reports they are using.

    First of all let me explain a little bit about the database. The Database is broken up into 2 pieces, the main MDB file that has all of the Forms, Reports, Queries, and Macros and is linked to a second MDB file that has all of the tables and data that needs to be stored. There is a Table that has all of the Usernames, Passwords and AccessLevels for the users to Login to the database.

    Here is where it gets strange.

    I am wanting to write from VB on the close of the successful login screen to a table in the main MDB the Username and AccessLevel that users has so I can reference it later for the activity log and to see if that user can even access that form or report. I chose to store this information in a table so it will not use system memory on the local machine.

    What I need to know is, how I can write data to a specific table from the event procedure's VB code. You will have to excuse me because this is my first REAL venture into VB and I am kinda clueless as to how this should be done.

    If you have any other questions about how the database is set up please feel free to ask I am always at a computer. Isnt it funny how they ask you to build a simple database and it grows exponentially out of control, oh well at least its user friendly and faster than doing it by hand.

    -Tony

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I think what you are saying is that you want to store the current user and the user's access level for the duration of this session so you can reference it when needed.

    There are three approaches to this situation. One is to do as you are currently thinking. Store into a local table, then lookup when needed.
    Two would be to create a class and store the user name and access level in a class property.
    And third, is how I have always done it. I have a form that is always open, but hidden that keeps my global type of variables. Because these variables are on a form rather than in a class, they can be accessed from a query, which I have found VERY useful.

    If you choose to keep this information in a local table, be sure to only allow one record in that table. Best way here is to have a function the writes the current user and access level to the table, but first does a DELETE * FROM tblCurrentUser; This way, when you do a lookup, you don't need any criteria to find the correct record. The DLookup would look like:
    MyCurrentUserName = DLookup("CurrentUser", "tblCurrentUser")
    MyCurrentUserAccessLever = DLookup("AccessLevel", "tblCurrentUser")

    Hope this helps with a few possible ideas.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how you store the information is up to you.. Id of thought that it would probably make more sense to store it centrally
    you could write a module that performed that task and then call it as required from the form/report on load, and on close events

    your common audit function could pick up the userid and say computer name from say the API calls, so all you would need to do is say supply it with the form/report name, and probably a type so you can easily identify which obejct is being opened.

    could egt a bit big brother ish.. if you have people who are apprehensive about a perceived snoop process being done. I dread to think what would happen if you were to try and implement this inside a heavily unionised or government organisation and they found out about it through the back door
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    I am thinking about the first method listed by GolferGuy of storing user information in a table is the best method for me because I can also then tell what the last user is that loged into the database from that computer. I just need to know how to get it into a table. I have about 8 computers I need to install this database on and they have plenty of hard drive space but not much in the way of memory so anything to minimize the memory usage would be great!

    I already know the DLookUp command and use it to access the table for the error checking for the username (from a pull down list of users) and password (referenced with the username) that it stored in the user table to make sure the username and password info is matching. I really need to know how to transfer it from the VB script after the user correctly verifies the username and password into the table to store the username and access level so I can quickly pull it up later.

    I have already informed the Powers that Be about the Activity log that I am working on and they think it is a useful feature since this database will track changes and claims for a Community Service Program and help spot people trying to defraud the system. Its a C.Y.A. for me to keep me out of heat for someone even trying to defraud the system and not being able to detect it.

    If you like, later I can tell you what the entire database actually does, I am quite proud of the progress I have made on it and the features I have built into the system.


    -Tony

    Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far,the Universe is winning.

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Tony,
    To add user name and access level to the table, a simple SQL statement would be in order.
    Code:
    INSERT INTO tblCurrentUser ( CurrentUser, AccessLevel )
    SELECT [MyCurrentUser] AS CurrentUser, [MyAccessLevel] AS AccessLevel;
    Of course you would need to insert your own "MyCurrentUser" and "MyAccessLevel" fields into this SQL string before executing it. If you look at the Access Design Tips in my signature, you will find a link to a downloadable utility that will change this SQL string into the VBA code needed to populate these two variables (which you would need to get into your VBA code) into the correct spot in the SQL code so it could be executed correctly.
    Here is the VBA code from that utility. You will need to change the names in this sample code to the table and field names you are already using.
    Code:
    strSql = "INSERT INTO tblCurrentUser  " & vbCrLf & _
    "( CurrentUser, AccessLevel ) " & vbCrLf & _
    "SELECT  " & vbCrLf & _
    "'" & [MyCurrentUser] & "' AS CurrentUser,  " & vbCrLf & _
    "'" & [MyAccessLevel] & "' AS AccessLevel;"
    This code is written for both Current User and Access Level being text fields.

    HTH,

  6. #6
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Thanks a lot man, Thats exactly what I needed. Now to create all of the event procedures to add to the records to the activity log.

    now with that SQL statement is it always going to overwrite the first entry in the tblCurrentUser table or should I delete and recreate the table on opening the login form?

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Clearing the table is a whole lot nicer way to go, rather than deleting and recreating the table.

    DELETE * FROM tblCurrentUser;

    That will do it, delete every single record in the table. It is sort of something that is VERY powerful, AND very dangerous. No more dangerous than deleting the whole table though.

    You are very welcome.

  8. #8
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37


    ok... I am showing how green my gills are, how so I have the strSql run in the VB?

    I will get this eventually...

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    CurrentDB.Execute strSQL

    This will require you to be sure that DAO is one of the references in the VBA screen. While in the VBA editor, use the Tools menu, References, then scroll down to Microsoft DAO ....????.... Be sure to check the highest numbered copy if there are more than one available. Before scrolling, check to be sure it is not already checked as one of those that are checked at the top of the list.

  10. #10
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Thanks a lot Vic!, ok just got one more question for ya...

    I am now trying to do the same things but send the information to the activity log and I think my Time and Date information is screing it up. How do I change the SQL statement to accept Time/Date Fields. This is what I tried.

    Code:
            StrUser = DLookup("Username", "tblUserInfo", 1)
            StrUserLevel = DLookup("AccessLevel", "tblUserInfo", 1)
            StrClientID = 0
            StrAction = "Logon to System"
            StrTimeDate = Now()
            
            strSql = "INSERT INTO tblActivityLog  " & vbCrLf & _
            "( Username, UserAccessLevel, ClientID, Action, Time, Date ) " & vbCrLf & _
            "SELECT  " & vbCrLf & _
            "'" & [StrUser] & "' AS Username,  " & vbCrLf & _
            "'" & [StrUserLevel] & "' AS UserAccessLevel,  " & vbCrLf & _
            "'" & [StrClientID] & "' AS ClientID,  " & vbCrLf & _
            "'" & [StrAction] & "' AS Action,  " & "#" & _
            "'" & [StrTimeDate] & "# ' AS Time,  " & "#" & _
            "'" & [StrTimeDate] & "# ' AS Date;"
    I know having a field for time and a field for date is redundant but I am thinking about pure ease of use for the supervisors to read since the log will be openable by a read only form in datasheed view.

    I will again thank you in advance because I know this is very simple to you.

    -Tony

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    First, sorry you did not download my utility. It would have done this for you with no problem.
    OK, to fix your situation.
    1. When dealing with dates, you put the pound sign (#) on each side of the date, but you do NOT put any quotes there. So, in your SQL statement, remove the quotes.
    2. ONLY have ONE DateTime field in your record. You said you will be displaying this with a read only form. On the form is where you put the two fields if you want to display date and then time in separate fields.
    3. DO NOT use reserved words for column names. I see both Time and Date as column names. Access will require brackets "[]" to be around those names, otherwise Access will think you are taking about Time() and Date().

  12. #12
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Ok let me review over that and make the changes. I did not download the utility because I am quite new to this and dont even understand fully how to create a SQL query from scratch. I am sure I will learn I just need a bit of time to review over it. I did how ever read through the manual for your program before I sent you the message wondering how to do it.

    After I sent it I thought about that and how I could seperate the time and date in the Datasheet form and just change the formatting which I have already done.

    I was actually happy with myself for figuring how to create a dropdown list that removes values that have been assigned to another table. Example: we are issuing food cards to individuals that need assistance, when a food card is issued it removes it from the list of available cards and only allows you to issue cards that are available. Keeps people from doubble issueing cards and creates a running list of what has and nas not been issued.

    I can do things like that but SQL is entirely new to me.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by GolferGuy
    pound sign (#)
    Errr, no..?
    That's a hash, not a £
    George
    Home | Blog

  14. #14
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    But George, Tony is from the Colonies too, so he would understand the pound sign! If he was from somewhere else I would not have said the dollar sign.
    Thanks!

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I fully don't understand that last statement...
    George
    Home | Blog

Posting Permissions

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