I've created an Access Data Project
to do some data entry using a SQL
Server database, but I keep getting the
message that the recordset is not
updatable. Is this a permissions problem?
On a recent project, I was correcting and enhancing the ADP front-end to SQL
Server data. It seemed unusual that the original author had done all the
updates by executing individual SQL statements for each control/field that
was changed. It also seemed that some areas could be made much, much simpler
(and others faster) by binding the Forms, but bound Forms were read only. A
little investigation showed that not one single table in the SQL Server
database had been defined with a Primary Key. The simple expedient of the
DBA defining Primary Keys on a few of the most frequently used tables
allowed binding the forms, saving a lot of work, and improved performance.
But, as Sylvain says, there are many reasons why a recordset is not
updateable, both for Jet or server databases. One that I have frequently
seen in looking over clients' databases is that the key of the table to be
updated is not included in the Query... even though the same value is in the
key of some table in the Query, the key to the table in which values are
being updated must be there.
Earlier versions of Access had good Help on updateable Queries and why they
might not be updateable. I haven't looked for that information in Access
2002 or 2003 -- I'd guess it is still there, but may not be as easy to find.
Larry Linson
Microsoft Access MVP