March 19th, 2013
Another common need for most database administrators & developers is to be able to see who is logged into a database. A very common solution to this in the past has been to use the Jet UserRoster, see:
http://support.microsoft.com/kb/285822
http://support.microsoft.com/kb/q176670
http://www.utteraccess.com/forum/Who-s-Logged-Who-s-Con-t1897146.html
http://www.rogersaccesslibrary.com/misc/misc.htm (see: WhosOn97)
I personally find much more useful to create a table to log the database usage by the users and have a form open in hidden mode at the database startup to populate the table in question. There is also the added benefit that this method permit much more detailed information, and historical review (not possible with the UserRoster). The historical data can be very useful when determining users that did not disconnect properly (which can lead to corruption) from the database. The extra information can also be useful when troubleshooting problems.
So how can you create this simple log tracking form? Very easily and here is how:
- First, we need to create a table to log the comings and goings within our database. Please note that you can add or remove the quantity (number of fields) you wish to keep track of. To do so you can use the following Data Definition Language (DDL) to create the corresponding MS Access table:
CREATE TABLE tbl_Db_UserTracking
(UTEntryID AUTOINCREMENT PRIMARY KEY,
OSUserName VARCHAR(255) NOT NULL,
ComputerName VARCHAR(255),
ComputerIP VARCHAR(20),
DbEntry datetime NOT NULL,
DbExit datetime); |
- Then we need to create a login tracking form which will get open at startup in a hidden mode (so the user never actually sees it). Below is one example of this form. Since the form is never actually visible, you need not waste any time formatting it or performing any special layout… A bare minimum is required.
- Then we need to setup a method to automatically launch the form at startup. Since all of my databases use an AutoExec macro to perform a number of steps (relink the table, check user permissions, stratup logoff forms, …) at starup it is only logical to merely add this to the function that the AutoExec macro calls.
- Then we need to create a form for the administrator to use to review the data.
Share and Enjoy
MS Access, MS Access General Information | 
No Comments »
June 9th, 2012
I was looking for a simple way to reproduce a web style expandable sidebar (accordian subform, expandable subform, sliding subform, shutter subform, or whatever name you'd like to use to describe it in MS Access), instead of merely making a subform visible/invisible. The attached file, does exactly that and with a single change to a constant variable you can control the speed of the shutter/slidder.
This is a very crude example to illustrate the principle. You can use any subform you like, within any form you'd like. There are no requirements to make this work and all the code is native MS Acces code, no external references, or ActiveX Controls. The button used to expand and retract the subform can also be changed as you see fit, it is just a question of placing the brief code behind whatever button you select to use in your design.
Just another nifty method to add a little wow factor to a form.
Sliding/Shutter Subform Example Download
Share and Enjoy
MS Access Forms, MS Access Samples | 2 Comments »
November 23rd, 2010
This database demonstrates how to insert a clock in your forms to display the current time as well as a demonstration of how to create a timer used to determine the amount of time which a form has been open for.
Download the sample database: MS Access Clocks and Timers Sample Database
Share and Enjoy
MS Access Samples | Comments Off
November 22nd, 2010
This database demonstrates some of the most common ways to work with dates. It demonstrates how to extract only a part of a date (year, month or day), how to display a date in different formats (ISO, American,…), how to determine the number of days, weeks between 2 dates, etc.
Download the sample database: MS Access Working with Dates Sample Database
Share and Enjoy
MS Access Samples | Comments Off
November 20th, 2010
This database example show the proper way to work with images within a database (which is not to embed them into the database).
Download the sample database: MS Access Images Sample Database
Share and Enjoy
MS Access Samples | Comments Off
November 19th, 2010
If you are using user-level security (ULS), the following is a simple demonstration of a form that can be added to any database to permit the user the ability to change their password. Useful when you create an mde or disable the standard toolbars for increased security but still want to give the user the ability to rotate their own password.
Download the sample database: User-Level Security Change of Password Form Demonstration
Special Note & Warning
Be very careful using this sample. If you run it on a database which does not have a designated mdw security database assigned, it will alter the system.mdw, thus affecting ALL your databases! It is only meant to be run on secured databases using ULS security. Any other applications can have disastrous results on your databases. I mention this because I recently was e-mail by someone who made this mistake. Below is the solution to fix such a mistake:
So if I understand the situation properly, you ran the chgPwd.mdb on your computer, in a db, or on its own in a db that wasn’t already secured? I would assume that by doing so you inadvertantly applied a password against your system mdw database. So in fact you secured the master system.mdw database, thus imposing a password against all databases running on your computer. The fix, you’d need to either reinstall Access, or copy over a system.mdw from another clean PC over to your.
Share and Enjoy
MS Access Samples | Comments Off
November 18th, 2010
This database provides a single form which permits the user to either import or export data to/from the database from a selected source (spreadsheet).
Download the sample database: MS Access Data Transfer Sample Database
Share and Enjoy
MS Access Samples | Comments Off
November 17th, 2010
This is a very simple database which provides the user a form which permits them to convert color values between OLE Color values, RGB values and HEX values.
Download the sample database: MS Database Color Converter Sample Database
Share and Enjoy
MS Access Samples | Comments Off
November 16th, 2010
This database demonstrates common programming concepts, such as: basing one combo box’s list on anothers value, changing a control’s background color, changing a form’s background color. This sample database will be updated as I have time to add to it.
Download the sample database: Common MS Access Issues Sample Database
Share and Enjoy
MS Access Samples | Comments Off
