Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Question Unanswered: Why is this query not editable?

    Access says the query is read only and fails at the ".Edit" function. Why?

    Code:
    Private Sub Command104_Click()
        Dim rs As DAO.Recordset
        Dim db As DAO.Database
        
        Set db = CurrentDb()
        
        'This would work great if only the RS was updateable
        qry = "SELECT [PPI DB NEW].itemid, [BT-Items].MinimumBid, [BT-Items].BuyItNowPrice, [BT-Items].FixedPrice " _
            & "FROM [PPI DB NEW] INNER JOIN ([BT-Items] INNER JOIN [BT-Inventory] ON [BT-Items].InventoryID = [BT-Inventory].InventoryID) ON [PPI DB NEW].modelnumber = [BT-Inventory].PartNum; "
        
        Set rs = db.OpenRecordset(qry, dbOpenDynaset, dbSeeChanges, dbOptimistic)
        With rs
            Do While Not rs.EOF
                .Edit
                !FixedPrice = PPIMarkUp(0, !itemid, False) - 0.1
                !MinimumBid = PPIMarkUp(0, !itemid, False) - 0.1
                !BuyItNowPrice = PPIMarkUp(0, !itemid, False) - 0.1
                .Update
                .MoveNext
            Loop
        End With
        
    End Sub
    TIA for any help. This is my first time using this forum and hope you guys can help me out. I have NO training on access but have figured out a lot myself over the years and have managed to create an entire backend management system that uses Access for everything.

    The tables starting with "BT-" are linked tables from an SQL Express server. The other tables are local Access tables. I have an update query that performs the above function just fine but it takes about 2 hours to run so I wanted to try this VBA coded method instead as I expect it will be faster, if it would work. I have lots of problems with queries being read only sometimes and I am never sure why??? Perhaps someone can enlighten me? TIA!

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    a query is not updateable due to any join that is able to result in multiple records on one side of the query joining to one record on the other side of the query.

    make sure you have a key in both tables. make sure you have a relationship established between the two tables.

    but some queries simply are not updateable.

  3. #3
    Join Date
    Nov 2009
    Posts
    4
    Quote Originally Posted by NTC View Post
    a query is not updateable due to any join that is able to result in multiple records on one side of the query joining to one record on the other side of the query.

    make sure you have a key in both tables. make sure you have a relationship established between the two tables.

    but some queries simply are not updateable.
    Thanks for the info. There are multiple records on one side joined to one on the other. So there is no way to update a query like this? There is no key in one table and I never set up relationships... Not even sure what they are for? Kinda a newb here. Thanks for the info!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. If you're dealing with a one-to-many type query, if you design a query (not using code), you can do it by changing the Query "Recordset Type" property from a "Dynaset" to "Dynaset - Inconsistent Updates". You would do this in the actual design of the query (using query designer) and then view the query's properties to change this which would then make it updatable (you're overriding the non-updatable with the one-to-many join.)

    Otherwise in code, I'm not sure how you would do it. You'd probably have to re-write your code to open multiple recordsets (one recordset based on another, looping through the records of the 1st) so that you can then update the 2nd recordset. I don't think there's a parameter you can use in code to make the recordset like you can with an actual query where you can set it to a "Dynaset - Inconsistent Updates" type recordset (ie. I don't 'think' anything like this would work:
    Set rs = db.OpenRecordset(qry, dbOpenDynasetInConsistentUpdates, dbSeeChanges, dbOptimistic) but I never tried this in code - it's not really a good idea/technique.
    Last edited by pkstormy; 11-06-09 at 23:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2009
    Posts
    4
    Quote Originally Posted by pkstormy View Post
    Yes. If you're dealing with a one-to-many type query, if you design a query (not using code), you can do it by changing the Query "Recordset Type" property from a "Dynaset" to "Dynaset - Inconsistent Updates". You would do this in the actual design of the query (using query designer) and then view the query's properties to change this which would then make it updatable (you're overriding the non-updatable with the one-to-many join.)

    Otherwise in code, I'm not sure how you would do it. You'd probably have to re-write your code to open multiple recordsets (one recordset based on another, looping through the records of the 1st) so that you can then update the 2nd recordset. I don't think there's a parameter you can use in code to make the recordset like you can with an actual query where you can set it to a "Dynaset - Inconsistent Updates" type recordset (ie. I don't 'think' anything like this would work:
    Set rs = db.OpenRecordset(qry, dbOpenDynasetInConsistentUpdates, dbSeeChanges, dbOptimistic) but I never tried this in code - it's not really a good idea/technique.
    I see. It currently works with the following Access update query (not code):

    Code:
    UPDATE [PPI DB NEW] INNER JOIN (([BT-Items] INNER JOIN [BT-Listings] ON [BT-Items].ItemID = [BT-Listings].ItemID) INNER JOIN [BT-Inventory] ON [BT-Items].InventoryID = [BT-Inventory].InventoryID) ON [PPI DB NEW].modelnumber = [BT-Inventory].PartNum SET [BT-Listings].MinimumBid = Round((PPIMarkUp(0,[ppi db new].[itemid],True)-0.1)*0.97,2), [BT-Listings].BuyItNowPrice = PPIMarkUp(0,[ppi db new].[itemid],True)-0.1, [BT-Listings].FixedPrice = PPIMarkUp(1,[ppi db new].[itemid],True)-0.1
    WHERE ((([BT-Listings].StatusID)<=8 Or ([BT-Listings].StatusID)=11 Or ([BT-Listings].StatusID)=14 Or ([BT-Listings].StatusID)=16));
    I did not change any properties on the query. It just works. But if I change this to a select query and try to edit the fields in datasheet view, it doesnt work. So why does the Update query work and not the Select query?

    You say that a query update in code like I had would be bad technique? I ask because I am only trying this because the above update query takes 2+ hours to run on 30,000+ records. I assumed code might be faster. Any ideas on why it takes so long? The PPIMarkUp() just does some math functions but does run one select query. I guess that is probably that function thats slowing it down?

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    2 hours on 30,000+ records!!! Holy #$@! that's way to long. Something is going on. I'm guessing it's looping through the records (unnecessarily) multiple times.

    Try breaking down the query down into other query pieces, possibly making temp tables with some queries and then reusing those temp tables in other queries to get to your final query result.

    If your doing it in code, break it down into different recordsets opening, some possibly based on another but open recordsets only based on 1 or 2 tables and then use variables to perform calculations.

    Also check your indexes in the table. No indexing on any fields will make queries crawl.

    For me, it took 15 minutes top to run totals/updates on 5 million records (but there were some complex calculations otherwise it would've been quicker)

    You could probably make this:
    UPDATE [PPI DB NEW] INNER JOIN (([BT-Items] INNER JOIN [BT-Listings] ON [BT-Items].ItemID = [BT-Listings].ItemID) INNER JOIN [BT-Inventory] ON [BT-Items].InventoryID = [BT-Inventory].InventoryID) ON [PPI DB NEW].modelnumber = [BT-Inventory].PartNum SET [BT-Listings].MinimumBid = Round((PPIMarkUp(0,[ppi db new].[itemid],True)-0.1)*0.97,2), [BT-Listings].BuyItNowPrice = PPIMarkUp(0,[ppi db new].[itemid],True)-0.1, [BT-Listings].FixedPrice = PPIMarkUp(1,[ppi db new].[itemid],True)-0.1
    WHERE ((([BT-Listings].StatusID)<=8 Or ([BT-Listings].StatusID)=11 Or ([BT-Listings].StatusID)=14 Or ([BT-Listings].StatusID)=16));

    run much faster by breaking it down into multiple parts. You're trying to do to much in one sql statement. Without diving into the details, I'm guessing any functions you might be calling would be called multiple times. Use the KISS method. I'd re-write it to open 1 recordset and then another based on the 1st recordset, etc...until I set it up to meet the needs.

    Note Also: You can apply variable logic against values in the recordset after it's opened so you don't really need to imbed all the equations into the SQL statement.
    Last edited by pkstormy; 11-07-09 at 00:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2009
    Posts
    4
    Quote Originally Posted by pkstormy View Post
    2 hours on 30,000+ records!!! Holy #$@! that's way to long. Something is going on. I'm guessing it's looping through the records (unnecessarily) multiple times.

    Try breaking down the query down into other query pieces, possibly making temp tables with some queries and then reusing those temp tables in other queries to get to your final query result.

    If your doing it in code, break it down into different recordsets opening, some possibly based on another but open recordsets only based on 1 or 2 tables and then use variables to perform calculations.

    Also check your indexes in the table. No indexing on any fields will make queries crawl.

    For me, it took 15 minutes top to run totals/updates on 5 million records (but there were some complex calculations otherwise it would've been quicker)

    You could probably make this:
    UPDATE [PPI DB NEW] INNER JOIN (([BT-Items] INNER JOIN [BT-Listings] ON [BT-Items].ItemID = [BT-Listings].ItemID) INNER JOIN [BT-Inventory] ON [BT-Items].InventoryID = [BT-Inventory].InventoryID) ON [PPI DB NEW].modelnumber = [BT-Inventory].PartNum SET [BT-Listings].MinimumBid = Round((PPIMarkUp(0,[ppi db new].[itemid],True)-0.1)*0.97,2), [BT-Listings].BuyItNowPrice = PPIMarkUp(0,[ppi db new].[itemid],True)-0.1, [BT-Listings].FixedPrice = PPIMarkUp(1,[ppi db new].[itemid],True)-0.1
    WHERE ((([BT-Listings].StatusID)<=8 Or ([BT-Listings].StatusID)=11 Or ([BT-Listings].StatusID)=14 Or ([BT-Listings].StatusID)=16));

    run much faster by breaking it down into multiple parts. You're trying to do to much in one sql statement. Without diving into the details, I'm guessing any functions you might be calling would be called multiple times. Use the KISS method. I'd re-write it to open 1 recordset and then another based on the 1st recordset, etc...until I set it up to meet the needs.

    Note Also: You can apply variable logic against values in the recordset after it's opened so you don't really need to imbed all the equations into the SQL statement.
    I understand what you are saying and will give it a try with the multiple recordsets. I have no idea what the KISS method is. About the variable logic, could you explain that to me a little? I would like to load the results from PPIMarkUp() into a variable that I can use to update the 5000 records instead of calling PPIMarkUp() seperately for each item. But, without using code, I don't know how to utilize variables. Is there a way to integrate variable usage like this into the query designer?

    I am sorry for all these newb questions and thanks so much for your help. Being self tought, I don't really know the 'correct' way to do things but I can always jerry rig some code to get it done.

Posting Permissions

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