Link to home
Start Free TrialLog in
Avatar of slgoetze
slgoetze

asked on

MS Access 2010 Compact Breaks Autonumber

MS Access split application (.mde, .mdb) developed using Access 2003.
300 sites, 5 year history
Includes a table which has a field which is the Primary key and is AutoNumber data type.
Using .mdb (2002-2003) vs .accdb (2007) file format.
After using access 2010 sp1 compact and opening again in 2010 sp1 experiencing dysfunction of the AutoNumber field. Ie. It does not try to use the next available value. It tries to use a previously used value causing error of ‘duplicate value in index’.
Do not see this problem when using access 2007 compact.
FYI, we see same result if we convert to accdb.
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

This slightly sounds familiar, but I can't quite put my finger on it.

I assume you have a backup?  Are you able to reproduce this?
Can you upload a db with just this table, that does not have the problem?  I can try C&R in A2010 on systems with and w/o SP1 ... curious to see if this happens.

mx
Avatar of slgoetze
slgoetze

ASKER

Mx, We can reproduce. Give me 30 minutes to prep a limited mdb and upload.
hi,

These links may or may not help...

I think this is likely to be the winner, http://support.microsoft.com/kb/812718 (sourced from http://www.access-programmers.co.uk/forums/archive/index.php/t-193628.html).
While this link of Allen Browne's may explain why it is happening: http://allenbrowne.com/ser-40.html

The next one includes Access 2007, but may (?) also play a part for Access 2010: http://support.microsoft.com/kb/287756, while a similar "reset seed number" is also shown in the links at the base of Allen's page. The above link of Allen Browne's is mentioned in a lot of threads such as:
http://www.accessmonster.com/Uwe/Forum.aspx/access-tablesdbdesign/15297/AutoNumber-Field-Access-2007
http://www.accessmonster.com/Uwe/Forum.aspx/access/98390/starting-value-for-autonumber-in-access-2007

Some comments relating to Access 2003: http://compgroups.net/comp.databases.ms-access/Compact-Repair-and-AutoNumber-problem

fwiw, I also came across this link which describes the issue of "compact and repair deleting db's" which has bitten my workmates & I earlier this year.

hth
Rob
Ooopps!
I was too slow on the posting - I didn't see DatabaseMX's posts before submitting. I suspect you are in much more knowledgeable hands with him, so I will step back & learn :-)

good luck :-)
Rob
ps: My last sentence of "fwiw, I also came across this link which describes the issue of "compact and repair deleting db's" which has bitten my workmates & I earlier this year." was meant to be followed by this link: http://blogs.office.com/b/microsoft-access/archive/2008/05/29/kb-article-950812-compact-and-repair-might-delete-your-database-access-2007.aspx

Rob
Finally. Attached is mdb. Open using msa 2010. Open table Activity and begin to add a record. You will see seqno (autonumber) is in the 80,000 range which is good. Hit esc twice to undo. Compact.
Open table Activity and begin to add a record. You will see seqno (autonumber) is NOT in the 80,000 range which is bad.
Thanks for your help.
2mx.zip
broro183,
Thanks for the input. That is not our situation.
ok ... is the db you uploaded have the problem?  Because there is a gap 9998 >> 80688.  I need a copy of the db *before* the problem existed ...

mx
No it does not. I didn't want to send 80,000 records.
mx,
I do find that a new table created in that mdb does not experience the same problem.
Yes, I mispoke. That table will break when run in 2010.
Table Activity will break autonumber.
Well, see ... I wanted to see if ... I could make in happen on a 'clean' table, in A2010 w and w/o SP1.

80K not that big ... just do a C&R then zip ...

mx
Mx,
Attached is mdb with 80,000+ records in table Activity.
2mx-2.zip
WOW ... I added a few records before C&R on an non SP1 A2010 system.  No problem. THEN ... I did a C&R, then the problem started.  Next AN was 774 instead 80690 !  774 , then 775 ... both already used.

I then deleted ALL records, did a  C&R ... and AN started back a 1 and incremented correctly.

I suspect this table is corrupted.  However, I will try this on my A2010  SP1 system at home tonight.

mx

Mx,
That is what I see. Entry is ok until C&R, then AN breaks.
I did create an mt mdb in 2007 and imported tables, data, relationships, etc.
It broke as soon as did 2010 C&R...
Note: 2007 C&R does not break AN.
Thanks again for your help.
I will test on A2010 w/SP1 tonight.

Man ... this sounds familiar ... I've seen this recently ...

mx
You can do it!
Good luck finding a solution.

I did have one more Google search & came across the below, which makes me ask, what are your reasons for using an AutoNumber field?

you shouldn't care what the autonumber is. If you care, you are using the autonumber in a way that you shouldn't. It really only guarantees you a UNIQUE number and not in any particular order, although increment usually does do it that way but it isn't a guarantee about that.

If you care what the numbering is, you need to use your own numbering using DMax+1 and possibly in combination with a one record table which can help lock others from getting the same number.

Sourced from http://www.access-programmers.co.uk/forums/showpost.php?p=1047908&postcount=10

Mx, :-)
You're much more likely to find a solution than me, for one, you have Access 2010, and for two, I'm learning/guessing as I go!

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

Rob
Thanks Rob. Application has been in use at over 300 sites for 5 years. MSA 2010 is first big problem.
broro183:
Here is the problem:
Because it's trying to add an already previously used AN, that's not allowed by the definition of the AN. So, it creates a runtime error. That's the issue. So, it's a serious problem.  The issue is not 'what are we using the AN for.

Just an FYI ...

mx
OK ... On my A2010 SP1 system at home, I got the following results.

MDB.  Before doing a C&R ... no problem.
MDB.  After doing a C&R ... AN is incorrect, tries to use previous value.

Created a blank ACCDB, import the table from the original MDB.
Same results.

I also did a Make Table query from the original MDB into a new table in the ACCDB.
Same results.

So, it appears this table is corrupted.

mx
Mx,
I disagree to an extent perhaps we all need to "step back" ;-)
This issue is shown in various sites/posts cross the web, and if the same functionality can be achieved using a work-around such as Dmax, then I think it is a viable alternative. We can't identify if DMax will allow the "same functionality" unless we understand what is needed by the application at a more holistic level than the current run-time error that is hogging the limelight.

Sometimes with "technological progress" there comes a need for change even if there are 5 years of successful usage. So it may be necessary to use a work around, or a more brutal solution could be to stop using MSA 2010, but I don't think that would be acceptable... would it?

Rob
What happens if you try one of the "reseeding" codes from my earlier links in MSA 2010?


Rob
Rob:
You are missing the point here. This table is corrupted. What an Access Auto Number tries to reuse an *existing* number already in the table ... for a new record, the table and its records clearly have a problem, and is such unreliable.  There is no point in trying to find a work around on a corrupted table and set of records. Again, the key word and point here is ... unreliable.

Also, none of the links I've seen you post have to do specifically with this issue of corruption.

And FWIW,  the DMax+1 scenario has potential problems in a multi-user scenario.

99.987 % of the time, the Access Auto Counter is extremely reliable.

You might a gain reread my post at http:#a36332811 ...

mx
MX,
"So, it appears this table is corrupted."    At many sites.
300 sites (if they all go to MSA 2010).
Any thoughts on how one might fix the table?  
We see 10 of 12  msa 2010 sites have problem.
hi Mx,

Yes, you're right, I'm sorry I did miss the point that the table is corrupted and therefore unreliable. In fact, I missed your whole message, for some reason (probably me not hitting [F5]!) my screen didn't show your posthttps://www.experts-exchange.com/questions/27244958/MS-Access-2010-Compact-Breaks-Autonumber.html#36333559.

There's a couple of problems with me trying to learn as I go:
1) I read a lot more pages than I provided as links and somewhere in the other pages must have been the points that I've now taken on as background info ie ones that relate specifically to this issue of corruption. Checking through my links the closest I can see would be the last three posts of http://compgroups.net/comp.databases.ms-access/Compact-Repair-and-AutoNumber-problem , ie from Dave Fenton's (1/29/2010 6:45:03 PM) post onwards. However, re-reading that I can see that Dave Fenton only goes as far as suggesting "importing all the data tables into a new database" which is what you've already tried in https://www.experts-exchange.com/questions/27244958/MS-Access-2010-Compact-Breaks-Autonumber.html#36333559
2) As I've just shown, when it comes to some MS Access issues - I'm just a Google monkey! :(

re "FWIW, the DMax+1 scenario has potential problems in a multi-user scenario":
Thankyou, I hadn't considered this, but now that you mention it, I can certainly see the potential for problems.

Slgoetze,
Mx has already tried a make table approach which didn't work. Some other approaches that I can think of (& you or Mx may have already tried) are:
- create a new table from scratch (via the MSA UI) and then populate it with data from the old table using an Append query.
- or do the same thing via vba code using tabledef's & recordsets.
- Try the different methods listed in http://support.microsoft.com/kb/812718. The link might not be quite what you're after, but at a glance it does appear to result in the table being recreated which will hopefully result in a new and non-corrupted table.

Rightio, back to my monkeying...
I am still interested to know if it works in MSA 2010 after one of the "reseeding codes" are used for example if you follow either of the below steps in MSA 2010, does the AutoNumber then start to work?
- http://support.microsoft.com/kb/287756
or
- in http://support.microsoft.com/kb/209696 and then running http://allenbrowne.com/func-ADOX.html#ResetSeed with a AN number of 80,000
http://allenbrowne.com/ser-25.html is titled "Preventing corruption"
http://allenbrowne.com/ser-47.html is titled "Recovering from corruption"

Mx,
I noticed repeated use of "currentdb", in the code for the Clean4Distribution form, as I glanced through the code in the db that has been uploaded. I'd say that this is nothing to do with the corrupt table esp as I can see no interaction between the code & the Activity table, however I'm curious what you would do.
The MSA 2007 help files state "You can use the CurrentDb method to create multiple object variables that refer to the current database." and "The CurrentDb method creates another instance of the current database". I would personally set a variable to point at a single "currentdb" and then use the variable throughout the rest of the code/sub. What approach would you use to refer to the required database?

Rob
Mx,
Update. In 2010 .mdb
1. Made a new table using orginal defs.
2. Removed relationships on orginal table and saved original table to SAVE.
3. Deleted all records from original.
4. Compacted
5. Appended records from SAVE into original except autonumber field.
6. Added back relationsips on original
7. Compacted.
8. Appears to be good.
Do you see a problem with above?
Actually, I had a question first:

In your original MDB, it appears that a lot of records have been deleted, since the Auto Number value is much larger than the total number of records.  How and when were these records deleted ?

mx
Mx,
Remember, I deleted them to keep the size down. Then republished. See 8/8 03:24 pm.  "Attached is mdb with 80,000+ records in table Activity. "
ok ... so in your actual production mdb, there are no deleted records, and is the AN sequential (before any compact) from 1 to N, where N = total # of records ?

mx
Mx,
No 100% 1 to N.  Yes,  unless a set of records was deleted due to a student being archived. Cascade delete.
ok, well re "Update. In 2010 .mdb" ... that's what I was about to try last night, but it got to late ... and I suspected it would probably resolve the corruption issue, which appears to be the case.  So, in that context, I would say ... Yes.

Why don't you upload a similar version of this new table ... and let me test it.

mx
Mx,
Will do in 60 minutes or so.
Mx,
I will not be able to upload file tonight. Will try as early as possible in am.
Sorry. I sure appreciate your expertise.
The real question is ... what caused the corruption?  And worse, WHY ... does doing a C&R only in A2010 render this issue!!!??  Damn weird.  You can C&R in A2003 all day ... no issue.

It's not completely clear really ... IF ... A2010 C&R somehow ... causes this issue. But, I tried 2-3 of my big tables in A2010, and could not reproduce that behavior.
Mx,
Attached is a sample with 'repaired' tbl Activity if you have time to play.
1. Created tbl Activity NEW and Activity SAV from broken Activity tbl.
2. Emptied Activity NEW.
3. C&R.
4. Appended Activity NEW from Activity SAV except AN field seqno letting msa autonumber.
5. C&R
6. Delete tbl Activity and Rename Activity New to Activity.
7. Test entering new records ok.
8. Delete tbl Activity SAV
We will continue to exercise this. We can script most of this but am looking for reliable code to rename tbl Activity New to Activity. I seem to be stumbling on this. Brain broken.
2Mx3.zip
I want to help find a solution too and learn along the way so I'm still popping in occasionally.

I've been thinking about about mx's question, "what caused the corruption?", and if it's not the AN, then perhaps it is one of the other fields so I went trawling through Allen Browne's site for something I'd read in the past.
Other field types in your table (which may or may not be in mx's large tables) include:

- Yes/No fields which are discussed on: http://allenbrowne.com/NoYesNo.html
- Memo fields which are discussed on: http://allenbrowne.com/bug-18.html
This is slightly out of context but Ron Paii states
"... one of your memos may be corrupted which can crash a compact & repair. You can find the record by opening the table and scrolling down until you see "ERROR" in the memo
field. Do not enter this record, use a query to delete the record then try
compact & repair.
"
in http://bytes.com/topic/access/answers/828743-app-crashes-compact-repair

-See post four by Allen Browne for discussion of Name Autocorrect & memo fields in http://objectmix.com/ado-dao-rdo-rds/751574-opening-table-cause-access-crash.html
- Even less relevant than the above may be, but interesting list of issues for Access 2007: http://www.everythingaccess.com/tutorials.asp?ID=The-lowdown-on-Access-2007
- probably irrelevant regarding truncation of memo fields: http://allenbrowne.com/ser-63.html

Regarding renaming the table:
What goes wrong with each of the code variations that you currently have in the "Private RenameAct_Click sub()"?
- Are you checking (somewhere else?) that the table is closed & that there are no locks on the table, for example bound forms etc, before trying to rename it?
- do you get any error messages, if so, what are they?
- does it help if you add the following lines:
        If Left(dbRename.TableDefs(i).Name, 20) = "Activity New" Then
            ''RB: or alternatively, does this work...?
                'If CBool(InStr(1, dbRename.TableDefs(i).Name, "Activity New")) Then
            ''RB: which can be effectively shortened to...
                'If InStr(1, dbRename.TableDefs(i).Name, "Activity New") Then
            
            dbRename.TableDefs(i).Name = Replace(dbRename.TableDefs(i).Name, "Activity New", "Activity")
        End If
    Next i
    'RB: does this line help?
    dbRename.TableDefs.Refresh
''RB: I don't think this is relevant but I saw it in the object browser
 '   dbRename.Synchronize
    dbRename.Close

Open in new window


- Does this work for you?
Sub test()
    Call CloseAndRenameTable("Activity New", "Activity")
End Sub

Sub CloseAndRenameTable(cur_tblName As String, new_tblName As String)
'RB: note this snippet doesn't test if the table is locked via a form or recordset etc...

'check if the table exists
    If DoesTableExist(cur_tblName) Then
        'use an error wrapper to let the code continue if the table isn't actually open
        On Error Resume Next
              DoCmd.Close acTable, cur_tblName, acSavePrompt
        On Error GoTo 0
        'http://msdn.microsoft.com/en-us/library/aa220691(office.11).aspx states
        'syntax of: docmd.Rename(NewName, ObjectType, OldName)
        DoCmd.Rename new_tblName, acTable, cur_tblName
    Else
        MsgBox "table doesn't exist!"
    End If

End Sub

Function DoesTableExist(tblName As String) As Boolean
'sourced from: http://www.ozgrid.com/forum/showthread.php?t=58675&p=303136#post303136
Dim objTable As dao.TableDef

    On Error Resume Next
        Set objTable = CurrentDb.TableDefs(tblName)
    On Error GoTo 0
    DoesTableExist = Not objTable Is Nothing
    Set objTable = Nothing
End Function

Open in new window


hth
Rob
Thanks broro183,
I will try to review Thr.
Steve
MX,
We set up the following:
Create new empty mdb
Create Table1
      Fields:       ID, autonumber, PK ascending
            F2, text (255)
Add 500 records
Compact & Repair
Add record 501 manually, ok
Delete all records
C&R
Add 1003 records
C&R
      Add record 1004 manually, ok
Change PK to Descending
C&R
      Add record 1005 manually, FAIL
Conclusion: Following a C&R, if more than 1001 records, insert fails when PK on autonumber field is descending.

Change PK to Ascending
C&R
      Add record 1006 manually, ok
Conclusion: Following a C&R, if more than 1001 records, insert fails when PK on autonumber field is descending.

Ironically enough, I am at this moment writing up an email with sample db and screenshot ... to post on our MVP special distribution list.

Whereas it *initially* appears that this table is some how corrupted, I am now not sure that is the case.  Because ... *until* you compact the MDB inside of A2010, there is no apparent issue.  As soon as you compact in A2010, a MAJOR problem starts.  Not only does it try to use a previously deleted Auto Number - which it allows to become a new record's autonumber, it also attempts to add an EXISTING Auto Number, which it does not allow,  because that would violate the unique PK principle.

This is very repeatable.  So, this may in fact be a serious problem with the Compact & Repair in A2010.  And I've tried this on both a system with and w/o SP1.

I will post those screen shots here in a bit.

"We set up the following:"
You did this in A2010 ?

When you say Fail, what is failing ?

Fail means it tries to use a pre-existing AN.
Note if primary key is ascending, no problem...
You did this in A2010 ?

I just recreated THAT problem in one of my own tables - in A2010 ! WOW.  It results in the exact same effect that I described above.

Have you tried that in A2003 ?  I will shortly also

Question:
With your original MDB ... did you at some point, change the PK to be Descending from the default Ascending value - using the Indexes dialog ?
I did this in 2010.
No prob in 2007.
No prob in 2003.
Original mdb has had descending PK for years.
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Very odd. But I feel confident we have identified the conditions.
Now what?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Abosolutely. Thanks.
FYI, I have one Microsoft person telling me compact & repair ALWAYS breaks AuotoNumber.
"As far as I know, after compacting and repairing the Access database, the AutoNumber will be broken. "
http://social.microsoft.com/Forums/en-US/partnerofficeaccess/thread/9dc92ff4-c5a5-47c5-ad8c-9590d3eb8e69/ 
For what ever reason, I cannot view that link " You are not authorized to perform this action".

However, that is completely False.  Hard to believe a Microsoft employee would make such an erroneous statement!  Can you tell who the person is ... are you SURE it's a Microsoft employee ... or just someone posting.

I forget ... does your A2010 system have SP1 installed ?

mx
Link. Have to sign in...
You and I know it is false but he doesn't.
My 2010 does have sp1.
See attached.
Memo-Style.pdf
Forums-Access-2010-autonumber-br.txt
Zhongjie Ren
Partner Online Technical Community
No clue who that is ... doubtful a MS employee ... I HOPE not.
This is another important discovery I have made during this investigation (and will also report to the Microsoft Access Product group).

Once again, the general behavior of Compact & Repair regarding the Access Auto Number is changed from previous versions, as follows:

Assume a Table with sequential Auto Numbers from 1 - 100

A2003 (and some other previous versions):

Add new record - AN becomes 101
Delete new record,
Compact & Repair
Add new record - AN becomes 102  
101 is *not* reused

A2010 (and some different other previous versions)

Add new record - AN becomes 101
Delete new record
Compact & Repair
Add new record - AN becomes 101
101 *is* reused.

So ... this may be related to the behavior/BUG we are seeing here ...

mx

Please test with more than 1000 records.
Test what ?

If you are referring to my last post, I tested with several tens of thousands of records.

mx
1000 seems to be the tipping point if PK is descending...
ok ... but above, I'm referring to a different situation ... how Auto Numbers are affected by Compact & Repair, not a bug ... but just what happens.  Although, there could be a connection.

mx
One more note on my post HERE

For the A2010 test, same results on either on either an MDB or ACCDB.

I tested on tables with different starting recordsets ... 5 records, and 550,925 records.  Again, same results.  Similar recordsets for the A2003 test.

mx
"Conclusion: Following a C&R, if more than 1001 records, insert fails when PK on autonumber field is descending."

I just confirmed this.  Upon adding record 1002 (after a C&R after adding 1001), it fails.  Either tries to use a previously used AN - if there are some gaps, OR ... an EXISTING AN !.

"Conclusion: Following a C&R, if more than 1001 records, insert fails when PK on autonumber field is descending."
Not completely sure about this yet ...

mx
I came across this thread from another, and I also wanted to mention that I too experience the behavior exactly as described.

Joe: Have you posted this to the Access MVP email list? I haven't seen it if you have ...
BTW: I'm running Access 2010 SP1 on windows 7 64-bit, with all service packs up to 08/15/2011 installed.
"Joe: Have you posted this to the Access MVP email list?"

Just now ... check your email "A2010 Compact & Repair /  Auto Counter behavior ... Part One of Two ..."

thx.mx
Will write Part Two shortly ... on the hosed up numbering ...

mx
Part Two is in the process of being report.  Effectively, an MVP saw your post on this on that other site, so it's being discussed.  Also, another person has reported similar behavior, only w/o the descending sort per se.

mx
Thanks for the update Mx.
Believe me ... it's raising some eyebrows !!