Compact/Repair and AutoNumber problem
in [Help]
|
Prev: Matching Partial Data
Next: Ping Allen Browne
From: Salad on 28 Jan 2010 22:42 Environment: Access 2003, connecting to site via Remote Desktop I have my database on a network drive. It is not split since this is a non-production product at this time, just to show off the features. I cleared out all the tables that aren't lookup. I then Compacted/Repaired it. I then went into the table directly and entered 1 record. It was number 8 in the autonumber field. I deleted the record. I then Compact/Repaired the mdb. I opened the table directly and entered a record and the autonumber was 9. Normally when I compact/repair an empty table the autonumber will revert to 1. I did this several times. I then Explored to the file. I had a bunch of DBs there; DB1.MDB to DB9.MDB. For some odd reason the Compact/Repair wasn't deleteing the files after Compact. There was NO error when I did the compact. Any idea on why the autonumber didn't reset? Any idea why the temp files weren't deleted after compact? My rights in that folder are Full Control, Modify, Read/Execute, Read, and Write. The only right that is removed is Special Permissions. I then copied the mdb to the C: drive. I have the same folder rights. I compact the mdb, fill in a field and the autonumber is 1, then hit ESC so the record isn't saved. I then compact the databse, add enter some data in the field, the autonum is 1 as expected, and hit ESC. It will do this forever. But once I add records to the table, I can't get back to #1 if I delete all the records and compact/repair it. Any reason you can think of for why the autonumber doesn't reset?
From: Allen Browne on 29 Jan 2010 00:18 Do you have Delete permissions on the db folder? Check that you have at least SP8 for JET 4, and SP2 for Office 2003. If you have SP3, you probably want this hotfix: http://support.microsoft.com/kb/945674 You'll want to solve the issue of the failed compact/spurious files, but if you need to reset the autonumber programmatically: http://allenbrowne.com/func-ADOX.html#SetSeed -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Salad" <salad(a)oilandvinegar.com> wrote in message news:e4ydna7dHJ-PxP_WnZ2dnUVZ_uadnZ2d(a)earthlink.com... > Environment: Access 2003, connecting to site via Remote Desktop > > I have my database on a network drive. It is not split since this is a > non-production product at this time, just to show off the features. > > I cleared out all the tables that aren't lookup. I then > Compacted/Repaired it. I then went into the table directly and entered 1 > record. It was number 8 in the autonumber field. > > I deleted the record. I then Compact/Repaired the mdb. I opened the > table directly and entered a record and the autonumber was 9. Normally > when I compact/repair an empty table the autonumber will revert to 1. > > I did this several times. I then Explored to the file. I had a bunch of > DBs there; DB1.MDB to DB9.MDB. For some odd reason the Compact/Repair > wasn't deleteing the files after Compact. There was NO error when I did > the compact. > > Any idea on why the autonumber didn't reset? > > Any idea why the temp files weren't deleted after compact? > > My rights in that folder are Full Control, Modify, Read/Execute, Read, and > Write. The only right that is removed is Special Permissions. > > I then copied the mdb to the C: drive. I have the same folder rights. I > compact the mdb, fill in a field and the autonumber is 1, then hit ESC so > the record isn't saved. I then compact the databse, add enter some data > in the field, the autonum is 1 as expected, and hit ESC. It will do this > forever. But once I add records to the table, I can't get back to #1 if I > delete all the records and compact/repair it. > > Any reason you can think of for why the autonumber doesn't reset? > >
From: Salad on 29 Jan 2010 01:08 Allen Browne wrote: > Do you have Delete permissions on the db folder? Hi Allen: Back in my old Novell days I could say yes or no. Now? Maybe. I move to the folder, right click on the file, hit properties, then Security in the dialog tab, click my name from the security list I see Full Control, Modify, Read/Execute, Read, Write. The only one not checked is Special Permissions. However, if I highlight a file, right click and select Delete, the file gets deleted. So I'll assume that I do. > > Check that you have at least SP8 for JET 4, and SP2 for Office 2003. > If you have SP3, you probably want this hotfix: > http://support.microsoft.com/kb/945674 I have SP3 if I click on Help/About from the database window. I used your function GetVersion() and it returned 4.0.9511.0. If I open up a module and click Help/About MS-VB/SystemInfo then from the dialog select Office 2003 /Microsoft Access 2003 I have 3 options. Under Summary it says I have Jet 4. Under Jet Core Components, Description is Jet 4.0 but Path and Version are blank. I don't know what the SP is for my Jet4. I will download the hotfix now. > You'll want to solve the issue of the failed compact/spurious files, but > if you need to reset the autonumber programmatically: > http://allenbrowne.com/func-ADOX.html#SetSeed > Here's something else that is weird. I open up the table. I had created a relationship with it and another table in the relationships window. Later, I highlighed the second table and deleted the relationship and the table...or maybe I simply selected the table and pressed Delete. If I look at MsysRelationships, the two tables are still related. I can't delete the rows from MSysRelationships. It seems if one makes a mistake one is stuck with that mistake forever. So when I open the primary table, enter some data in a field to see the next autonumber, then hit the table window's (X) to close the table, I get a message box in Chinese. I hit OK, then I get another message box in Chinese. I hit OK and I get "Can't add or change record because a related record in table x is required." I hit OK and I get another message box that says "You can't save this record at this time". I have absolutely NO idea why I'm getting a Chinese language messagebox. If you have some insight, I'll be glad to have it. On this machine I have Visio, Word, Access 2003. Also, Excel, Outlook, PowerPoinmt, and Publisher 2007. I used to have A2007, but not tonight.
From: Salad on 29 Jan 2010 01:21 Salad wrote: > Allen Browne wrote: > >> Do you have Delete permissions on the db folder? > >> Check that you have at least SP8 for JET 4, and SP2 for Office 2003. I downloaded the HotFix but can't run it. It says I don't have admin privileges. I'll have to wait till the morning.
From: Allen Browne on 29 Jan 2010 02:39
If there are spurious relationships, hidden tables, bad data types (showing as Chinese chars), it might be worth creating a new database and importing everything. Before doing that, I'd run the standard recovery sequence: http://allenbrowne.com/recover.html Then import all but the problem table. Then import the problem table without relationships. Then create the valid relations between this table and the others. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Salad" <salad(a)oilandvinegar.com> wrote in message news:s4udnQp2ltDS5v_WnZ2dnUVZ_uKdnZ2d(a)earthlink.com... > Allen Browne wrote: > >> Do you have Delete permissions on the db folder? > > Hi Allen: > > Back in my old Novell days I could say yes or no. Now? Maybe. I move to > the folder, right click on the file, hit properties, then Security in the > dialog tab, click my name from the security list I see Full Control, > Modify, Read/Execute, Read, Write. The only one not checked is Special > Permissions. However, if I highlight a file, right click and select > Delete, the file gets deleted. So I'll assume that I do. > >> >> Check that you have at least SP8 for JET 4, and SP2 for Office 2003. >> If you have SP3, you probably want this hotfix: >> http://support.microsoft.com/kb/945674 > > > I have SP3 if I click on Help/About from the database window. > > I used your function GetVersion() and it returned 4.0.9511.0. If I open > up a module and click Help/About MS-VB/SystemInfo then from the dialog > select Office 2003 /Microsoft Access 2003 I have 3 options. Under Summary > it says I have Jet 4. Under Jet Core Components, Description is Jet 4.0 > but Path and Version are blank. I don't know what the SP is for my Jet4. > > I will download the hotfix now. > >> You'll want to solve the issue of the failed compact/spurious files, but >> if you need to reset the autonumber programmatically: >> http://allenbrowne.com/func-ADOX.html#SetSeed >> > > Here's something else that is weird. I open up the table. I had created > a relationship with it and another table in the relationships window. > Later, I highlighed the second table and deleted the relationship and the > table...or maybe I simply selected the table and pressed Delete. If I > look at MsysRelationships, the two tables are still related. I can't > delete the rows from MSysRelationships. It seems if one makes a mistake > one is stuck with that mistake forever. > > So when I open the primary table, enter some data in a field to see the > next autonumber, then hit the table window's (X) to close the table, I get > a message box in Chinese. I hit OK, then I get another message box in > Chinese. I hit OK and I get "Can't add or change record because a related > record in table x is required." I hit OK and I get another message box > that says "You can't save this record at this time". I have absolutely NO > idea why I'm getting a Chinese language messagebox. > > If you have some insight, I'll be glad to have it. > > On this machine I have Visio, Word, Access 2003. Also, Excel, Outlook, > PowerPoinmt, and Publisher 2007. I used to have A2007, but not tonight. > |