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.
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.
ASKER
Mx, We can reproduce. Give me 30 minutes to prep a limited mdb and upload.
ok
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
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
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
Rob
No need to 'step back' :-)
mx
mx
ASKER
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
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
ASKER
broro183,
Thanks for the input. That is not our situation.
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
mx
ASKER
No it does not. I didn't want to send 80,000 records.
ASKER
mx,
I do find that a new table created in that mdb does not experience the same problem.
I do find that a new table created in that mdb does not experience the same problem.
ASKER
Yes, I mispoke. That table will break when run in 2010.
ASKER
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
80K not that big ... just do a C&R then zip ...
mx
ASKER
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
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
ASKER
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.
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
Man ... this sounds familiar ... I've seen this recently ...
mx
ASKER
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
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
ASKER
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
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
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
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
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
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
ASKER
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.
"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
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
ASKER
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?
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
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
ASKER
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. "
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
ASKER
Mx,
No 100% 1 to N. Yes, unless a set of records was deleted due to a student being archived. Cascade delete.
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
Why don't you upload a similar version of this new table ... and let me test it.
mx
ASKER
Mx,
Will do in 60 minutes or so.
Will do in 60 minutes or so.
ASKER
Mx,
I will not be able to upload file tonight. Will try as early as possible in am.
Sorry. I sure appreciate your expertise.
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.
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.
ASKER
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
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:
- Does this work for you?
hth
Rob
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
- 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
hth
Rob
ASKER
Thanks broro183,
I will try to review Thr.
Steve
I will try to review Thr.
Steve
ASKER
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.
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 ?
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 ?
ASKER
Fail means it tries to use a pre-existing AN.
Note if primary key is ascending, no problem...
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 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 ?
ASKER
I did this in 2010.
No prob in 2007.
No prob in 2003.
Original mdb has had descending PK for years.
No prob in 2007.
No prob in 2003.
Original mdb has had descending PK for years.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very odd. But I feel confident we have identified the conditions.
Now what?
Now what?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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/
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
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
ASKER
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
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.
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
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
ASKER
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
If you are referring to my last post, I tested with several tens of thousands of records.
mx
ASKER
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
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
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 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 ...
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
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
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
mx
ASKER
Thanks for the update Mx.
Believe me ... it's raising some eyebrows !!
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