Editing SQL Server data using an Access Form

[RL]

Hi,

One of our established tools has an Access front end to SQL Server data. For
some time, users have sucessfully read data from that tool and added records
to the SQL tables using append queries.

I am trying to add another feature which enables them to edit the data
contained within one of the SQL tables, via an Access form.

When the form is opened in edit mode, I cannot type into any of the fields,
even though they are not locked.

When I originally tried to run a set value macro to change one of the fields
in that form, an error message appeared saying that 'This Record Set is not
updateable'.

Have you any idea what I am doing wrong?

Thanks in advance,
RL

 

[Roger Carlson]

There are a lot of reasons why a recordset would be non-updateable. Is the
form based on a single table or a query joining multiple tables? If the
table are linked, did you define a primary key for them in Access? I the
recordset a UNION query or a "Totals" (aggregate) query? Each of these can
cause a recordset to be non-updateable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John W. Vinson]

Hi,

One of our established tools has an Access front end to SQL Server data. For
some time, users have sucessfully read data from that tool and added records
to the SQL tables using append queries.

I am trying to add another feature which enables them to edit the data
contained within one of the SQL tables, via an Access form.

When the form is opened in edit mode, I cannot type into any of the fields,
even though they are not locked.

When I originally tried to run a set value macro to change one of the fields
in that form, an error message appeared saying that 'This Record Set is not
updateable'.

Have you any idea what I am doing wrong?

Thanks in advance,
RL

What is the Recordsource property of the form? If it's a query is the query
updateable?

One common reason that blocks updating of SQL tables is that the SQL table
must have a primary key, and Access must know what that field (or fields) is.

 

[RL]

Thank you very much. It was a linked table, for which I hadn't defined a
primary key. I have added an int identity field and set that as the primary
key. It all works now.

Thank you very much for your help.

RL

 

[RL]

Thank you very much. It was a linked table, for which I hadn't defined a
primary key. I have added an int identity field and set that as the primary
key. It all works now.

Thank you very much for your help.

RL