Solved

make a dynaset querydef updateable

Posted on 1998-09-22
3
300 Views
Last Modified: 2010-08-05
I need to update a field that is in a querydef.  I have 2 queries.
query 2 is based on query 1. The problem is an error occurs "recordset is read only"
I have looked up the dynaset updateable property and is says that it can be updated.
the first query can be edited but the second can't.  how can I get this second query to be updateable?.  Are compounded queries read only??? is it because it is based on "dates" and the update property can only update the field of its original table.?
 
 icdpSQL = "SELECT tblSlideDetail.ToSchedule, tblOrders.ProjectName,..... " & " between #" &       DATE1 & "# and #" & DATE2 & "#))ORDER BY tblSlideDetail.CompletionDate;"

iccycleSQL = "SELECT [Dates].ProjectName, count([Dates].ItemID) as countofitemid,             [Dates].Available, [Dates].ToCycle FROM [Dates] GROUP BY [Dates].ProjectName,             [Dates].ItemID, [Dates].Available, [Dates].ToCycle;"

    Set icdpQD = dbs.CreateQueryDef("Dates", icdpSQL)
        With icdpQD
            Set icdpRS = icdpQD.OpenRecordset(dbOpenDynaset)
        End With
    Set iccycleQD = dbs.CreateQueryDef("Cycles", iccycleSQL)
        With iccycleQD
            Set iccycleRS = iccycleQD.OpenRecordset(dbOpenDynaset)
        End With
       
        With iccycleRS
           iccycleRS.MoveFirst
            Do Until iccycleRS.EOF
                If iccycleRS!countofitemid > (iccycleRS!Available * 2) Then
                    itemover = iccycleRS!countofitemid - (iccycleRS!Available * 2)
                    totaldays = totaldays + itemover
                    increasedays = totaldays / numberofdays
                   
                End If
             
               If iccycleRS!countofitemid > iccycleRS!Available Then
             '      this will set the cycle field for the mould that needs cycling
                   .Edit
                   !ToCycle.Value = True
                   .Update
                   
                End If
                .MoveNext
            Loop          
0
Comment
Question by:SE081398
  • 2
3 Comments
 
LVL 3

Author Comment

by:SE081398
ID: 1963216
Edited text of question
0
 
LVL 7

Accepted Solution

by:
spiridonov earned 30 total points
ID: 1963217
In your second query you have GROUP BY clause,this makes your query not updatable.Query can't be updated if it contains aggregate functions.
0
 
LVL 3

Author Comment

by:SE081398
ID: 1963218
Thanks.  I've now had to create a 3rd recordset that is primarily the same as the first recordset icdpRS, except a few less fields and a where clause that includes a unique identifier of each record in the icdpRS.  in short, a modification was made and it works.
thanks.  the code would have been more efficient if I could have used the agregate query,
but oh well it works.

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now