add timestamp field to linked SQL table 1

[pandapark]

Hi

to resolve a write conflict error for multiple users accessing the same record I was getting in access 97 (tables are linked to SQL) i added a timestamp field to the SQL tables - problem is I now get a write conflict error in access whenever even just one person accesses any record?

any help much appreciated
gwilym

 

[Quehay]

How did you intend for a timestamp field to help with write conflict errors?

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[cmmrfrds]

Can you show the SQL Statement you are using? This is necessary to venture an opinion.

 

[pandapark]

Quehay

haven't a clue been honest - i've read hundreds of times add a timestamp with default of GetDate() and hey presto your write conflict in access goes away - except it doesn't

 

[cmmrfrds]

If an sql server field is defined as timestamp, the value is managed by sql server. The use is that products like ODBC and ADO need someway of making sure a record is unique so they can manage whether an update has been applied since your app last read the record. If an update was applied, i.e. something changed in the record since you retrieved it then ODBC or ADO will inform you that somebody else updated the record. If for some reason a table does not have a unique index then ODBC or ADO will look at all the fields to determine if something changed. The timestamp allows the products to determine uniqueness from the 1 field instead of looking at all the fields.

 

[Quehay]

That's a nice informative paragraph cmmrfrds! (*).

emmittrhodes:
If you're using linked tables, you're necessarily using ODBC since ADO doesn't permit linking per se. If your table doesn't already have a unique key Access won't allow for a form bound to a table or view to edit or insert data at all.

In your situation is it likely, possible, or not an issue that two or more users would try to update the same record concurrently?

There are concurrency settings for the tables in SQL Server that determine whether users read dirty records or not, whether an update will be attempted until the record is unlocked, or whether a lock will be placed on a record until update is complete, etc. It'd take more than a paragraph to cover all that. Do you have someone in DBA capacity working with the backend or are you on your own?



Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[pandapark]

thanks

yeah it is likely that 2 or more users may try to update the same record
I'd be quite happy to use the SQL locking whereby a lock is placed until an update is completed, but I am alone unfortuantely so any help would be much appreciated

 

[ordendelfai]

Just to throw my two cents in, if you have a boolean field (true/false), it can never be null in a linked SQL server table. If you create a record without setting a default value of false or true for the field, you will run into record lock problems (don't know why, but this along with the timestamp solution has always fixed my record lock problems).

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.