SQL/ODBC record seems locked

[Zakraket]

I have an Access-application working on SQL-server with ODBC. Since last week I have a problem that in one of the linked tables records "lock" after inserting them, or to be more precise, when I want to edit/delete an existing record, I get:

"This record has been changed by another user blabla .... [COPY TO CLIPBOARD][DROP CHANGES]"

I am the only user currently (developing)

The table has an autoID, timestamps (missing these can cause some problems with odbc)

Directly on the server, the records are fine: I can edit them, remove them (using SSMS).

I can not recall making specific changes that could be related to this. The only change I can recall is adding a NTEXT-field, but without this field, the problem stays.

I have been playing with the Access-client recordlock settings but that doesn't make a difference. Current setting is "no locks" (since I want SQL to handle them).
Relinking the table doens't help, also when I create a linked table in another (new) access-db I have this problem.

So it must be something with the construction of the table?

FIELD		TYPE		ALLOWNULL
QUOTATION_ID	int		Unchecked
QUOTATIONNR	nvarchar(255)	Unchecked
QUOTATIONDATE	datetime	Unchecked
COMPANY_ID	int		Unchecked
USER_ID		int		Unchecked
PROJECT_ID	int		Unchecked
MATERIAL_RES_ID	int		Checked
CUSTOMER_ID	int		Checked
STATUS_ID	int		Checked
RENTAL_STARTDATEdatetime	Checked
RENTAL_ENDDATE	datetime	Checked
RENTAL_PERIOD	int		Checked
ORDERDATE	datetime	Checked
JB_ORDERNR	nvarchar(255)	Checked
COMPANY_ORDERNR	nvarchar(255)	Checked
REDUCTION_1	numeric(18, 3)	Checked
REDUCTION_2	numeric(18, 3)	Checked
REDUCTION_3	numeric(18, 3)	Checked
REDUCTION_4	numeric(18, 3)	Checked
REDUCTION_5	numeric(18, 3)	Checked
REMARKS		ntext		Checked
ENTEREDON	datetime	Unchecked
ENTEREDBY		nvarchar(255)	Checked
UPDATEDBY	nvarchar(255)	Checked
UPDATEDON	datetime	Checked
DELETED	bit	Checked

Any idea's?

edit: another "strange" thing: when I take a multiweeks old version of my client the same table works. But when I refresh the link to the table in this old client, the problem occurs again... So it MUST be something in the table construction??

 

[Zakraket]

Problem solved!

For some reason the DELETED bit field didn't have a defaultvalue anymore (should have been ((0)) ), like in all other tables. I have no idea how the defaultvalue could have been erased in the SQL-table but after setting it and refreshing things left and right it works again.

Has cost me a day in total to figure this out

 

[Rx_]

You mentioned that you have a autocounter (autoID) field.
In Access Linked Table the Design View will warn it can't be modified. But, it should show that your autoID is the Primary Key. Is it in fact your Primary Key?
Otherwise, ODBC has the option of indexing multiple fields to become a Primary Key.

You didn't mention how big the database is. If it is under a million, then the indexing would probably be OK. It if is up in the millions of records, it would be interesting to know how many indexes are set. There is a SP that will basically defrag the index if the table has heavy updates / deletes.

Do you use the DELETED field to indicate if the field is marked for deletion?
It is a common practice. Every so often for some tables, a script moves all fields marked for deletion to a Historical Deleted Table. It basically has the same table structure.

I think you found the problem but don't know how the default was changed. Thanks for posting your soltuion. You never know if it might help someone else out.
If so, go ahead and mark your request as Solved. Let us know if you have another question.

 

[Zakraket]

I did not provide all information, but the Quotation_ID is an autoID/primary key.

The database is small, at most a few hundred/thousand records will exist in this table.

The DELETED field is used to keep records in the database when users delete records. So, normally users cannot actually delete records, they mark them "deleted" and these records are filtered from the UI.
I do this for different reasons,

The defaultvalue for the field DELETED could be changed by me, but it would have been a mistake that I can't recall.

The problem is that a bit-field in the SQL-server translates to a yes/no-field in MSaccess (which is the goal). But in MSAccess a yes/nofield can't hold null, so the bit-field in the SQL-server needs to be 0 or 1 (-1) by default. An empty value creates problems...with a not very clear error in this case.

It's something I know (bitfield needs default value), but the error pointed me in the wrong direction.

Maybe I should not use the bitfield (to prevent yes/no fields in MSaccess) altogether and just use a int(1) field and a num-field in Access, http://allenbrowne.com/noyesno.html

 

[Rx_]

There are some records where I use the Integer in SQL Server.
Then, the Access front end puts a -1 into the field.
In my rule engine, T-SQL functions return -1 for Validated, 0 for Failed validated and then a positive number for a Recommended Status ID.

But, you are right, there are multiple methods that can be used.
It then becomes your responsibility to be consistant.

The design kind of depends on the goals and the multi-use.
Since I also use VBA Excel Object Model to read SQL Server data via linked tables and then programatically create all reports in Excel, the data type choices stay interesting.