Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table relations blocking updates (A2K)

    I am developing a specialized task manager in A2K. There is a table of personnel and a table of required tasks. The form to look at tasks has a combobox for personnel, a combobox for task names, and date criteria. These three are ANDed together in a SQL statement that becomes the recordset for a subform in datasheet view. In this way you can look at all the tasks, all tasks for one person, one task for all personnel, and so on. All of this worked fine; I created the recordset using DAO. (The purpose for displaying these tasks is to mark off the ones that have been completed.)

    Because the list of personnel can be long, I built the capability for making

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table relations blocking updates (A2K)

    Access forms bound to an ADO recordset are never updateable, so forget ADO for this purpose. I can't actually tell from your description what exactly you're doing. I would suggest you post the SQL because it isn't going to be possible to guess at the problem without seeing it.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table relations blocking updates (A2K)

    Here is the query that fails:

    <code>

    SELECT
    tblEventsRequired.strEventID,
    tblEventsRequired.strSSAN,
    tblPersonnelData.strName,
    tblEventsRequired.dtmDateDue,
    tblEventsRequired.dtmDateOverdue,
    tblEventsRequired.memComments,
    tblEventsRequired.ysnCompleted
    FROM (tblEventsRequired
    INNER JOIN tblPersonnelData
    ON tblEventsRequired.strSSAN
    = tblPersonnelData.strSSAN)
    INNER JOIN tblGroupMembers
    ON tblEventsRequired.strSSAN
    = tblGroupMembers.strSSAN
    WHERE tblGroupMembers.lngGroupID=75
    ORDER BY tblPersonnelData.strName;

    </code>

    The problem is the second JOIN.

    tblPersonnel is one-to-many with tblEventsRequired. tblPersonnel is also one-to-many with tblGroupMembers. So Access thinks I'm trying to update a many-to-many when in fact all I want to do on the form is change tblEventsRequired.ysnCompleted so that a batch process routine can come in and deal with the ones that have been checked off.

    The purpose of the groups is to constrain the personnel list and thereby constrain the Events in veiw for edit.

    BTW, I have also changed the FROM to

    FROM (tblPersonnelData

    and made the appropriate changes in the JOIN clauses but the problem remains.

    Thanks,
    Donald

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table relations blocking updates (A2K)

    In Access 2000 a query can be read-only if you don't include the DISTINCT keyword and also if you don't include the key fields from each of the tables, even if you aren't writing to those fields or even to those tables. Try making those changes and see if that makes a difference.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table relations blocking updates (A2K)

    Hi Charlotte,

    Well, your suggestions didn't solve the problem but I was also working this issue over on AccessD and finally struck paydirt: replace one of the JOINs with a subquery. Here are the two statements:

    <Code Fails>

    SELECT
    tblEventsRequired.strEventID,
    tblEventsRequired.strSSAN,
    tblPersonnelData.strName,
    tblEventsRequired.dtmDateDue,
    tblEventsRequired.dtmDateOverdue,
    tblEventsRequired.ysnCompleted

    FROM (tblPersonnelData

    INNER JOIN tblEventsRequired
    ON tblPersonnelData.strSSAN
    = tblEventsRequired.strSSAN)

    INNER JOIN tblGroupMembers
    ON tblPersonnelData.strSSAN
    = tblGroupMembers.strSSAN

    WHERE tblGroupMembers.lngGroupID=130

    ORDER BY tblPersonnelData.strName;

    </Code Fails>


    <Code Works>

    SELECT
    tblEventsRequired.strEventID,
    tblEventsRequired.strSSAN,
    tblPersonnelData.strName,
    tblEventsRequired.dtmDateDue,
    tblEventsRequired.dtmDateOverdue,
    tblEventsRequired.ysnCompleted

    FROM (tblPersonnelData

    INNER JOIN tblEventsRequired
    ON tblPersonnelData.strSSAN
    = tblEventsRequired.strSSAN)

    WHERE tblEventsRequired.strSSAN IN

    (SELECT tblGroupMembers.strSSAN
    FROM tblGroupMembers
    WHERE tblGroupMembers.lngGroupID=130)

    ORDER BY tblPersonnelData.strName;

    </Code Works>

    Thanks for your time and help.
    Donald

Posting Permissions

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