December 16, 2009 at 12:04 pm
I inherited an existing Access application with a SQL Server backend. The previous programmer did not use any IDENTITY fields in his table. So all primary keys are ints, and the next number is generated by looking up the next value from a table. After every insert, a trigger is called that increments the next number in this table.
In the MSAccess application, there ia a "datasheet" form that has a Before_Insert event (on each record) that gets the next number, assigns it to the textbox that represents the primary key field, and then the form quickly saves the record when the user jumps to another row in the datasheet.
All this is fine...until two people read from the table at the same time. Then we get the Violation of PRIMARY KEY error when Access tries to insert. This does not happen often, there are 40 users, and I guess we've been lucky. But my client wants to know if there is a solution. I cannot change the field to IDENTITY because that would imply a major upgrade on the entire application. Is there another alternative (simpler) solution? Perhaps finding a way to disallow two people from reading from the table at the same time to fetch the next number. Or anything else?
Thanks in advance
Ray
December 16, 2009 at 12:39 pm
ray 78019 (12/16/2009)
I inherited an existing Access application with a SQL Server backend. The previous programmer did not use any IDENTITY fields in his table. So all primary keys are ints, and the next number is generated by looking up the next value from a table. After every insert, a trigger is called that increments the next number in this table.In the MSAccess application, there ia a "datasheet" form that has a Before_Insert event (on each record) that gets the next number, assigns it to the textbox that represents the primary key field, and then the form quickly saves the record when the user jumps to another row in the datasheet.
All this is fine...until two people read from the table at the same time. Then we get the Violation of PRIMARY KEY error when Access tries to insert. This does not happen often, there are 40 users, and I guess we've been lucky. But my client wants to know if there is a solution. I cannot change the field to IDENTITY because that would imply a major upgrade on the entire application. Is there another alternative (simpler) solution? Perhaps finding a way to disallow two people from reading from the table at the same time to fetch the next number. Or anything else?
Thanks in advance
Ray
Instead of the table where the insert is completed updating the "number table" when an insert is completed, the process that provides the user with the number should update itself as each number is issued.
December 17, 2009 at 8:25 am
I understand what you are saying but can you give me an example of how this can be done. How can I ensure that when a number is issued, that the table is "locked" while it updates itself. What happens if two people read the number at the same time?
December 17, 2009 at 9:22 am
Are we talking a SQL table or an Access table that holds this sequence number? Also, is the number currently in the table the next value or the last value? Is there only one row in this table or are the multiple rows defined by a type or table column? I guess it would help to have the table definition.
December 17, 2009 at 9:47 pm
It is a SQL table and it holds the NEXT value. There are multiple rows in this table, one row for each table that needs to be incremented. This table (called tblTableIDs) has a TableID, and intNextValue. In my particular case it's the PurchaseOrderItems table and it is represented in this table as TableID = 9.
In the Access front-end there is a Purchase Order form that contains a subform. This subform is represented as a datasheet listing the Purchase Order Items. The user enters the purchase order items in this subform line by line. In the detail section of this subform, there is a textbox called POItemID which is initially empty. As the user jumps to a new line, Access runs its BeforeUpdate event. It is in this event where the code performs a query "SELECT intNextValue FROM tblTableIDs WHERE TableID=9", and then uses this value to store in the empty textbox POItemID, then Access performs the insert.
On the SQL backend, the table PurchaseOrderItems has an INSERT trigger. When there is an insert, the intNextvalue in the table tblTableIDs gets incremented for TableID = 9, getting it ready for the next record.
Now the problem is that sometimes the user gets the error "Violation of PRIMARY KEY constraint" on this table (PurchaseOrderItems). I'm asking myself if two people insert at the same time, there is a possibility that the same intNextValue is read by two users at the same time. What can I do to improve this logic, to prevent this error from happening?
Thanks !
December 17, 2009 at 10:10 pm
I'm not an Access programmer. How does Access retrieve the value from the table? Does it simple select the value or does it use a store procedure to return the value.
I'll be honest, if it simply selects the value; you basically have a single user system in a multi-user environment. Been there, done that, have the t-shirt, don't want to do it again. Oh, it was a COBOL/ISAM based system with record locking on one and only one record in the entire system; the control record.
Edit: Actually, as I think about it, either way, you have a single-user system in a multi-user environment. Only difference is one may be easier to fix than the other.
December 17, 2009 at 10:28 pm
I would go to the client and propose two options - fix it (not a band-aid) or leave it as is. We had a funky setup similar to that with inline SQL controlling the ID value (no identity or PK) by locking the next 100 values. That was a horrific mess. I found 67000 duplicate ids for 1 ID value and a grand total of 350K dups. And the ID field was used as the join field.
It is well worth the time and money to fix it correctly now, than continue with any headaches they may be getting (at least they have a PK).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 17, 2009 at 10:47 pm
Here is some code that may help you figure out one possible solution. The idea behind this would eliminate the the triggers used to update the table where the next value is pulled from and move the updaing of that column where it belongs.
create table dbo.UpdateTest (
UpdTableId int,
NextValue int
);
insert into dbo.UpdateTest
select 1, 100 union all
select 2, 1001 union all
select 3, 98 union all
select 4, 10231 union all
select 5, 2945 union all
select 6, 1554 union all
select 7, 4093 union all
select 8, 873 union all
select 9, 908 union all
select 10, 19872;
go
create procedure dbo.GetNextValue (
@pTableId int,
@pNextValue int OUTPUT
)
as
declare @NextVal int,
@TableId int;
set @TableId = @pTableId;
update dbo.UpdateTest set
@NextVal = NextValue = NextValue + 1,
@NextVal = @NextVal - 1
where
UpdTableId = @TableId;
set @pNextValue = @NextVal;
go
select * from dbo.UpdateTest;
declare @NextValue int;
exec dbo.GetNextValue @pTableId = 9, @pNextValue = @NextValue OUTPUT
select @NextValue CurrentValue, NextValue, UpdTableId
from dbo.UpdateTest
where UpdTableId = 9;
go
drop procedure dbo.GetNextValue;
drop table dbo.UpdateTest;
Again, I am not an Access programmer, so I would not know how to implement this in the before action in the front end.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply