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

    Unanswered: MoveFirst, DoEvents, MoveLast Syntax help

    This is probably the simplest fix, but I need to get the Autonumber and store it to a public variable that will be used to identify the session the user is in. This ID is used when the User logs off to close a session. The Red code below needs to be modified to fit with the rest of this code. I need the Recordsource to be the dbo.tTbl_LoginSessions. The LngLoginID is used later. I would be very grateful if someone can take a quick gander at this. I am still way to new to SQL. Thank you in advance.

    '********************CREATE SESSION - WHO AM I
    Function CreateSession()
    '/This function records the details regarding the login details of the person

    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strSQL As String
    Dim WhoAmI As Long

    Dim StrLoginName As String, StrComputerName As String

    'passing variables
    StrMSID = StrLoginName
    StrComputerName = FindComputerName

    'Declaring what table you are passing the variables to
    strSQL = "Insert into dbo.tTbl_LoginSessions(fldUserName, fldLoginEvent, fldComputerName) Values ('" & StrMSID & "','" & Now() & "','" & StrComputerName & "')"

    '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 = strSQL
    .CommandType = adCmdText
    .Execute
    End With
    '/Next get the autonumber and store it to a public variable that will be used to
    '/identify this session.
    '/This id is used when user logs off to close this session.
    Rs.MoveFirst
    DoEvents
    Rs.MoveLast
    LngLoginId = Rs(0)

    Debug.Print strSQL
    'close connections
    con.Close
    Set cmd = Nothing
    Set con = Nothing



    End Function

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to the Microsoft Access topic.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use a stored procedure that would store the data then return the last IDENTITY value used in the table tTbl_LoginSessions:
    Code:
    CREATE PROCEDURE StoreLogin
    (
          @MSID NVARCHAR(50)
        , @LoginEvent SMALLDATETIME
        , @ComputerName NVARCHAR(50)
    )
    AS
        BEGIN    
            INSERT INTO tTbl_LoginSessions(fldUserName, fldLoginEvent, fldComputerName) 
            VALUES (@MSID, @LoginEvent, @ComputerName);
            SELECT IDENT_CURRENT('tTbl_LoginSessions'); 
            RETURN IDENT_CURRENT('tTbl_LoginSessions'); 
        END
    GO
    You could also use the statement:
    Code:
    SELECT IDENT_CURRENT('tTbl_LoginSessions');
    from within Access.
    Last edited by Sinndho; 10-31-13 at 07:10.
    Have a nice day!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what I think you want is the last inserted id, to be run immediately after your insert. something like:-
    Autonumber value of last inserted row - MS Access / VBA - Stack Overflow
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    @Sinndho - a better method in SQL Server is to use the OUTPUT clause
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2013
    Posts
    23
    So like in this Code -> <http://technet.microsoft.com/en-us/library/ms187342.aspx>
    I would be concerned with Max would not work as if someone else inserts a record just after you've inserted a record but before your max function finishes executing then you would get their record.
    There has to be a simpler way and I literally am not smart enough to finish this module. Way too complex for me.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The use of a stored procedure will always return the correct value. From Access and using DAO (I seldom use ADODB) it would be something like (tested and working):
    Code:
    Function CreateSession()
    
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim StrMSID As String
        Dim StrComputerName As String
        Dim strSQL As String
        Dim LngLoginId As Long
        
        StrMSID = StrLoginName
        StrComputerName = FindComputerName
        strSQL = "StoreLogin @MSID='" & StrMSID & "', @LoginEvent='" & Format(Now, "yyyy-mm-dd hh:nn:ss") & _
                 "', @ComputerName= '" & StrComputerName & "';"
        Set qdf = CurrentDb.CreateQueryDef("")
        With qdf
            .Connect = cSQLconn
            .SQL = strSQL
            Set rst = .OpenRecordset
            LngLoginId = rst.Fields(0)
            rst.Close
            .Close
        End With
        Set rst = Nothing
        Set qdf = Nothing
    
    End Function
    Note: The connection string must be in the form:
    Code:
    Const cSQLconn As String = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
    Have a nice day!

  8. #8
    Join Date
    Oct 2013
    Posts
    23
    George, It look like after much research the Output Method would be the best. I have never used that method. What would that look like in my code? I am clueless, thanks.

Posting Permissions

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