Unique id property value to identify an Access database instance

Question

I have an access 'application' (.adp file), when it opens i have it update an admin database with the username and time open. When it closes it updates the admin database with username time closed - these are sperate records in the events table so it looks like

username,dbaction,time
bob,open,13:00
gareth,open,13:05
bob,close,14:00

If the user where to open the db twice there would be 2 open and 2 close actions recorded but no way to establish which database session each of the 2 close events belonged.

What i want to store in this table is a unique identifier to link the open and close actions together with 'each session'. Preferably i would like to use a property ov the application object in vba if something exists. Does it even store the time the db was opened? I could generate my own id when databases are opened and store it in a variable until close, but id prefer to use something in built. Any ideas?

Answer 1

There is likely a method that runs in an access database when it is opened. In this you could generate a random identifier and store it in a global variable. When writing your log line you could include this identifier allow you to trace login and logout.

Update: You can use the code shown here to generate a GUID which is pretty much gauranteed to be unique so this should do what you want. If it doesnt you might need to clarify as I'm not understanding the question.

Answer 2

You could have a global 'Id_session' variable, initiated at startup (random generated uniqueIdentifier for example) that you will store in an 'id_Session' column of your 'event' table. When the database is opened, the record is inserted in the 'event' table, and the record identifier is stored as a global variable. When the database is closed, the existing record is identified (through the id_session value) and updated in the database.

In fact, I do not understand the interest of an inbuilt identifier instead of this solution.

Answer 3

I do this using a hidden unbound form which opens on startup. In that form I insert the record into the table. I then fetch the autonumber ID (or whatever SQL Server calls that field.) of that record and store in a text control. If you do any development and you hit an error and reset the running code you lose all global variables thus I prefer using forms to store these kinds of variables.

In the hidden forms On Close event I then update the same record with the date/time exited. I've been using this technique for well over a decade without any problems.

Answer 4

Why not HWnd?

Answer 5

All these answers seem to me to be too clever by half.

What I do is add an Autonumber field to the table I'm logging these in, and then capture the Autonumber value of the startup event record and store it somewhere in (usually in a hidden field on the app's startup form) for use at shutdown to write the shutdown event with the ID number of the startup event.

Answer 6

is HWND going to be unique for 2 access applications opened at the same time on possibly on the same pc or 2 different pcs?