[Impressive] Duplicate value in key field !?

Hi
I have a simple table in MSAccess, it contain a row field that is Primary 
Key and with AutoNumber type.
After inserting some records to the table (for example 74630 records) , ADO 
throw an error that my table is damaged(The error origin is that access can 
not retrieve records for the operation, because Duplicate record data are 
exists in the table)!
After looking on my table i see that Row field contain duplicate data! its 
impossible because Row is Primary Key!
Primary Key can not accept duplicate value, However Row is AutoNumber why it 
contain duplicate data!?, because MSAccess are filling it after each record 
inserting.
Im confusing :(

Thanks for any help
H.Ahmadi
0
Hossein
8/18/2008 6:51:57 AM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

16 Replies
1496 Views

Similar Articles

[PageSpeed] 36

I have no real idea, but since you posted this under ADO my first guess 
would be if you add records to the table using ADO and by any chance have 
inserted (or left) a value in the key field in records that are to be added?

(BTW: Your key field is defined so that it can hold values greater that 
32767 I suppose?)

regards Sven

"Hossein Ahmadi" <AhmadiSolness_B@yahoo.com> wrote in message 
news:4004@forums.codegear.com...
> Hi
> I have a simple table in MSAccess, it contain a row field that is Primary
> Key and with AutoNumber type.
> After inserting some records to the table (for example 74630 records) , 
> ADO
> throw an error that my table is damaged(The error origin is that access 
> can
> not retrieve records for the operation, because Duplicate record data are
> exists in the table)!
> After looking on my table i see that Row field contain duplicate data! its
> impossible because Row is Primary Key!
> Primary Key can not accept duplicate value, However Row is AutoNumber why 
> it
> contain duplicate data!?, because MSAccess are filling it after each 
> record
> inserting.
> Im confusing :(
>
> Thanks for any help
> H.Ahmadi
0
Sven
8/18/2008 9:44:05 AM
>I have no real idea, but since you posted this under ADO my first guess
> would be if you add records to the table using ADO and by any chance have
> inserted (or left) a value in the key field in records that are to be 
> added?
Never, i never give any value for my Key Field, because i leave it to 
MSAccess fill it(its AutoNumber i suspect that MSAccess set lastValue of 
it+1)
If you are sure that i can not suspect that access do the incremention for 
me , i can remove AutoNumber property of it and i fill it manually.
> (BTW: Your key field is defined so that it can hold values greater that
> 32767 I suppose?)
Yes, it can, it is Long Integer(4 bytes).
0
Hossein
8/18/2008 10:15:15 AM
"Hossein Ahmadi" <AhmadiSolness_B@yahoo.com> wrote in message 
news:4040@forums.codegear.com...
> >I have no real idea, but since you posted this under ADO my first guess
>> would be if you add records to the table using ADO and by any chance have
>> inserted (or left) a value in the key field in records that are to be
>> added?
> Never, i never give any value for my Key Field, because i leave it to
> MSAccess fill it(its AutoNumber i suspect that MSAccess set lastValue of
> it+1)
> If you are sure that i can not suspect that access do the incremention for
> me , i can remove AutoNumber property of it and i fill it manually.
>> (BTW: Your key field is defined so that it can hold values greater that
>> 32767 I suppose?)
> Yes, it can, it is Long Integer(4 bytes).

Sounds OK.

I have no problem using ADO adding records into an autonum keyed table, but 
I have never tried to add that many records in one operation.

My prime suspect now would be to investigate if adding a huge number of 
records in one ADO operation (if that is what you do) somehow can upset the 
autonumbering process?

I'm afraid that is all I can suggest.

regards Sven
0
Sven
8/18/2008 10:35:28 AM
> I have no problem using ADO adding records into an autonum keyed table, 
> but
> I have never tried to add that many records in one operation.
> My prime suspect now would be to investigate if adding a huge number of
> records in one ADO operation (if that is what you do) somehow can upset 
> the
> autonumbering process?
I don't add the records in one operation, the records are adding each after 
each, in different times.
But in 2 systems i have the problem, after some days that my records count 
are above 64000 records.
0
Hossein
8/18/2008 10:51:42 AM
"Hossein Ahmadi" <AhmadiSolness_B@yahoo.com> wrote in message 
news:4054@forums.codegear.com...
>> I have no problem using ADO adding records into an autonum keyed table,
>> but
>> I have never tried to add that many records in one operation.
>> My prime suspect now would be to investigate if adding a huge number of
>> records in one ADO operation (if that is what you do) somehow can upset
>> the
>> autonumbering process?
> I don't add the records in one operation, the records are adding each 
> after
> each, in different times.
> But in 2 systems i have the problem, after some days that my records count
> are above 64000 records.

If the problem arises when the number of records in the table exceeds 65535 
or 65536 I believe you have identified the precise condition for the 
problem. At least that is a condition that should be easy to test for.

(According to my Access 2007 help file there should not be any such problem 
with autonum)

regards Sven
0
Sven
8/18/2008 12:26:37 PM
"Hossein Ahmadi" <AhmadiSolness_B@yahoo.com> wrote in message 
news:4054@forums.codegear.com...
>> I have no problem using ADO adding records into an autonum keyed table,
>> but
>> I have never tried to add that many records in one operation.
>> My prime suspect now would be to investigate if adding a huge number of
>> records in one ADO operation (if that is what you do) somehow can upset
>> the
>> autonumbering process?
> I don't add the records in one operation, the records are adding each 
> after
> each, in different times.
> But in 2 systems i have the problem, after some days that my records count
> are above 64000 records.

If the problem arises when the number of records in the table exceeds 65535 
or 65536 I believe you have identified the precise condition for the 
problem. At least that is a condition that should be easy to test for.

(According to my Access 2007 help file there should not be any such problem 
with autonum)

regards Sven
0
Sven
8/18/2008 2:59:31 PM
Sounds like the database is damaged.
Compact the database that will fix damaged tables.

> {quote:title=Hossein Ahmadi wrote:}{quote}
> Hi
> I have a simple table in MSAccess, it contain a row field that is Primary 
> Key and with AutoNumber type.
> After inserting some records to the table (for example 74630 records) , ADO 
> throw an error that my table is damaged(The error origin is that access can 
> not retrieve records for the operation, because Duplicate record data are 
> exists in the table)!
> After looking on my table i see that Row field contain duplicate data! its 
> impossible because Row is Primary Key!
> Primary Key can not accept duplicate value, However Row is AutoNumber why it 
> contain duplicate data!?, because MSAccess are filling it after each record 
> inserting.
> Im confusing :(
> 
> Thanks for any help
> H.Ahmadi
0
Brian
8/19/2008 2:35:30 AM
> Sounds like the database is damaged.
> Compact the database that will fix damaged tables.
You right, i also know that the database is damaged, but why?
its a basic database concept that "Primary Key Field can not accept 
duplicate data."
Maybe MSAccess forget it?
My clients are using UPS, this mean that there is no cutting power, or 
system failure.
Why the database damaged?
Thanks
0
Hossein
8/19/2008 6:41:36 AM
"Hossein Ahmadi" <AhmadiSolness_B@yahoo.com> wrote in message 
news:4447@forums.codegear.com...
>> Sounds like the database is damaged.
>> Compact the database that will fix damaged tables.
> You right, i also know that the database is damaged, but why?
> its a basic database concept that "Primary Key Field can not accept
> duplicate data."
> Maybe MSAccess forget it?
> My clients are using UPS, this mean that there is no cutting power, or
> system failure.
> Why the database damaged?
> Thanks

I have assumed, but not really asked for verification: Can your problem be 
recreated on demand?
If not I would just have the database repaired and forget about it.

regards Sven
0
Sven
8/19/2008 7:52:20 AM
Hossein Ahmadi wrote:

> Why the database damaged?

Any kind of network hardware failure, Windows crashing on one  of the
workstations or possibly some type of hard disk problem on the server.
One of the bad things about file server databases is that they are
relatively easy to corrrupt since every workstation writes directly to
the database file.

-- 
Bill Todd (TeamB)
0
Bill
8/19/2008 2:17:39 PM
> {quote:title=Hossein Ahmadi wrote:}{quote}
> > Sounds like the database is damaged.
> > Compact the database that will fix damaged tables.
> You right, i also know that the database is damaged, but why?
> its a basic database concept that "Primary Key Field can not accept 
> duplicate data."
> Maybe MSAccess forget it?
> My clients are using UPS, this mean that there is no cutting power, or 
> system failure.
> Why the database damaged?

How many users access the database at the same time? Is the database stored on a network drive? 

Access is not designed for multi-user systems (the limit is really low - IIRC, around 10 users), and many users simultaneously can cause database corruption.

Also, Access is not designed to operate from a network drive. It's a desktop database, and should be used on the local PC.

If you need multiple user support, or reliable performance, why are you using Access? Firebird is free and works well, as does SQL Server Express (which is also free, but has a limit to the size of the database; I think it's 2GB, and since you're talking about 75K rows that should be sufficient).
0
Ken
8/19/2008 2:56:53 PM
> How many users access the database at the same time? Is the database 
> stored on a network drive?
> Access is not designed for multi-user systems (the limit is really low - 
> IIRC, around 10 users), and many users simultaneously can cause database 
> corruption.
> Also, Access is not designed to operate from a network drive. It's a 
> desktop database, and should be used on the local PC.
Im using MSAccess for a simple desktop database, it is not a network 
database.
The problem is here, i have a simple database with one connection(user), 
with a simple table. why its damaged!? and why i have double value for my 
Primary Key field (Double records that all fields are equal even my Primary 
Key field).
0
Hossein
8/19/2008 7:16:31 PM
Hossein Ahmadi wrote:

> why its damaged!?

There is no way to know for sure. An Access database is just a disk
file so anything that can damage a disk file can damage an Access
database. The most likely cause is a PC or Windows crash but a
transient or intermittent failure in the disk system or a memory
problem could also cause the file to become corrupt.

> why i have double value for my 
> Primary Key field (Double records that all fields are equal even my >
> Primary Key field).

Because the database file is damaged.

-- 
Bill Todd (TeamB)
0
Bill
8/19/2008 7:52:50 PM
If this is as you describe a single user database then I would susspect a bad hard drive or power supply as the cuase.
Did you compact it to repiar it.  Did that fix the damage?

> {quote:title=Hossein Ahmadi wrote:}{quote}
> > Sounds like the database is damaged.
> > Compact the database that will fix damaged tables.
> You right, i also know that the database is damaged, but why?
> its a basic database concept that "Primary Key Field can not accept 
> duplicate data."
> Maybe MSAccess forget it?
> My clients are using UPS, this mean that there is no cutting power, or 
> system failure.
> Why the database damaged?
> Thanks
0
Brian
8/20/2008 1:43:52 AM
> If this is as you describe a single user database then I would susspect a 
> bad hard drive or power supply as the cuase.
> Did you compact it to repiar it.  Did that fix the damage?

No,MSAccess can not repair it(even if i select compress and repair item in 
MSAccess 2007), the only way is that i create a new table(tempTable) , then 
i move all record from damaged table to tempTable, and i delete damaged 
table and rename tempTable to the damaged table.
In the above activity, duplicate records will remove and my Primary Key 
have not any duplicate data.
0
Hossein
8/20/2008 7:32:31 AM
> There is no way to know for sure. An Access database is just a disk
> file so anything that can damage a disk file can damage an Access
> database. The most likely cause is a PC or Windows crash but a
> transient or intermittent failure in the disk system or a memory
> problem could also cause the file to become corrupt.
All of my clients are using UPS, and my OS never turned off in unnormal way.
Also its not an accident, because i have 2 same problems in one week(on 2 
PC)! this mean that the problem can occur in future for many times.
I can solve problem manually, but i think maybe there is a problem in my ADO 
ConnectionString or MSAccess table settings.
Thanks.
0
Hossein
8/20/2008 7:36:52 AM
Reply:

Similar Artilces:

SQL Server
I have a table that has a RecordID field and TicketNumber field.  The RecordID field is the primary key and auto increments.  It will not duplicate as expected. However, I need to set my TicketNumber to "not allow duplicate values".  Is there a way to do this in SQL 2000.  Access allows you to do this without having to make it a primary key.  Does SQL Server allow this? Thanks for any help. You can use a UNIQUE Constraint for this column. Run the following query: ALTER TABLE yourTable ADD CONSTRAINT TicketNumber_UNIQ_1 UNIQUE (TicketNumber...

how to implement key/value collection that accepts duplicate keys
basically i want a collection object that i can dynamically increase size of which will allow duplicate keys. any ideas if this is possible. DataTable  You can also insert your own custom objects to it. Though in such case the key field wouldn't be a primary key but basically it would be possible.Thanks,Teemu KeiskiFinland, EU Allowing duplicate keys violates the concept of a key/value collection.  Have you considered the NameValueCollection class that while not allowing duplicate keys does allow multiple values under the same key?  When you reference the Item pr...

3 field hash, value lookup by key is 2nd field
Goal- look up value in a hash of 3 fields where middle is key field for lookup. The build hash from text file part below is working fine. Then I read in a transaction file with field $sicode which should lookup/chain/match to the key ($sig)in the hash and find a weight (3rd field in hash, $wgt), and a code, $type(1st field). I can't get the basic lookup on key $sig to work. I have created the hash from a text file thusly while (<INFILE>) { ($type,$sig, $wgt) = split ','; # splits $_ by default chomp $wgt; print "2 $type $sig $wgt \n";# sin...

Error "Multiple-step operation generated errors. Check each status value setting datetime field value" with TADOQuery and TDateTime (Delphi XE2)
Hello all, I've created an application which should do some automated tasks. When I run the application normally (as admin on a Window Server 2008 R2 x64 + MSSQL Server 2008 R2) everything works just fine. But whe I run the application a a 'sceduled taks' (with a different Windows User account), I get the following error when I modify a datetime field of a SQL query and I try to post the record Multiple-step operation generated errors. Check each status value setting datetime field value This is what I do (not exact query...): procedure Edit(aConn: T...

SortedList duplicate Key values
They are displayed in the correct order. The problem is when something has gone wrong and two gadgets have the same position (same value), I get this error: System.ArgumentException: An entry with the same key already exists. Maybe I can use the path as Key and the position as Value but then is it possible to make it sort by the Values instead?What can I do to make sure it sorts them even though two gadgets have the same position and not cause an error?  SortedList doesn't allow to sort by values. An workaround (but not using SorteList) is given in this thread: http://bytes.c...

how to filter duplicate field value
In Access and VFP,statement "select distinct fieldvalue from tablename",In PB,how to filter duplicate field value? TKS! Exactly the same way in SQL syntax eg SELECT DISTINCT staff_ref FROM Transactions The DISTINCT key word will filter the result set to remove duplicate entries. Beware using this to 'fix' badly constructed queries such as those giving the cartesian product of two or more tables. On Sat, 7 Jul 2001 10:29:44 +0800, in powersoft.public.powerbuilder.datawindow Kalmwu <kalmwu@163.com> wrote: >In Access and VFP,statement "se...

Convert Delphi 5 ADO App to Delphi 2007 ADO.NET
I have an appliction tha that is writen in Delphi 5 with ADOConnetion, ADOTable, and ADOQuery components. I need to upgrade to Delphi 2007 and use ADO.NET. I have the program building in with Delphi 2007. Is there anything I need to do to convert the ADO to ADO.NET? > I need to upgrade to Delphi 2007 and use ADO.NET. Why ? -- With best regards, Dmitry Arefiev AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL, Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX, ODBC, InMem - universal high-speed native data access engine Reasons for ...

Selecting value in one field then causes value in another field to be selected.
I have a web form which contains a date field, the date is populated using a DatePicker which is Javascript based. The form also has a drop down box containing 3 values. What I want to do is when a date has been selected that this will cause the dropdown list value to change to dated which is one of its 3 values. i.e. when the date field is not blank then the value will be selected in the dropdown. What I also want to happen is when the date has been selected and the dropdown value is dated then the date field and the dropdown field will be set to read only.Anyone any ideas how to this usin...

How can I add 2 fields value to 1 fields value in a GridView?
Hi all!I have one access database named=Medlemmar.mdb and Tablename=TidBokning and with some columns ex. "Bokad_For" and "Plats".Let say that "Bokad_For" contains value "Group2" and "Plats" contains value "Stockholm".Now I want to make this 2 value to 1 fileds like this: "Bokad_For" contains value "Group2Stockholm"How can I do that?I use asp.net 2.0 and C#.I tried with Eval("Bokad_For") and somthing, but Eval works only in Templates (I guess) I cant get Templates to work in my GridView.i'm newbee in this and think I need some help here :-) ?Thanks for all help!Micael Roxenhall You can sol...

no primary key value for foreign key value error in Master-Detail Dataset
Hi, We are using master detail relationship dataset but getting primary key violation error during posting the data. ------------------------------- *Project GBlWare.exe raised exception class EASANativeException with message* *[FireDAC][Phys][ODBC][Sybase][SQLAnywhere]no primary key value for foreign key* ------------------------------- Our current master detail scenario is that ,we are using parameter in details dataset that reference to master dataset primary key field value. But when I add a new record at that time master dataset key value is null. So when I press tab or down ...

Checking for Null values in ADO field
Hello, I am trying to check whether or not HERE is my some of my code: strSQL="SELECT ..." Dim cn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("ConnectionString")) cn.Open() Dim cmdHeader As OleDbCommand = New OleDbCommand(strSQL, cn) Dim rdHeader As OleDbDataReader rdHeader = cmdHeader.ExecuteReader So you can see that I am loading a OleDbDataReader from my table. This code works fine. Now I want to check to see if one of the fields is null. I have tried ALL of the following: If Val(0 & rdH...

Gridview Hyperlink Field needs to include value from a previous gridview and a field value
Hi all Hope you can help, here is what I'm trying to do. I have a gridview on a page that contains a value (PracticeCode) I use this value as a parameter to filter an additional GridView (Gridview2). Now what I also want to do is take that value (PracticeCode) and use it in as part of the URL for a hyperlink field in GridView2 concatenatint it with the value of a Gridview field called Documentname so that: Hyperlink field URL Is "/store/" & PracticeCode & "/" and DocumentName Anyone know how to do this  regards chubsterit wasn't me, ...

Add failed. Duplicate key value supplied
I know this problem has been posted on before but I have done a search for it and nothing comes up.I am running DNN 3.1.1 and I am getting this error roughly every ten minutes:AssemblyVersion: -1Method: FileName: FileLineNumber: -1FileColumnNumber: -1PortalID: -1PortalName: UserID: -1UserName: ActiveTabID: -1ActiveTabName: AbsoluteURL: AbsoluteURLReferrer: ExceptionGUID: DefaultDataProvider: InnerException: Add failed. Duplicate key value supplied.Message: System.ArgumentException: Add failed. Duplicate key value supplied. at Microsoft.VisualBasic.Collection.Add(Object Item, String Key, Obje...

DetailsView: conditionally hide field if value == null, but can't update the value of other fields
Hi, I need help please. I have made some fields hidden if their value is null by setting visible to false on the DetailsView1_Itemcreated event. I put the field's to the DataKeyNames, so that I can check the value is null or not when detailsview is at the readOnly mode.But, when I tried to edit, it throws an error: must declare a scalar for @...  so, i add some code in the DetailsView1_ItemUpdating: e.newValues["color"] = null; then the error is gone. but, the values for other fields are not updated. I already check the e.newValues["other fields"] , they all are ...

Web resources about - [Impressive] Duplicate value in key field !? - embarcadero.delphi.ado

Impressive HTML5 demo
This is just great. Let the demo play through — it doesn’t take long. You can read more about it in a story Om Malik wrote last Friday .

Xperia M4 Aqua – Capture sharp, impressive photos with the easy-to-use camera phone - YouTube
Take standout selfies with a powerful Full HD front camera: http://www.youtube.com/watch?v=uESKunaw4Qw Discover Xperia M4 Aqua and make it yours: ...

Impressive details make new Bentley stand out
The makers of Bentley Mulsanne have paid attention to all the small details.

The First Official Trailer for ‘Jurassic World’ is Here, and It’s Impressive
The story is set 22 years after the disastrous events in the original film, but now it’s a fully functioning dinosaur theme park with, of course, ...

Simon Hickey boots Blues to impressive win over Crusaders
Benji Marshall sat on the bench for the entire match as the Blues downed the Crusaders 35-24 in the Super Rugby clash in Auckland on Friday. ...

Racegoers bring 'impressive' fashion to Derby Day (02:09)
While most punters glammed it up in black and white for Derby Day, some dodged the dress code and arrived in Oompa-Loompa outfits.

14 Of The Most Impressive Predictions Of All Time
Really impressive predictions are the ones that sound ridiculous at the time but come true in the end.

Tide could turn for James Muirhead after impressive performance
James Muirhead bowled a delivery that could draw comparisons with Shane Warne's ''Ball of the Century".

Malware authors’ hard-fought “professionalism” impressive, frightening: researcher
Malware authors have become so good at seeding exploits en masse that their monitoring, customer service, marketing and Australian localisation ...

Godolphin filly Ghisoni destined for bigger things after scoring impressive debut win - DailyTelegraph ...
THE impeccably-bred Ghisoni proved her debut was worth the wait when she forged away with the TAB Rewards Plate (1200m) at Warwick Farm on Wednesday. ...

Resources last updated: 1/11/2016 7:42:17 AM