Jet AutoNumber fields and Key Violations

Jet AutoNumber fields and Key Violations

Post by Egdi » Wed, 13 Dec 2000 00:25:04



Has anyone encountered problems with AutoNumber fields in MS-Access
databases, in both stand-alone and/or multi-user environments? The problem
we've hit is that the sequencing of the AutoNumber field has become out of
sync. with the actual number of records written. Ie. A record whose
AutoNumber field value should have been 7001 has, for some reason, tried to
go in with the value 6095.  The AutoNumber field is the primary key for that
table and the above is causing a Key Violation.

The test system this problem occured on is as follows:

200mhz Cyrix 6x86
32mb RAM (EDO)
1.5 gb IDE HDD
Windows 98 SE

M.D.A.C. version 2.1.3711.11 (GA)
MS Jet 4.0 OLE DB Provider

Database created using MS-Access 2000.

Within the Delphi application, we are using the following ADO Express
settings for the table in question:

TADOConnection:
    CursorLocation -     clUseClient
    IsolationLevel -        ilCursorStability
    Mode -                    cmUnkown

TADOTable:
    CursorLocation -     clUseClient
    CursorType -          ctKeySet
    LockType -             ltOptimistic
    MarshalOptions -     moMarshalAll
    MaxRecords -         1
    TableDirect -           False

What we need to know is whether this is a common problem, much like "Index
Out of Date" with Paradox?  Why does it happen? How can we prevent it
happening? Will it keep re-occuring, particularly in a multi-user
environment (about 10 users max)? Is it advisable to use Access/Jet
databases in a multi-user setup, even though Microsoft claim it to be safe
for at least 50 concurrent users?  Should we consider moving over to
M.S.D.E., although Microsoft state it should only be used by upto 5
concurrent users?

SQL Server 7 is not an option as client budgets won't cover the cost of
licenses

All help and comments gratefully received,
Ben Fidge

 
 
 

Jet AutoNumber fields and Key Violations

Post by M.H. Avegaar » Wed, 13 Dec 2000 00:34:18


The problem is probably the fact that you are using client-side cursors.
Client-side cursors don't get feedback from records added by other sessions
(=users or applications), so every user/application is able to insert the
same autonumber value.



Quote:> Has anyone encountered problems with AutoNumber fields in MS-Access
> databases, in both stand-alone and/or multi-user environments? The problem
> we've hit is that the sequencing of the AutoNumber field has become out of
> sync. with the actual number of records written. Ie. A record whose
> AutoNumber field value should have been 7001 has, for some reason, tried
to
> go in with the value 6095.  The AutoNumber field is the primary key for
that
> table and the above is causing a Key Violation.

> The test system this problem occured on is as follows:

> 200mhz Cyrix 6x86
> 32mb RAM (EDO)
> 1.5 gb IDE HDD
> Windows 98 SE

> M.D.A.C. version 2.1.3711.11 (GA)
> MS Jet 4.0 OLE DB Provider

> Database created using MS-Access 2000.

> Within the Delphi application, we are using the following ADO Express
> settings for the table in question:

> TADOConnection:
>     CursorLocation -     clUseClient
>     IsolationLevel -        ilCursorStability
>     Mode -                    cmUnkown

> TADOTable:
>     CursorLocation -     clUseClient
>     CursorType -          ctKeySet
>     LockType -             ltOptimistic
>     MarshalOptions -     moMarshalAll
>     MaxRecords -         1
>     TableDirect -           False

> What we need to know is whether this is a common problem, much like "Index
> Out of Date" with Paradox?  Why does it happen? How can we prevent it
> happening? Will it keep re-occuring, particularly in a multi-user
> environment (about 10 users max)? Is it advisable to use Access/Jet
> databases in a multi-user setup, even though Microsoft claim it to be safe
> for at least 50 concurrent users?  Should we consider moving over to
> M.S.D.E., although Microsoft state it should only be used by upto 5
> concurrent users?

> SQL Server 7 is not an option as client budgets won't cover the cost of
> licenses

> All help and comments gratefully received,
> Ben Fidge