Access front-end and SQL Server back-end table lock

[amn]

I just upsize an Access 2003 back-end to SQL Server 2008.

I have a table with only 1 record to use as "traffic light". When I
need to get a new Invoice number, I open table with "DenyWrite" option
to get an unique invoice number and capture the error 3262 for other
users trying to do the same.

After upsize to SQL Server, I get error 3254-ODBC, so how can I lock
the table/record in order to do that?

Best regards,

Angel

 

[Paul Shapiro]

I just upsize an Access 2003 back-end to SQL Server 2008.

I have a table with only 1 record to use as "traffic light". When I
need to get a new Invoice number, I open table with "DenyWrite" option
to get an unique invoice number and capture the error 3262 for other
users trying to do the same.

After upsize to SQL Server, I get error 3254-ODBC, so how can I lock
the table/record in order to do that?

I don't know how to do it with ODBC, but since you're using SQL Server you
can write a stored procedure that assigns the next number using a serialized
transaction to prevent any other updates. Look in Books On Line for Tablockx
for details. It could be something like:
Update MySchema.MyTable With (TablockX Serializable)
Set myField = myField + 1

or with an explicit transaction:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Begin Transaction;
Update MySchema.MyTable Set myField = myField + 1;
Select myField From MySchema.MyTable;
END TRANSACTION;

 

[amn]

I don't know how to do it with ODBC, but since you're using SQL Server you
can write a stored procedure that assigns the next number using a serialized
transaction to prevent any other updates. Look in Books On Line for Tablockx
for details. It could be something like:
Update MySchema.MyTable With (TablockX Serializable)
Set myField = myField + 1

or with an explicit transaction:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Begin Transaction;
Update MySchema.MyTable Set myField = myField + 1;
Select myField From MySchema.MyTable;
END TRANSACTION;

Paul,

Thanks for your prompt response.

I am a newbie regarding SQL Server matters, this is my first upsizing
process.

I was trying to find some VBA code applicable to MDB back-end as well
as SQL Server back-end, because it is not easy for me to enter
suddenly in SQL Server world.

I try to isolate as much as possible all things related to SQL Server
but I am starting to discover that I will have to pay a toll to use
it.

Anyway, I keep your code as an option.

Best regards,