Recordset Not Updatable

[Chris]

Hello All,

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?

Thanks in Advance.

Chris

 

[Sylvain Lafontaine]

There are many can reasons for a recordset to be read-only in an ADP form.

For example: make sure that your recordset have a primary key; set the
property Unique Table for the form (not always necessary but never a bad
thing to have it set. Also, if you cannot set it in the GUI, set it in the
On_Load event of the form); do not use UNION or DISTINCT clauses in your
query (not sure anymore about this DISTINCT thing, I should go make some
more tests); etc.

And of course, this could be a permission problem.

You should start with a very simple form and a simple query based on a table
with a primary key well defined.

S. L.

 

[Steve Jorgensen]

All that's true, but I've also run into enough cases where following all the
rules, it's still not editable, that I've just given up on trying to make
multi-table queries editable in ADPs. I'll just have to change it when
something makes it break later anyway.

So instead, my strategy for editable multi-table queries (and I don't know if
your case is a multi-table query) in ADPs is to use a combo box to do the
"join", and use calculated controls to pull related fields out of hidden
columns in the combo boxes.

It's a kludge, but at least I know it'll work, and it won't break when I make
some small change, then accidentally get delivered back to the client in a
broken state because I didn't know it was broken.

 

[Guest]

So instead, my strategy for editable multi-table queries (and I don't know if
your case is a multi-table query) in ADPs is to use a combo box to do the
"join", and use calculated controls to pull related fields out of hidden
columns in the combo boxes.

Good Advice. Even in Access mdb's I've gone to using single table
recordsource's. I've never heard you could "pull related fields out of hidden
columns in the combo boxes". Is there documentation you can point me to?
Also, has anybody seen a control that acts like a combo box but displays like
a text box (ne pull-down menu indicator)? Some of my users get confused when
they see the pull-down indicator but the control is locked.

 

[Steve Jorgensen]

Good Advice. Even in Access mdb's I've gone to using single table
recordsource's. I've never heard you could "pull related fields out of hidden
columns in the combo boxes". Is there documentation you can point me to?

It's blindingly simple. Hide a column by setting its width to zero in the
column widths property, and refer to a column via another calculated control

Also, has anybody seen a control that acts like a combo box but displays like
a text box (ne pull-down menu indicator)? Some of my users get confused when
they see the pull-down indicator but the control is locked.

Just hide the combo box altogether (make it invisible), and use text boxes to
refer to combo box columns you want to see.

 

[Guest]

It's blindingly simple. Hide a column by setting its width to zero in the
column widths property, and refer to a column via another calculated control


Just hide the combo box altogether (make it invisible), and use text boxes to
refer to combo box columns you want to see.

Yeah, I figured this out shortly after my last post when I found a thread
showing how to reference the fields of a combo box selection. But thanks for
the reply.

 

[Larry Linson]

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