Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Nested Transactions? 1
- Thread starter JJman
- Start date
- Thread starter
- #4
Thank you both for your input.
Yes, fortunately I have multiple backups, but fortunately this portion of the database is only in the testing phase and has not been rolled out yet. I actually did do a compact and repair, but it did not resolve the issue. I didn't mention this in my first message, but this has apparently caused an issue with the table's PK, which is an autonumber. This autonumber serves as a "batch number" for items received [rec_batch_id]. Multiple users are accessing it via a form. After receiving something, I have a msgbox pop up informing the user what the batch number is for the item(s) just received. It appears that the "impossible" is possible after all, because Access is duplicating its own autonumbers in the receiving table! Just being an autonumber of course means it can't be duplicated, let alone being the PK. At first, after users had stopped receiving, I could not open the receiving table and it gave the message I asked about above. When I finally could open the table, I found the duplicates. It was skipping a number, then duplicating the following number. Anyway, the story doesn't end there. When I queried the receiving information and typed the batch number that the autonumber had skipped, it brought up the record for the next number which it had duplicated! When I queried another batch number that it had skipped, it brought up information for a batch two numbers behind it! I have gone beyond the built-in C&R utility and used the JetComp.exe on it, but that did not deal with duplicate issues in the table either or recognize any corruption issues. Any insight is appreciated.
Joe
Yes, fortunately I have multiple backups, but fortunately this portion of the database is only in the testing phase and has not been rolled out yet. I actually did do a compact and repair, but it did not resolve the issue. I didn't mention this in my first message, but this has apparently caused an issue with the table's PK, which is an autonumber. This autonumber serves as a "batch number" for items received [rec_batch_id]. Multiple users are accessing it via a form. After receiving something, I have a msgbox pop up informing the user what the batch number is for the item(s) just received. It appears that the "impossible" is possible after all, because Access is duplicating its own autonumbers in the receiving table! Just being an autonumber of course means it can't be duplicated, let alone being the PK. At first, after users had stopped receiving, I could not open the receiving table and it gave the message I asked about above. When I finally could open the table, I found the duplicates. It was skipping a number, then duplicating the following number. Anyway, the story doesn't end there. When I queried the receiving information and typed the batch number that the autonumber had skipped, it brought up the record for the next number which it had duplicated! When I queried another batch number that it had skipped, it brought up information for a batch two numbers behind it! I have gone beyond the built-in C&R utility and used the JetComp.exe on it, but that did not deal with duplicate issues in the table either or recognize any corruption issues. Any insight is appreciated.
Joe
JJman
One of the brighter stars of Tek-Tips, MichelRed, wrote an FAQ on this issue - on why not to use autonumber for multiuser systems, and a suggested ...
Richard
One of the brighter stars of Tek-Tips, MichelRed, wrote an FAQ on this issue - on why not to use autonumber for multiuser systems, and a suggested ...
Richard
- Thread starter
- #6
JJman
Another solution may be to change the property for the autonumber from incremental to random. This is done at the table design level.
The issue with using the incremental autonumber is when two people are creating an input record about the same time. Access grabs the number before the record is written. This way, you would not have to redesign your databasse - just make the table level changes.
By using the randomly generated autonumber, the chance of two poeple creating a record in the same time period, and generating the same random autonumber are pretty slim.
I have never seen it happen, but I guess there is the chance of generating / re-generating the same random number during two different data entry events is possible. Provided you are not replicating your database, the duplicate will fail to commit / write -- which is a good thing.
Richard
Another solution may be to change the property for the autonumber from incremental to random. This is done at the table design level.
The issue with using the incremental autonumber is when two people are creating an input record about the same time. Access grabs the number before the record is written. This way, you would not have to redesign your databasse - just make the table level changes.
By using the randomly generated autonumber, the chance of two poeple creating a record in the same time period, and generating the same random autonumber are pretty slim.
I have never seen it happen, but I guess there is the chance of generating / re-generating the same random number during two different data entry events is possible. Provided you are not replicating your database, the duplicate will fail to commit / write -- which is a good thing.
Richard
Wow...that's something I've heard of, but never seen with my own eyes...
Tried many times to cause it to happen, always failed. I'll probably succeed with a live database [LOL]
So...if it is possible, could you send me the table in question with fake data? I'm only interested in that autonumber field.
It's not the duplicated autonumber that puzzles me...it's the duplicated PK, which should be impossible to duplicate, otherwise the entire Access is just a piece of crap, even though I like it very much...
In any case, you could try 2 remedies:
1. Import all tables in a brand new database and see if it solves the issue.
2. Try Start->Run-> msaccess.exe /decompile "C:\Path\File.mdb"
Either the database is corrupt or your Jet engine has gone crazy...
However...after reading again:
"Access is duplicating its own autonumbers in the receiving table"
Is the receiving table the "many" part? If yes, there is nothing wrong, as it's actually assigning one PK to many FKs.
danvlas@yahoo.com
Thanks in advance,
![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")
Daniel Vlas
Systems Consultant
Tried many times to cause it to happen, always failed. I'll probably succeed with a live database [LOL]
So...if it is possible, could you send me the table in question with fake data? I'm only interested in that autonumber field.
It's not the duplicated autonumber that puzzles me...it's the duplicated PK, which should be impossible to duplicate, otherwise the entire Access is just a piece of crap, even though I like it very much...
In any case, you could try 2 remedies:
1. Import all tables in a brand new database and see if it solves the issue.
2. Try Start->Run-> msaccess.exe /decompile "C:\Path\File.mdb"
Either the database is corrupt or your Jet engine has gone crazy...
However...after reading again:
"Access is duplicating its own autonumbers in the receiving table"
Is the receiving table the "many" part? If yes, there is nothing wrong, as it's actually assigning one PK to many FKs.
danvlas@yahoo.com
Thanks in advance,
Daniel Vlas
Systems Consultant
Daniel
I have never seen the duplicated primary key myself.
However, in a past life, I do recall one DBA friend of mine commenting that the primary key reverts to a non primary key. This change in the rules for the key change may be in another database, not Access. From a serial sequential with no nulls, unique to a regular long int.
Comments?
Richard
I have never seen the duplicated primary key myself.
However, in a past life, I do recall one DBA friend of mine commenting that the primary key reverts to a non primary key. This change in the rules for the key change may be in another database, not Access. From a serial sequential with no nulls, unique to a regular long int.
Comments?
Richard
- Thread starter
- #10
Funny you should mention that. When I said before that the C&R did not fix the duplicates, I was only looking at the data itself and hadn't checked the field in design view. After doing so, I noticed that Access did make the field so that it is no longer a PK and now allows duplicates. I do, however, remember before doing the C&R that it was definitely still the PK and indexed as "Yes (No Duplicates)". I guess my Access equivalent of Bigfoot got away...
- Thread starter
- #11
Ok, Bigfoot's been captured after all. I found a copy of the database made before the C&R was done and, sure enough, the field is still the PK incremental autonumber (set to no dups), but there are dups/skips in it. If either of you would still like to see a copy, I'll need some suggestions on how to send it in a way that will preserve the data. I've deleted everything in the DB except that table, and even deleted the other fields in that table, but because I obviously can't do a C&R or bigfoot will go bye-bye again, the DB is still too large to just attach in an email. I tried putting it into a blank database, but, predictably, Access corrects the issues. Anyway, if you're curious, out of 196 records, 5 were skipped, with 5 others duplicated, (usually the following number). In one instance, a number (170) was skipped and the sequence was also broken: 167, 168, 169, 168, 171.
Thanks again.
Thanks again.
here is a ftp account that you can use:
ftp://ftp.artrom.ro
Login as: bigfoot
Password: tektips
Just hope nobody deletes the file before I get it
Looking forward to it...
Daniel Vlas
Systems Consultant
ftp://ftp.artrom.ro
Login as: bigfoot
Password: tektips
Just hope nobody deletes the file before I get it
Looking forward to it...
Daniel Vlas
Systems Consultant
- Thread starter
- #13
Ok, I've never dealt with FTP before, simple as I'm sure it is, so I'm "one taco short of a combo plate" on this one. I opened it but didn't get any prompt for any kind of login. I tried to copy the database to the folder but it failed and gave me an error.
?
Or
File->Login As
Or
(paste the link into your browser, i couldn't get it active)
Good luck
Daniel Vlas
Systems Consultant
File->Login As
Or
Code:
ftp://bigfoot:tektips@ftp.artrom.roGood luck
Daniel Vlas
Systems Consultant
- Thread starter
- #16
OK, I got the file. And also got a DoS attack on the server that really screwed my day. The account is now deleted - sorry, but I had to do it. And may it be a warning to all those guys who post such data on public forums...
Some thoughts about 'bigfoot':
Indeed, visually there are duplicates in the autonumber-surprisingly enough.
However, a Find duplicates query does not return any records and a Totals query shows a count of 1 for all numbers. There are 5 records less in the groups/totals query than in the original table, but a left join on the table and query does not reveal anything.
Created a related table and entered one value for the first instance of 168 and another record for the second instance.
<b>When doing a join between two tables, only 2 rows were returned. Which means that the 2 instances of 168 are actually the same. If they were duplicates, I would have obtained 4 rows. Which means you have a beginning of corruption in your database that causes displaying something else than what's actually stored.</b>
Since compacting the database gets everything back to normal, I'm almost sure that you face some 'light' corruption and you should do a decompile, a compact and a repair of the database.
It's however a relief, as i was going to throw out Access for duplicating the primary key.
Those records look as duplicates, but they are not...actually it looks like they don't even exist.
Daniel Vlas
Systems Consultant
Some thoughts about 'bigfoot':
Indeed, visually there are duplicates in the autonumber-surprisingly enough.
However, a Find duplicates query does not return any records and a Totals query shows a count of 1 for all numbers. There are 5 records less in the groups/totals query than in the original table, but a left join on the table and query does not reveal anything.
Created a related table and entered one value for the first instance of 168 and another record for the second instance.
<b>When doing a join between two tables, only 2 rows were returned. Which means that the 2 instances of 168 are actually the same. If they were duplicates, I would have obtained 4 rows. Which means you have a beginning of corruption in your database that causes displaying something else than what's actually stored.</b>
Since compacting the database gets everything back to normal, I'm almost sure that you face some 'light' corruption and you should do a decompile, a compact and a repair of the database.
It's however a relief, as i was going to throw out Access for duplicating the primary key.
Those records look as duplicates, but they are not...actually it looks like they don't even exist.
Daniel Vlas
Systems Consultant
Dan
With such varied results in your analysis, it would seem the indexes are messed.
Out of curiousity, can you delete and recreate the index or indexes? And does this impact the results?
Sorry about the DoS. Nasty stuff going about, and every month seems to bring new challenges.
Richard
With such varied results in your analysis, it would seem the indexes are messed.
Out of curiousity, can you delete and recreate the index or indexes? And does this impact the results?
Sorry about the DoS. Nasty stuff going about, and every month seems to bring new challenges.
Richard
-
1
- #19
A ways back, JJMan said, "When I queried the receiving information and typed the batch number that the autonumber had skipped, it brought up the record for the next number which it had duplicated!"
Right then, I says to myself, "The number in the index is different from the number in the table." Dan's analysis bears that out. Analytical processes that use the index see no duplicates, while those that process the table directly see them.
Dan said, "Which means that the 2 instances of 168 are actually the same. If they were duplicates, I would have obtained 4 rows. Which means you have a beginning of corruption in your database that causes displaying something else than what's actually stored."
Or, it means that two index rows are pointing at the same location, because a failure to serialize allowed two instances of Access to allocate the same slot for their records. It would be interesting to find out whether the rows have equal bookmark values--though I'll admit I'm not sure I'd know how to interpret any particular result.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
Right then, I says to myself, "The number in the index is different from the number in the table." Dan's analysis bears that out. Analytical processes that use the index see no duplicates, while those that process the table directly see them.
Dan said, "Which means that the 2 instances of 168 are actually the same. If they were duplicates, I would have obtained 4 rows. Which means you have a beginning of corruption in your database that causes displaying something else than what's actually stored."
Or, it means that two index rows are pointing at the same location, because a failure to serialize allowed two instances of Access to allocate the same slot for their records. It would be interesting to find out whether the rows have equal bookmark values--though I'll admit I'm not sure I'd know how to interpret any particular result.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
BTW, if it really is just a corrupted index, you should be able to delete the index, save the table, and re-add the index. The record with different keys (depending on whether you access it via the index or via the table) should disappear, since it's not really in teh table anyway (it was overwritten by the other record).
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question
