Results 1 to 9 of 9
  1. #1
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12

    How To Update Logout Time in LoginLog Table when user is sudden removed their LAN cable from Laptop

    Dear All,
    I need your help on Access VBA 2007, How To system will automatically update Logout Time in LoginLog Table when user is sudden disconnected on Network Connection OR sudden removed LAN cable from their Laptop.
    Below is my Login_Form VBA Code, here i am using Sub Form_Close() event and it is working fine when user is properly logout on this application.



    If user sudden remove their network cable from laptop then this LoginLog_ID is always showing on Who’s Online Query because i have created Who’s Online Query based on LoginLog Table where LogOut Time is Null. Next time when he will again Login this Application, on Who’s Online Query will showing duplicate records on this user.

    I have automated if any new task was came on TaskDetail Table then automatically allocate user who’s online against New Tasks, that scenario when user will not Logout this Application properly then this user will showing always as Online and system will always assign new tasks to this user continually.

    In my Application i am not using Login sessions if required please suggest and also suggest how/where to use session in my application.

    Public GlobalEmpCode As Variant
    Public GlobalEmpName As Variant
    Public GlobalLngLoginId As Long

    Private Sub cmdLogin_Click()
    Dim con As Object
    Dim Rs As Object
    Set con = Application.CurrentProject.Connection
    Set Rs = CreateObject("ADODB.Recordset")

    Rs.Open "select * from SRT_tblEmployees where lngEmpID='" & txtLoginID.Value & "' and strEmpPassword='" & txtPassword.Value & "'", con, 1, 3

    If Rs.RecordCount > 0 Then
    GlobalEmpCode = Rs.Fields("lngEmpID").Value
    GlobalEmpName = Rs.Fields("strEmpName").Value

    Dim rs2 As Object
    Set rs2 = CurrentDb.OpenRecordset("SRT_tblLoginLog")
    rs2.AddNew
    rs2.Fields("Emp_ID") = GlobalEmpCode
    rs2.Fields("Emp_Name") = GlobalEmpName
    rs2.Fields("Login_Date") = Now()
    rs2.Update
    rs2.MoveFirst
    DoEvents
    rs2.MoveLast
    GlobalLngLoginId = rs2(0)
    rs2.Close
    Set rs2 = Nothing

    Else
    If Rs.State = 1 Then Rs.Close
    Set Rs = Nothing
    Exit Sub
    End If

    End Sub



    Private Sub Form_Close()
    Dim RsL As DAO.Recordset
    Set RsL = CurrentDb.OpenRecordset("SELECT * FROM SRT_tblLoginLog WHERE ID =" & GlobalLngLoginId)

    If Not RsL.EOF And Not RsL.BOF Then
    RsL.Edit
    RsL.Fields("Logout_Date").Value = Now()
    RsL.Update
    RsL.Close
    End If
    Set RsL = Nothing
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming your back end is where tblLoginLog resides and it is on the network, you are disconnected from the backend so there is no way to update the table.

    for the user best you can do is have some code in your login event which checks if the last 'state' was 'user logged in' and do whatever you need to do as a result - perhaps list all the assigned tasks since last logged in

    there may be a way to have some code on the server to run on a timed basis - every minute/hour/whatever to check connection status of each (apparently) logged in user and update the table if required, but regret I've no idea how you might do that - others here may have an idea. Only thing I can think of is send a 'are you there?' type message the user has to acknowledge.

    What the solution may be depends on what is considered a suitable final outcome - for example, assign a task, and if the user has not acknowledged with a certain time period, reassign to another user.

    Think you need to explain your business in a bit more detail - nature of assignments, time periods, how frequently disconnections happen etc

  3. #3
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12
    Dear Ajax, I am intermediate level VBA Developer, i don't this so there is no way to resolved my issue, suppose i have logged in with website www.hdfcbank.com and sudden i remove my LAN cable without pressing logout button on www.hdfcbank.com then what happened www.hdfcbank.com Application will automatically consider your last session as and system will auto update your last logout time in their table, if you again connect your LAN and refresh your browser then you will get a message your last session was terminated and need to re-login.

    Here I am expecting same logic from you with code. thanks.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Here I am expecting same logic from you with code. thanks
    I understand what you are trying to achieve but as said, I don't have any code.

    Access does not do server side processing which is what hdfcbank will be doing - it will be monitoring your link and will log you out if there is no activity after a period of time.

    In order to be able to see how (if it is in fact possible) you might achieve a similar effect, then provide the information requested

  5. #5
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Hrrm... So it would probably be best if you had another program running alongside the backend. Here is what I'm thinking, with just a couple minutes of thought put into it.

    Have a new backend, when the users sign into the exsisting system have them send a query that includes machinename or ID, or IP address.

    Have the new backend either ping for an ip address or check to see if the machinename is on the network... if that fails, then update the current backend to show the log out time. To get rid of the delay (from looping through each record) you could record "Last Check Time" and just use that as the logout time if they are no longer available.



    Pinging an ip address might work, machine name might work.. it just depends on your network setup. Do you have a way to determine if a computer is on the network now? If so, Automate that process in a loop.



    Now that I re-read your post. The MachineCheck process could be done before each task is assigned. Or even in it, before assigning confirm the machine is available.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So it would probably be best if you had another program running alongside the backend
    kind of what I was suggesting - problem is with using Access is the code is run on the user machine/user CPU, so would need to be run by a server admin person who is a) logged into the server machine permanently and b) has access installed on the server on the admin profile. To take your last paragraph this instance of Access would also need to run the task assignment as well.

    If back end was sql server this could perhaps be managed by sql server. Alternatively perhaps the server windows task scheduler could be utilised to run a routine to do the machine check and update the table

    However the OP is asking for the code - and I have no idea what to suggest - and as you and I have asked, all depends on setup and actual requirement - and only the OP knows that, anything else is fruitless speculation

  7. #7
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12
    Dear Ajax/Redbull,

    I have rights only to do any things on my database on SQL Server but nothing to plan any scheduler or anything on Server Machine because some TCOC rules.

    Ajax as Suggested, I have a backup desktop and no buddy to use this machine and it is always connected to Network.

    If I run a Macros Access file on this machine by 24/7 and with scheduler script to check every 15 minutes available in who’s online table and check they are still connected to SQL Server table or not, if not then it will update their Logout_Time in LoginLog table against that user.

    Here VBA code is the challenging for me, can you help me to solve this process, if their I used MS Access as backed DB there is the way to check who’s using Access file but here i am using SQL Server as BE DB now how can i Wright VBA to check who’s using a SQL Table that is the challenging for me.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so far as I am aware there is no code I can point you to - I think you need to look at redbulls suggestion and try googling some of his suggestions - perhaps google something like 'access vba ping IP' or similar - or 'access vba determine who is connected'. And as he says, it will be dependant on how your network is set up.

    Regret I do not have the time to research an area I have no knowledge. Happy to help resolving coding issues once you have something more concrete.

  9. #9
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Quote Originally Posted by redbull View Post
    Do you have a way to determine if a computer is on the network now?

    Before any code can be suggested, Answer this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. code for logout
    By rameshjctr in forum Programming
    Replies: 4
    Last Post: 04-20-2016, 10:24 PM
  2. Replies: 2
    Last Post: 10-22-2014, 11:37 AM
  3. Replies: 4
    Last Post: 01-10-2014, 01:20 PM
  4. Replies: 1
    Last Post: 02-17-2013, 06:54 AM
  5. User interface to update a table
    By HectorH in forum Forms
    Replies: 1
    Last Post: 09-16-2011, 06:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums