Insert or update record with vba and ado

[Jan T]

I am using Access 2010 with the new Accdb file format. My DB has a
front end and a back end on a shared folder. When my program opens, I
want some code to search the table tblLoggedIn for a username and then
update the record with the field LoggedIn = True and the field
TimeStamp with current Time and date like in the function Now().
If the code cannot find the passed Username, the code should add a
username, LoggedIn = True and a Time stamp.
Same procedure when logging of or closing the database, that is,
LoggedIn should then be updated to False and TimeStamp refreshed to
current date and time.
(I do have a function for returning UserName called ReturUserName from
Windows).

Does anybody have a code example how to do this with vba and ado?

Regards
Jan T

 

[Bob Quintal]

m:

I am using Access 2010 with the new Accdb file format. My DB has a
front end and a back end on a shared folder. When my program
opens, I want some code to search the table tblLoggedIn for a
username and then update the record with the field LoggedIn = True
and the field TimeStamp with current Time and date like in the
function Now(). If the code cannot find the passed Username, the
code should add a username, LoggedIn = True and a Time stamp.
Same procedure when logging of or closing the database, that is,
LoggedIn should then be updated to False and TimeStamp refreshed
to current date and time.
(I do have a function for returning UserName called ReturUserName
from Windows).

Does anybody have a code example how to do this with vba and ado?

Regards
Jan T

I do something similar, but with DAO.

table usage contains fields for
User (text),
Logon (date/time)
Logoff (date/time)

When a user logs on, I insert a new record (like you do if user not
found) by simply running an append query.

"insert into usage (user, logon) VALUES (""" _
& Me.User & """," & Now() &");"

This creates a new row each time a user logs on. with a null value
for logoff. If logoff is null, then the user is logged in, or has
crashed out of the program.

When the user closes the database, I update usage with qryLogoff,
This sets logoff = now() where user = me.txtUser and logoff = null.

Doing a separate row for each time the user has logge in is a very
handy statistical tool and debugging aid.

 

[Jan T]

m:








I do something similar, but with DAO.

table usage contains fields for
User (text),
Logon (date/time)
Logoff (date/time)

When a user logs on, I insert a new record (like you do if user not
found) by simply running an append query.

"insert into usage (user, logon) VALUES (""" _
   & Me.User & """," & Now() &");"

This creates a new row each time a user logs on. with a null value
for logoff. If logoff is null, then the user is logged in, or has
crashed out of the program.

When the user closes the database, I update usage with qryLogoff,
This sets logoff = now() where user = me.txtUser and logoff = null.

Doing a separate row for each time the user has logge in is a very
handy statistical tool and debugging aid.

--
Bob Q.
PA is y I've altered my address.- Hide quoted text -

- Show quoted text -

---------------------------------------------------------------

Thank you very much, Bob Q.

This sounds like a good idéa and is for sure a very good solution too.
Thank you for the code provided also.

Best regards
Jan T.