Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133

    Question Unanswered: Changes in Access

    I have an Access app that uses VBA to modify a complex query and then opens the query as a recordset and changes data in a field. It worked in the past but now it will no longer allow data changes. An error message states I must use an updateable query. When did this change and is there a simple workaround?

    Thanks for the help.
    SteveH

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi SteveH
    can't give a defintive reply without more details...

    First off what has changed since the query last worked, have you changed your veersion of MS Access, or the JET Engine, have you changed OS's, fille formats etc...

    I've seen this error message before when trying to run an Update OR Delete query which has used a join and got round the problem with a distinct clause in the select
    HTH

  3. #3
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133
    Here's what has changed:

    1. New servers where the app is kept were installed using Windows Server 2003 where Windows NT was before.

    2. I am now running both Acc 97 and Acc 2002 on my computer.

    The app has always had an Acc 97 front and back end, as well as an Acc 2000 front end. There are no problems when I run the live version from my conputer. I have problems when I run a test version from my computer. This version is kept on a different server running Windows Server 2003 (recently installed). As stated earlier, the only change to my computer is the addition of Acc 2002.
    SteveH

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I've heard bad things about Server 2k3 and Access 97 not playing together very well.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If the app is now running 2002 compared to 97 then I would suspect that problem lies in the changes in the underlying JET engine between 97 & 2002. I'd be surprised if the change in server per se caused problems as there is no code running on the server - its just a dumb file server.

    Have you tried the 'distinct' predicate. The message usually refers to a query which the JET enngine has deemed non updatable because it cannot resolve which table to update becasue of a join. There are probably other explanations out there, but thats what I read it meant. including the distinct predicate coerced JET to 'see' only the columns being updated as from one table, despite using a join to others.

  6. #6
    Join Date
    Oct 2004
    Posts
    12

    Query Does Not Include Specified Expression

    I have 4 fields in an Access query, FGRSSPRM, FTRANS, LMTP, LAMT.

    FGRSSPRM (contains series of amount e.g. 2000.00
    FTRANS (contains nos like 1, 2, 3, 4)

    LMTP:IIF([FTRANS]=1 OR [FTRANS]=4, 1, -1)
    LAMT:[FGRSPRM]*[LMTP]

    What I want to achieve is to have all values in FGRSSPRM that are 1 or 4 in FTRANS to display absolute values in LMTP and otherwise make the other values negative.

    But the query did not execute with the following complain:

    "You tried to execute a query that does not include the specified expression [FGRSSPRM]*[LMTP] as part of an aggregate function"

    I will appreciate your kind assistance

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Can we actually see the offending SQL?

  8. #8
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133
    Here is the SQL. The query name is qryNoticeAdvance:

    SELECT DISTINCT tblAnimalMaster.[MAST ID], tblAnimalMaster.YEAR, tblAnimalMaster.TAG AS TAGN, tblAnimalMaster.[LAST NAME], tblAnimalMaster.[FIRST NAME], tblAnimalMaster.ADDR, tblAnimalMaster.CITY, tblAnimalMaster.STATE, tblAnimalMaster.ZIP, tblAnimalMaster.ATYPE, tblAnimalMaster.VACDATE, tblAnimalMaster.VACEXPD, tblAnimalMaster.VACTYP, DateDiff("m",Date(),[VACEXPD]) AS [Date Diff], IIf([Date Diff]=13,DateAdd("m",-11,[VACEXPD]),IIf([Date Diff]=25,DateAdd("m",-23,[VACEXPD]))) AS [Due Date], tblAnimalMaster.ANAME, tblRegistration.AMOUNT, tblRegistration.AdvanceNoticeDate, tblRegistration.REGYEARNO
    FROM tblAnimalMaster INNER JOIN tblRegistration ON tblAnimalMaster.[MAST ID] = tblRegistration.[REG ID]
    WHERE (((tblAnimalMaster.ATYPE)=1 Or (tblAnimalMaster.ATYPE)=3) AND ((tblAnimalMaster.VACTYP)=3) AND ((DateDiff("m",Date(),[VACEXPD]))=13 Or (DateDiff("m",Date(),[VACEXPD]))=25) AND ((tblRegistration.AMOUNT)=0))
    ORDER BY tblAnimalMaster.ZIP;

    Here is the code that gets the error:
    Set rs = db.OpenRecordset("qryNoticeAdvance", dbOpenDynaset)
    With rs
    Do Until rs.EOF
    .Edit ***the error occurs here
    ![AdvanceNoticeDate] = Date
    .Update
    .MoveNext
    Loop
    .Close
    End With
    Set db = Nothing

    The specific error message is:

    Runtime error "3027"
    Cannot update. Database object is read-only.

    One of my colleagues copied the database to his desktop (Windows XP), ran the program and had the same problem.

    Thank you again for the help.
    SteveH

  9. #9
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133
    It turns out that removing "DISTINCT" from the SELECT statement solves the problem.
    SteveH

Posting Permissions

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