Cannot change records in linked tables after adding fields

  • Thread starter Thread starter Roger Eriksen
  • Start date Start date
R

Roger Eriksen

Hi

I've upsized a database to SQL, ending up with some linked tables.
Then I had to add two fields to one of my tables. I created a new project
in Access with my SQL database as the data source. I added the needed
fields and then I refreshed the link in my Access application so I could see
the new fields.
I am able to add new records to the table, but I am not able to change
records. I get a message telling me that the data has been changed since I
opened and I get the option to either discard changes or copy them to
clipboard. I have tried to do the change operation directly in linked table
list so I do not have any form or code interfering. Doing the same from my
Access *project* that is connected directly to the table I can modify
records without problem.

Best regards
Roger Eriksen
 
I've done some checking.
The field causing problem is a "bit" field. I need a yes/no field and I
found that the upsizing wizard had been generating "bit" fields for this
use. I've been trough all possible settings on the field but I cannot see
any differences on the one I added and the ones that the wizard created.

Roger
 
The bit filed is a Yes/No for all intents and purposes (really a Boolean
data type). 1 is Yes and 0 is No. But back to your original problem: You
should be adding a field to all of your SQL server tables, let's call it
"upsize_ts", with a data type of "timestamp". Access will use this field to
determine if a record has changed. You probably have a memo field in the
table and Access can't determine whether a record has been updated because
it can't compare memo fields (same for OLE).
 
Roger said:
I've done some checking.
The field causing problem is a "bit" field. I need a yes/no field
and I found that the upsizing wizard had been generating "bit" fields
for this use. I've been trough all possible settings on the field but
I cannot see any differences on the one I added and the ones that the
wizard created.
Roger
Click to expand...

All bit fields must have a default value for the record to be editable.

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
 
Back
Top