Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2013
    Posts
    23

    Unanswered: Run-time error '3705'

    Utilizing the following code, I am now running into a Run-time error '3705': Operation is not allowed when the object is open. I tried debugging line by line, but to no avail I have been unsuccessful. Any help, would be greatly appreciated.
    Code:
    Function CloseSession()
    '/This closes the open session
    
    '/Define the OLE DB connection string.
    StrConnectionString = "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey;PWD=Mouse;DATABASE=Regulatory;"
        
    Dim Connection As New ADODB.Connection, RS As New ADODB.Recordset
    Connection.Open StrConnectionString
    
    RS.Open "SELECT * FROM [tTbl_LoginSessions] WHERE fldLoginKey =" & LngLoginId, _
        Connection, adOpenForwardOnly, adLockPessimistic
    Debug.Print CloseSession
    
    If Not RS.EOF And Not RS.BOF Then
        RS.Fields("fldLogoutEvent").Value = Now()
        RS.Update
        RS.Close
       End If
    
        
    'RS.Open "SELECT * FROM [tTbl_Users] WHERE PKUserID =" & LngUserID, _
        Connection , adOpenForwardOnly, adLockPessimistic
    
    '/Flag user as being logged out
    If Not RS.EOF And Not RS.BOF Then
        RS.Fields("fldLoggedIn").Value = "False" '/was 0
        RS.Fields("fldComputer").Value = ""
        RS.Update
        RS.Close
      End If
         
    End Function

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I propose a slightly different method... No need to open recordsets, simply execute UPDATE commands to perform the actions.

    Air-code (straight from my brain to paper, no testing performed)
    Code:
    '/Define the OLE DB connection string.
    StrConnectionString = "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey;PWD=Mouse;DATABASE=Regulatory;"
        
    Dim Connection As New ADODB.Connection
    Connection.Open StrConnectionString
    
    Dim sql As String
    
    sql = "UPDATE tTbl_LoginSessions
           SET    fldLogoutEvent = Now()
           WHERE  filLoginKey = " & LngLoginId
    
    ' Execute SQL Command
    Connection.Execute sql
    
    sql = "UPDATE tTbl_Users
           SET    fldLoggedIn = 0
                , fldComputer = ''
           WHERE  PKUsedID = " & LngUserID
    
    ' Execute SQL Command
    Connection.Execute sql
    
    Connection.Close
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so which line is the error being reported on?

    Im surprised at
    Code:
    Debug.Print CloseSession
    should that read
    Code:
    Debug.Print "in CloseSession"
    do you mean
    Code:
    RS.Fields("fldLoggedIn").Value = "False" '/was 0
    or should that read
    Code:
    RS.Fields("fldLoggedIn").Value = " & vbFalse '/was 0
    you could jsut do this using an update statement, seeign as you don't seem concerned as to whether the row is found or not
    Code:
    strSQL = "UPDATE Tbl_LoginSessions set fldLogoutEvent = " & now() & " WHERE fldLoginKey =" & LngLoginId
    docmd.runsql(strSQL) 'may have to use Gvee's connection.execute .runsql may be a vba only method
    are your tables really prefixed tTBL_ ?
    Last edited by healdem; 11-15-13 at 13:36.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2013
    Posts
    23
    Quote Originally Posted by gvee View Post
    I propose a slightly different method... No need to open recordsets, simply execute UPDATE commands to perform the actions.

    Air-code (straight from my brain to paper, no testing performed)
    Code:
    '/Define the OLE DB connection string.
    StrConnectionString = "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey;PWD=Mouse;DATABASE=Regulatory;"
        
    Dim Connection As New ADODB.Connection
    Connection.Open StrConnectionString
    
    Dim sql As String
    
    sql = "UPDATE tTbl_LoginSessions
           SET    fldLogoutEvent = Now()
           WHERE  filLoginKey = " & LngLoginId
    
    ' Execute SQL Command
    Connection.Execute sql
    
    sql = "UPDATE tTbl_Users
           SET    fldLoggedIn = 0
                , fldComputer = ''
           WHERE  PKUsedID = " & LngUserID
    
    ' Execute SQL Command
    Connection.Execute sql
    
    Connection.Close
    This is what I had for code and it wasn't working, which is very similar to yours(however, yours is much simpler and concise):
    Code:
    'Function CloseSession()
    'This closes the open session
    
    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strSQL1 As String
    Dim strSQL2 As String
    
    Dim StrLoginName As String, StrComputerName As String
    
    'passing variables
        StrComputerName = ""
        strLoggedIn = "False"
                          
        'Declaring what table you are passing the variables to
         strSQL1 = "Update tTbl_LoginSessions SET fldLogoutEvent = '" & Now() & "'" & _
         " WHERE fldLoginKey = " & LngLoginId
                      
        'Declaring what table you are passing the variables to
         strSQL2 = "Update tTbl_LoginUsers SET fldLoggedIn = '" & strLoggedIn & "', fldComputer = '" & StrComputerName & "'" & _
         " WHERE intCPIIUserID = " & LngUserID
        
    
        'connect to SQL Server
        Set con = New ADODB.Connection
        With con
            .ConnectionString = cSQLConn
            .Open
        End With
    
        'write back
        Set cmd = New ADODB.Command
        With cmd
            .ActiveConnection = con
            .CommandText = strSQL1
            .CommandText = strSQL2
            .CommandType = adCmdText
            .Execute
        End With
      Debug.Print strSQL
      
        'close connections
        con.Close
        Set cmd = Nothing
    Last edited by T-Rex19; 11-15-13 at 13:57.

  5. #5
    Join Date
    Oct 2013
    Posts
    23
    Quote Originally Posted by healdem View Post
    so which line is the error being reported on?

    Im surprised at
    Code:
    Debug.Print CloseSession
    should that read
    Code:
    Debug.Print "in CloseSession"
    Probably the latter versus my former

    do you mean
    Code:
    RS.Fields("fldLoggedIn").Value = "False" '/was 0
    or should that read
    Code:
    RS.Fields("fldLoggedIn").Value = " & vbFalse '/was 0
    I was playing with an actual value of 0 versus text False in order to pop an error

    you could just do this using an update statement, seeing as you don't seem concerned as to whether the row is found or not
    Code:
    strSQL = "UPDATE Tbl_LoginSessions set fldLogoutEvent = " & now() & " WHERE fldLoginKey =" & LngLoginId
    docmd.runsql(strSQL) 'may have to use Gvee's connection.execute .runsql may be a vba only method
    I did notice the non-concern of rows in the orginal code, but for some reason straight updates where stil tripping my results up

    are your tables really prefixed tTBL_ ?
    Yes, unfortunately, they do have a tTbl, because this SQL database is huge and I was tired of searching for my tables. Spelling duely noted, good eyes!

    THANK YOU

Posting Permissions

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