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?
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.
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.
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)
Do Until rs.EOF
.Edit ***the error occurs here
![AdvanceNoticeDate] = Date
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.