Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2015
    Posts
    1

    Unanswered: CurrentDb.OpenRecordset

    Hi

    Currently I have a recordset using :
    CurrentDb.OpenRecordset("SELECT * FROM ...... ORDER BY SEQ_ID DESC", dbOpenDynaset, dbSeeChanges)

    Then I have the following code :

    Do While Not TRS.EOF
    TRS.Edit

    TRS.Update
    TRS.MoveNext
    Loop

    I have transferred the functionality into a stored procedeure so my question is :

    Can I use : CurrentDb.OpenRecordset("exec spCreateRSFromSP ...........", dbOpenDynaset, dbSeeChanges) and STill be able to AddNew and/or Edit the current record???

    Thanks
    Darryl

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by darrylw99 View Post
    Hi

    ....Can I use : CurrentDb.OpenRecordset("exec spCreateRSFromSP ...........", dbOpenDynaset, dbSeeChanges) and STill be able to AddNew and/or Edit the current record???
    Darryl
    ..why not try it and see what happens
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by darrylw99 View Post
    ...
    Can I use : CurrentDb.OpenRecordset("exec spCreateRSFromSP ...........", dbOpenDynaset, dbSeeChanges) and STill be able to AddNew and/or Edit the current record???l
    No: You must use a QueryDef object and the resulting RecordSet will be read-only:
    Code:
    Sub PassThroughQuery()
    
        ' Connection string to a SQL Server.
        '
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=ISKENDER;DATABASE=Sales;Trusted_Connection=Yes;"
        
        ' Name of the stored procedure to be executed.
        '
        Const c_SQL As String = "Proc_Tbl_Items_Select;"
        
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        
        Set qdf = CurrentDb.CreateQueryDef("")
        With qdf
            .Connect = c_Connect
            .SQL = c_SQL
            Set rst = .OpenRecordset
            With rst
                Do Until .EOF
                    ' ...
                    .MoveNext
                Loop
                .Close
            End With
            .Close
        End With
            
    End Sub
    If you want to send changed values back to the database, you'll need to call a second stored procedure (hence use a second QueryDef object) for updating the table(s).
    Have a nice day!

Posting Permissions

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