Can't re-use deleted auto number records

[GeneHo10]

My database is a record of CDs (programs and others) I have in CD hanging
sleeves. I designated the Auto Number field as Primary Key. I had 244
sleeves of CD records and cleaned out a lot of obselete programs to trim my
database; thinking I could use the new vacant sleeves for new CD/DVDs. I
trimed the database down to #182. That worked OK with the vacant numbers
prior to #183 until I reached record #182 and realized by deleting the last
field #244 the database index must still recognize those vacant numbers from
#183 to #244. So I got to the last field and expected the next fill-in to be
#183; instead the database insisted on designating it as #245 (which would
have been the original database continuation).

Is there a way to get out of this dumb mistake and continue the series at
#183 so I won't have to leave a gap of numbers from 183 to 244? I couldn't
find a solution in Help or Microsoft's on-line support.

 

[Bill Mosca]

AutoNumbers should really not be used for any meaningful value as you have
seen. You could make a copy of the table (structure only) and then insert
all the records from the old table into the new one, but you would
eventually have the same problem should you delete records again.

I suggest adding a new field to your table. Make it a number (long). Update
it to the value in the AutoNumber field. Then start using the new field for
your sleeve numbers. You will have to manually enter that number for new
CDs, but I wouldn't think that would be much trouble in this database. Think
of the sleeve number as a simplified street address.

 

[KARL DEWEY]

Try Compact and Repair, then close Access and re-open.

 

[GeneHo10]

I did the Compact and Repair after viewing another post about a somewhat
similar situation, but it didn't do the job. Thanks for responding.

 

[GeneHo10]

Thanks for the education --- I think I can make your recommendation work OK.
Appreciate your time and thoughts.

 

[Bill Mosca]

You're welcome.

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals

 

[a a r o n . k e m p f]

with SQL Server you're free to reset the identity increment and seed
values whenever you want

 

[posted_by_anonymous]

with SQL Server you're free to reset the identity
increment and seed values whenever you want

SFW, rattlebrain? They are for unique identifiers, surrogate keys, and other
internal use, not for showing to users. And what you describe does not solve
the problem posed by the poster, anyway.

Do you know why you have this irrational, overwhelming need to make a fool
of yourself in public? If so, are you sticking to your recommended
treatment? If so, it does not appear to be working very well. If not, have
you considered finding a good psychoanalyst to determine what you can do
about these obvious personality flaws?

Anony Mous

 

[a a r o n _ k e m p f]

I disagree.

Sorry.

I'd never ever let anyone CHANGE them.. but for christ sakes-- most
tables should have a single key identity field.. because otherwise
you're stuck with a very very WIDE pointer to that record... which
would slow down every other index on the table.

Most users I interact with surely understand the concept of an
identity / autonumber.. and they don't toy with it.
It helps them to find records... I mean.. honestly-- what is the
point?

yah-- anything going external- like the public internet- should have
something more obfuscated-- but for interal, intranet apps-- a lot of
times it makes sense to expose this autonumber field to your users.

it helps them to find their data.

-Aaron

 

[Fred]

I think that he was referring to the fact that your answer to every Access
question is, in essence to tell the person to not use Access.

Typical new posters don't know your agenda and think that you are actually
answering their question. The result is that your answers do harm, not good.
And consistently doing harm to people to serve your anti-Access agenda is
the personality flaw that the poster was referring to.

 

[a a r o n . k e m p f]

whatever dork

Jet is dead
and no one cares

SQL Server is frequently used with Microsoft Access.
it makes sense with anyone with a clue-- to use ADP.

Binding a sproc to a form through SQL Passthrough is just 'too much
work' otherwise

Copying queries to all your front ends-- is just too much work.
Having 3 tiers of Access Jet Databases-- is just too much work.

SQL Server is NOT more complex, it is easier.. no linking tables--none
of that BS.

Just plug and play.

File, New, Project (existing data).

thanks

-Aaron

 

[a a r o n . k e m p f]

is it a personality flaw to STAND UP AGAINST A CRAP DATABASE THAT
CORRUPTS DATA AND DOES NOT SUPPORT CHANGING CONDITIONS?

Is it _MY_ fault that jet doesn't work well over Wan, VPN, Wireless,
etc?

Is it my fault?

Jet is stupid as is anyone that uses it.

sorry

-Aaron

 

[George Hepworth]

IMHO, the best way to deal with trolls is a two-part strategy.

1. Ignore their standard rantings.
2. Step in when it is necessary to correct a misstatement that could
potentially mislead a novice poster.

While it is true that novice posters don't know a great deal about Access,
it is also true that most of them are smart enough to figure out quite
quickly who the trolls are.

 

[a a r o n _ k e m p f]

is it a personality flaw to STAND UP AGAINST A CRAP DATABASE THAT
CORRUPTS DATA AND DOES NOT SUPPORT CHANGING CONDITIONS?

Is it _MY_ fault that jet doesn't work well over Wan, VPN, Wireless,
etc?

Is it my fault?

Jet is stupid as is anyone that uses it.

 

[Fred]

Yeah, all of the people using the world's most popular database software are
stupid and you are smart

 

[So Sorry For Poor Aaron]

is it a personality flaw to STAND UP AGAINST
A CRAP DATABASE THAT CORRUPTS DATA
AND DOES NOT SUPPORT CHANGING CONDITIONS?

Your personality flaw is that you harrass posters asking and answering
legitimate questions with accurate answers by LYING about a perfectly good
database engine and about the recommended configuration of database for
accessing server DBs. It is compounded by additional personality flaws that
lead you to believe that by bluff and bluster, and insulting your betters,
you can win an argument.

Is it _MY_ fault that jet doesn't work well over
Wan, VPN, Wireless, etc?

It is your fault that you try to convince users of your erronoeous
assumption that Jet was intended to work in those environments, and that
every problem that anyone has is because of such environments. That is, you
LIE about the source of the problems, and you propose (only, ever) a solution
(the same solution, no other) that is, in most cases, at best impractical and
at worst unfeasible.

Is it my fault?

It is your fault that you won't recognize that you are consistently wrong
and consistently disruptive and just shut your ignorant yap. Then again, your
ancestors probably passed on a weak character gene that's not easy to
overcome.

Jet is stupid as is anyone that uses it.

The International Standard for Stupid is "aaron kempf". The International
Standard for Stupid for database advice is, "Never mind the circumstances,
environment, and requirements, just propose the same database, regardless."
The International Standard for Stupid for Database Appllication Front-Ends is
"stick with the front end whose great virtue was it was hyped by marketing
weasels" even when, as now, it is no longer recommended by people who really
know. You hit all three of these, aaron, you antonym of intelligence.

"Now and then, there's a fool such as aaron is over ADP," to adapt from a
song. But, we hasten to add, there aren't many fools in aaron's class -- he's
a World Class Fool.

But, hey, sweetcheeks*, don't lose hope -- Big Bruce, Big Bubba, and Big
Barney just can't wait to see you again on the backside of the cellblock.
"Orange is aaron's color," they say.

* you know which cheeks those big boys mean

Keep up your rantin' and ravin', and sooner or later, you'll violate the
terms of your probation and the big boys will have an opportunity to be
overjoyed.

So Sorry For Poor, Ignorant, Conceited Aaron-The-Stretched-Orifice

 

[a a r o n _ k e m p f]

lying about what?

why would I lie-- half of the postings in this group involve people
that
a) don't understand the benefit of linking (because it's not a logical
arrangement- queries should be in the backend also)
b) database corruption

so now why in the heck would I have to lie about anything?

Jet just isn't reliable enough for anything (and because SQL Server
development is _EASIER_) it makes no sense to have a more expensive
crappy database like JET.

It's just a lot more practical to 'right-size' _ALL_ your databases.
SQL Server is the worlds most popular database.. as demonstrated at
www.microsoft.com/sql

-Aaron

 

[a a r o n _ k e m p f]

jet isn't the worlds most popular database.

sorry.

but I hope you're not counting exchange are you? that is a better
flavor of Jet-- one that doesnt' corrupt.

SQL Server has assimilated the whole world because it is definitely
faster and easier for anything you need.

 

[a a r o n _ k e m p f]

disruptive?

am I the dipshit that writes letters to people's bosses-- just because
they disagree?
am I the dipshit that writes letters to people's bosses-- just because
they disagree?
am I the dipshit that writes letters to people's bosses-- just because
they disagree?
am I the dipshit that writes letters to people's bosses-- just because
they disagree?


how dare you call me disruptive.

the disruptive technology really came about a decade ago.. it's called
'MSDE' when SQL Server became FREE that jet became irrelevent.

 

[Bill Mosca]

I guess you all didn't see the "Please Don't Feed The Trolls" sign. <grin>

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals