Results 1 to 14 of 14

Thread: Access/VB Code Question

  1. #1
    Regular Member
    Join Date
    Oct 2003
    Location
    Roanoke, VA
    Posts
    182

    Access/VB Code Question

    i've got a contact type database here at work. i'm in charge of running reports, queries, and mantaining the database. my boss has the idea of making a input box come up when the database main form loads and having the employee enter their initials into the box so track their work. it will also help a lot b/c their are a lot of mistakes people are making and no one ever admits to it. i'd really like to put a stop to certain things. can someone please help me out with this? if you need anymore info then let me know. i have had 2 years of VB and 1 year of C++ so i'm familiar with coding. thanks!
    *~*Killing Machine*~*
    P4 2.53ghz | 512mb PC2700 DDR | ASUS P4S8X Mobo | ATI Radeon 9700 Pro | Soundblaster Live 5.1 Platinum w/ Live Drive | WD 120gig Hd | Maxtor 80gig Hd | Plextor PX-504A/SW 4x DVD-R | Plextor 48x24x48 CD-R | ThermalTake Xaser II Case / 430watt Antec Truepower PSU | Win2K Pro | HP 18.1" LCD Screen

    *~*Backup Computer*~*
    Celeron 400mhz | 256mb of pc100 | Maxtor 80gig Hd | nVidia TNT | ASUS Mobo | Sony 48x24x48 CD-R | Sony 52x CD-Rom | Win2K Pro | Sony Trinitron 17in Flat Screen

  2. #2
    SG Enthusiast Easto's Avatar
    Join Date
    Dec 2000
    Location
    So. California
    Posts
    4,968
    The easiest way is to setup some validation rules for each data entry point that you're having problems with. In other words only allowing numbers to be put in a phone number textbox and things like that.

    You can setup a routine that allows you to to record any changes to a database record at any point but that does take a bit more work. I do have a routine that does that. You set a "tag" on each field you wish to "monitor" and each time a change is made to that field it writes the UserInfo, FieldName, OldValue, NewValue and time it was done.

    If you're interested pm me and I'll supply the code.

  3. #3
    Regular Member
    Join Date
    Oct 2003
    Location
    Roanoke, VA
    Posts
    182
    what i wanted it to do was when you opened the main form of the database a box would come up asking for you initials. the user's initials would be put automatically entered into a text box for each record in the database. that way when i'm going through it i can see who edited a record so i know who's making the mistakes. also it will help me track how much work they are doing. thanks for your help man. my aim sn is phigity if you wanna chat on there.

    all i did was go to User/Group Accounts under Security and setup some users and pw's. then under the code editor, i put in this simple code:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    txtUser.Value = CurrentUser
    End Sub

    thanks for you help!
    *~*Killing Machine*~*
    P4 2.53ghz | 512mb PC2700 DDR | ASUS P4S8X Mobo | ATI Radeon 9700 Pro | Soundblaster Live 5.1 Platinum w/ Live Drive | WD 120gig Hd | Maxtor 80gig Hd | Plextor PX-504A/SW 4x DVD-R | Plextor 48x24x48 CD-R | ThermalTake Xaser II Case / 430watt Antec Truepower PSU | Win2K Pro | HP 18.1" LCD Screen

    *~*Backup Computer*~*
    Celeron 400mhz | 256mb of pc100 | Maxtor 80gig Hd | nVidia TNT | ASUS Mobo | Sony 48x24x48 CD-R | Sony 52x CD-Rom | Win2K Pro | Sony Trinitron 17in Flat Screen

  4. #4
    Regular Member
    Join Date
    Oct 2003
    Location
    Roanoke, VA
    Posts
    182
    if there is an easier way to do then please let me know or if there is another way that is better.
    *~*Killing Machine*~*
    P4 2.53ghz | 512mb PC2700 DDR | ASUS P4S8X Mobo | ATI Radeon 9700 Pro | Soundblaster Live 5.1 Platinum w/ Live Drive | WD 120gig Hd | Maxtor 80gig Hd | Plextor PX-504A/SW 4x DVD-R | Plextor 48x24x48 CD-R | ThermalTake Xaser II Case / 430watt Antec Truepower PSU | Win2K Pro | HP 18.1" LCD Screen

    *~*Backup Computer*~*
    Celeron 400mhz | 256mb of pc100 | Maxtor 80gig Hd | nVidia TNT | ASUS Mobo | Sony 48x24x48 CD-R | Sony 52x CD-Rom | Win2K Pro | Sony Trinitron 17in Flat Screen

  5. #5
    SG Enthusiast Easto's Avatar
    Join Date
    Dec 2000
    Location
    So. California
    Posts
    4,968
    What you're doing is just marking the record with the last user who played with that record. It does not tell you what was changed, what it was changed from. You also have to check every record to see if it was changed.

    Example: Someone creates a new record. Client "A" changes the record from 1 to 2. Later in the day Client "B" changes the record from 2 to 1. See where I'm going? You would have no record of Client "A"s changes.

    My approach is to flag each field of a record you want to watch. When that field in the record is changed it writes to a table that holds information about all the changes i.e. User, Timestamp, Old Value, New Value and when a User makes a change a dialog box opens up asking them for a brief description/reason the change was made and that is recorded too.

    I'll try and locate the code. I have it on a disk laying around here somewhere.

  6. #6
    SG Enthusiast FunK's Avatar
    Join Date
    Aug 2000
    Posts
    2,721
    Could you run two more databases?

    One that would hold the usernames and passwords and another to log the changes.

    That way you could authenticate against one DB and as stated above, write the changes to a seperate DB, who's only purpose is to log changes.

    username = slappy
    Password = dappy.

    Check the login against your username database to ensure that the person logged in correctly. If so allow them to make changes to the main DB.

    When they make a change to the database, as the information is inserted, it writes the change to the live DB and also writes the change (probably to include the time of the update) to the secondary database. Log the user name along with the change that was made (not an update, but written as a new row). The line above would be the previous change and the one inserted would be the latest. if you displayed this list by the date the change was made, you would see a chronological sequence of changes. You could also sort by user ID and then the time stamp to track all changes by a certain user or if your main DB can have any field changed by anyone, you could easily sort the fields as needed (like any other regular database).

    Of course the database that holds the changes will get rather large, but can be purged after it has been reviewed..

    Once you have the culprits isolated, you could remove the change database and the code that writes to it (comment it out). Everything will work as needed and if the situation presents itself, you could uncomment the code and start debugging again.

    This way, you add some extra security, can edit user accounts easily (by taking away or adding new users) and you don't mess around with the live database too much and can still log all your changes to the secondary DB.

    Not sure if this makes sense......
    Simply run adaware, spybot, ZoneAlarm, HijackThis, AVG, update windows daily, have a router, don't open e-mail, turn off action scripting, don't use P2P networks, don't violate EULAs, and wear a condom to get Windows secured.

    People say Linux is alot of work!

  7. #7
    Regular Member
    Join Date
    Oct 2003
    Location
    Roanoke, VA
    Posts
    182
    it makes sense to me.

    now i'm having a problem. i logged in as admin on one computer in our network then setup a few user accounts under access's security. on the computer i setup the accounts on everything worked fine. when i went to the next computer the login dialog box appeared but wouldn't accept any pw's. not even the admin login/pw. then i started up a few more computers in our dept and the login dialog box never appeared. when have the database on a server and shortcuts on everyone's workstation. can anyone give me some advice and/or a website i can go to in order to figure this out? not sure if this makes a different or not but but some people are running win2k and some are running winxp. thanks a lot!
    *~*Killing Machine*~*
    P4 2.53ghz | 512mb PC2700 DDR | ASUS P4S8X Mobo | ATI Radeon 9700 Pro | Soundblaster Live 5.1 Platinum w/ Live Drive | WD 120gig Hd | Maxtor 80gig Hd | Plextor PX-504A/SW 4x DVD-R | Plextor 48x24x48 CD-R | ThermalTake Xaser II Case / 430watt Antec Truepower PSU | Win2K Pro | HP 18.1" LCD Screen

    *~*Backup Computer*~*
    Celeron 400mhz | 256mb of pc100 | Maxtor 80gig Hd | nVidia TNT | ASUS Mobo | Sony 48x24x48 CD-R | Sony 52x CD-Rom | Win2K Pro | Sony Trinitron 17in Flat Screen

  8. #8
    SG Enthusiast Easto's Avatar
    Join Date
    Dec 2000
    Location
    So. California
    Posts
    4,968
    Exactly how is your database setup?

    Do you have shortcuts to the 1 main database on a server somewhere or do you have a backend/frontend system setup?

    Frontend/Backend: The database with the Tables is located on a server and on each client is a frontend that contains all the Forms, Reports etc and contains "Linked" tables to the database on the server.

  9. #9
    Regular Member
    Join Date
    Oct 2003
    Location
    Roanoke, VA
    Posts
    182
    1 main database on the server with shortcuts placed on the workstation's desktops. i told them they should do a frontend/backend b/c it helps with speed. am i correct? to do a filter/lookup takes about 30sec. we have about 97,XXX records in the database.
    *~*Killing Machine*~*
    P4 2.53ghz | 512mb PC2700 DDR | ASUS P4S8X Mobo | ATI Radeon 9700 Pro | Soundblaster Live 5.1 Platinum w/ Live Drive | WD 120gig Hd | Maxtor 80gig Hd | Plextor PX-504A/SW 4x DVD-R | Plextor 48x24x48 CD-R | ThermalTake Xaser II Case / 430watt Antec Truepower PSU | Win2K Pro | HP 18.1" LCD Screen

    *~*Backup Computer*~*
    Celeron 400mhz | 256mb of pc100 | Maxtor 80gig Hd | nVidia TNT | ASUS Mobo | Sony 48x24x48 CD-R | Sony 52x CD-Rom | Win2K Pro | Sony Trinitron 17in Flat Screen

  10. #10
    SG Enthusiast Easto's Avatar
    Join Date
    Dec 2000
    Location
    So. California
    Posts
    4,968
    Ok,

    First you need to start reading the help files in Access regarding " record locking" strategies. Also, if it's take 30 seconds to retrieve even a complex querys results I'd say that something is wrong. I've run Access Databases with 100,000+ records and even my most complex reports take about 5-7 seconds to show results.

  11. #11
    Regular Member
    Join Date
    Oct 2003
    Location
    Roanoke, VA
    Posts
    182
    Originally posted by Easto
    Ok,

    First you need to start reading the help files in Access regarding " record locking" strategies. Also, if it's take 30 seconds to retrieve even a complex querys results I'd say that something is wrong. I've run Access Databases with 100,000+ records and even my most complex reports take about 5-7 seconds to show results.
    i use help files a lot but couldn't find one on what i wanted. as the database is concerned, i didn't built it. there are a lot of things that should be done a different way but prob in a few months we are switching from access to sql so i'm not worried about redoing the entire DB
    *~*Killing Machine*~*
    P4 2.53ghz | 512mb PC2700 DDR | ASUS P4S8X Mobo | ATI Radeon 9700 Pro | Soundblaster Live 5.1 Platinum w/ Live Drive | WD 120gig Hd | Maxtor 80gig Hd | Plextor PX-504A/SW 4x DVD-R | Plextor 48x24x48 CD-R | ThermalTake Xaser II Case / 430watt Antec Truepower PSU | Win2K Pro | HP 18.1" LCD Screen

    *~*Backup Computer*~*
    Celeron 400mhz | 256mb of pc100 | Maxtor 80gig Hd | nVidia TNT | ASUS Mobo | Sony 48x24x48 CD-R | Sony 52x CD-Rom | Win2K Pro | Sony Trinitron 17in Flat Screen

  12. #12
    SG Enthusiast FunK's Avatar
    Join Date
    Aug 2000
    Posts
    2,721
    That would have been my suggestion a loooong time ago. SQL is sooooo much better than access in soooo many different ways..

    Good luck.. you'll love SQL.
    Simply run adaware, spybot, ZoneAlarm, HijackThis, AVG, update windows daily, have a router, don't open e-mail, turn off action scripting, don't use P2P networks, don't violate EULAs, and wear a condom to get Windows secured.

    People say Linux is alot of work!

  13. #13
    Regular Member
    Join Date
    Oct 2003
    Location
    Roanoke, VA
    Posts
    182
    Originally posted by FunK
    That would have been my suggestion a loooong time ago. SQL is sooooo much better than access in soooo many different ways..

    Good luck.. you'll love SQL.
    i know nothing about SQL. i hope i'm not SOL! ha ha ha....j/p. i could use a good webiste if anyone knows of one that teachs SQL. thanks!
    *~*Killing Machine*~*
    P4 2.53ghz | 512mb PC2700 DDR | ASUS P4S8X Mobo | ATI Radeon 9700 Pro | Soundblaster Live 5.1 Platinum w/ Live Drive | WD 120gig Hd | Maxtor 80gig Hd | Plextor PX-504A/SW 4x DVD-R | Plextor 48x24x48 CD-R | ThermalTake Xaser II Case / 430watt Antec Truepower PSU | Win2K Pro | HP 18.1" LCD Screen

    *~*Backup Computer*~*
    Celeron 400mhz | 256mb of pc100 | Maxtor 80gig Hd | nVidia TNT | ASUS Mobo | Sony 48x24x48 CD-R | Sony 52x CD-Rom | Win2K Pro | Sony Trinitron 17in Flat Screen

  14. #14
    Cabledude Avatar Fan purecomedy's Avatar
    Join Date
    Feb 2000
    Location
    Canada
    Posts
    1,354
    Some may disagree with me, but I have found that I learned the most about SQL by taking examples and ripping them apart. Obviously you want to start out with easy ones first and work your way up.

    One of the more annoying things I have notice lately is that the syntax between Access SQL and SQLServer/Oracle SQL is different. Like around a date in Access you use # like this #01/01/2004# but any other database you use single quotes like this '01/01/2004'. We still have Access 97 at work, I wonder if they stick with the # around dates in Access XP or 2003 (if you know, tell me!). In adding some functionality I believe that Oracle causes the odd bonehead thing to happen as well.

    Access is the least powerful, but still in most ways most user-friendly database.

Bookmarks

Posting Permissions

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