ADP:  "This recordset is not updatable"-- problem with form SQL

Posted on 2006-06-03
Last Modified: 2011-04-14
Hi all,

I have an ADP project with a major subform as one of about 5 tabs.  On one subform in the tab named "Customer File", I have the SQL of the form regularly updating to reflect different records.  This form works great and I can update it without any problem.  But on another tab, I have a form called "For Sales" where information from an inventory table (tblinv) displays info.  However, anytime I go to any record, the query isn't updatable.  I've checked the form itself and it is set (like the other one) as an Updatable Snapshot showing up to 10,000 records at a time from the SQL Server datasource.

My understanding is that for a query to be updatable, these are the rules:

The select list can't include a DISTINCT or TOP clause.    
The select list can't include an aggregate function.    
The select list can't include a calculated value.    
The SELECT statement can't include a GROUP BY or HAVING clause.  

Here is my SQL that I feed to the form for it to update--in full compliance with the above:

sql = "SELECT tblinv.* FROM tblinv WHERE (tblinv.status='FOR SALE' Or tblinv.status='Available') AND tblinv.InvID=" & i

Am I missing something?  Why do I still get the error:  "This recordset is not updatable" in the lower left corner of Access when trying to add or edit a field?

Thank you in advance for your help--

Question by:normenclature
    LVL 142

    Accepted Solution

    does the table have a primary key?
    LVL 1

    Author Comment

    it should-- let me check...
    LVL 1

    Author Comment

    holy dumb data, Batman!  the InvID is NOT a primary key!!  That's probably the issue!  Thanks!  Let me make that happen
    LVL 1

    Author Comment

    That was it!!  Thanks-- you're an ANGEL!!  (no pun intended)

    I could only change the ID field to a PRIMARY key using Enterprise Manager.  As soon as I did, it worked perfectly.

    Thanks again!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now