Access 2007 autonumber oddity

[Elizabeth Swoope]

I'm recreating a tiny database in Access 2007 that I've used in Access 2003
(class assignment). I'm perfectly happy letting Access create an autonumber
ID field, but I'm accustomed to the numbering starting with 1 and increasing
by 1 with each record.

In Access 2007, the ID starts at 1 but increments by 1 with each new field!
When I'm done entering the first records (through Field6), the autonumber ID
has cycled through 1, 2, 3, etc. and it's 6 when I begin entering the second
record.

This is a small database that students will create to begin learning the
fundamentals of data management. I understand that in a production database,
you wouldn't want to use an autonumber field but that's way beyond the scope
of this introduction.

Any ideas why autonumber isn't 1 for the first record? Any way to fix is
without writing code?

Thanks,

liz

(who thinks her students will be much happier using Access 2007 than they
have been with 2003)

 

[boblarson]

Elizabeth:

An autonumber ONLY guarantees you a UNIQUE number. It does not guarantee
you sequential numbers, which it would kind of do before but even then,
depending on circumstances it would not necessarily be incremental.

If you want an incremental number you need to build your own by using a DMax
lookup and adding one. If you do this be sure to assign it at the last
possible moment before saving the record otherwise you could end up with gaps
like an autonumber when you start a record and then cancel it.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[KARL DEWEY]

When I'm done entering the first records (through Field6), the autonumber ID
has cycled through 1, 2, 3, etc. and it's 6 when I begin entering the second
record.
How are you entering data, in a form or directly into the table?
If entering into a form do you have any code assoicated with it?
Did you look directly at the table to see how many records there are?
Do all fields of the records contain data?

 

[Elizabeth Swoope]

Bob,

The students mostly don't have a clue about databases and this is the first
thing they'll do so I'll just have to be content with whatever number Access
generates. I'll just note that in the instructions.

Thanks for your prompt response.

liz

 

[Elizabeth Swoope]

Karl,

This is entering data directly into the table, no forms involved. I know
exactly how many records are in the table because they're all visible.

I'll just tell the students that the numbers may or may not make any sense
and let it go at that. It's just a "throwaway" database so they can get a
feel for entering a few records, naming fields, etc.

Thanks for your prompt response.

liz

 

[Tony Toews [MVP]]

This is a small database that students will create to begin learning the
fundamentals of data management. I understand that in a production database,
you wouldn't want to use an autonumber field but that's way beyond the scope
of this introduction.

Whoa on a sec here. *ALL* my production databases use autonumber
fields for their primary keys. Even if I was doing a lot of work in
SQL Server that would still be the same thing. Now, where
appropriate I have non duplicate indexes covering one or more fields.

Any ideas why autonumber isn't 1 for the first record? Any way to fix is
without writing code?

If you have entered records in the table and deleted them then the
autonumbers don't reset themselves. But what usually works is to
delete all the records and then compact and repair the database. That
should reset the seed back to 1.

(who thinks her students will be much happier using Access 2007 than they
have been with 2003)

Interesting comment. And I'd agree with you.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Elizabeth Swoope]

Tony,

I was trying to head off the "you shouldn't use autonumber fields as keys"
that seems to be a popular topic of discussion here... (A fact I discovered
when I searched to see if there was a solution for my problem.)

I started with a brand new database, brand new table. Started typing the
first record and the ID field (autonumber) is 1, until I move to the second
field (doesn't matter whether I use Tab or Enter, tried both) and then the ID
field changes to 2. The autonumber field increments by 1 for EACH FIELD in
the first record. After entering the data for Field6 and moving to the second
record, the autonumber ID for the first record is 6. Then the numbers
increment by 1 for each record (not for each field) as I'd expect them to. I
just don't understand why the autonumber increments for each new field on
that first record.

I really don't want to have my students deleting all the records and doing
all that stuff. What I'd do in the real world for a production database is
far different from what I'll teach in a beginning computer literacy class.
(For instance, I know that the lookup wizard is considered a "no no", but I'm
going to teach it since this isn't a programming class, and include the
caveat that you'd typically use a different approach in the real world.)

The Layout view alone is worth the pain of rewriting all my materials for
Office 2007. I think that's going to cut way down on the time my students
spend tweaking the position of controls to get numbers to line up properly
and I think that their frustration levels will be way down. That's just one
thing that I think will help, but it's probably the most significant.

liz

 

[Tony Toews [MVP]]

I was trying to head off the "you shouldn't use autonumber fields as keys"
that seems to be a popular topic of discussion here... (A fact I discovered
when I searched to see if there was a solution for my problem.)

<chuckle> Yes, there are a few people who think that. However, in my
opinion most folks are happy with autonumber keys. However your
point is a good one.

Folks, if anyone wants to discuss this topic yet again please start
another thread.

I started with a brand new database, brand new table. Started typing the
first record and the ID field (autonumber) is 1, until I move to the second
field (doesn't matter whether I use Tab or Enter, tried both) and then the ID
field changes to 2. The autonumber field increments by 1 for EACH FIELD in
the first record. After entering the data for Field6 and moving to the second
record, the autonumber ID for the first record is 6. Then the numbers
increment by 1 for each record (not for each field) as I'd expect them to. I
just don't understand why the autonumber increments for each new field on
that first record.

Ahhhh, I didn't understand what you meant. I didn't pay attention to
those key few words in your posting. " the ID starts at 1 but
increments by 1 with each new field! "

Hmm, you may very well have located a bug. What happens if you give
the field names first and then enter data into the fields.

I can't test this right now as A2007 isn't working for me on my system
right at the moment. And I'm leaving very shortly for steak and wine
with some friends. But I'm sure many others will jump in.

(For instance, I know that the lookup wizard is considered a "no no", but I'm
going to teach it since this isn't a programming class, and include the
caveat that you'd typically use a different approach in the real world.)

So long as folks know what Lookup fields are and how they work I don't
have a problem with them. However most newbies don't really
understand them.

BTW a Microsoft employee has already noticed your posting and is
looking at this problem.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Elizabeth Swoope]

Tony,

I just checked to see what would happen if I put fieldnames in and the same
thing happens. I tried with four and five fields and in each case, the ID for
the first record is the number of fields (including the ID field). That is,
four fields, ID is 4. Five fields, ID is 5.

This is starting a new database in datasheet view and just typing data as
you would in a worksheet. I only have them do this once but it seems a good
basic transition from spreadsheets to databases. Everything else they do,
they start in design view and lay out the structure of the table there before
entering data.

If I start in Design view, autonumber correctly starts at 1. Looks like it's
a bug, but I can just tell my students that and be done with it since they
only use this method once.

Thanks for taking the time to help me troubleshoot this. I was so focused on
getting this part of the lab rewritten (working late tonight, feeling
overwhelmed at what I have to get done before the spring semester starts)
that I didn't take the time to experiment as I should have. I was thinking
that this wasn't anything new and that I'd pop on and find the solution or at
least an explanation.

Again, thanks for your help. This isn't a deal breaker and it's a good
example for the students that sometimes there are little buglets to work
around. Since I'll be moving them immediately to a more structured approach
(and if this hasn't come up before, it's obviously not something that people
do very often), it's not critical to me that the problem be resolved.

Enjoy your evening out!

liz

 

[Guest]

Probably more a 'feature' than a 'bug'. If it's dynamically adding
fields, it has to close and open the recordset. If it doesn't do a
save first, it has to request a new Autonumber. In my (older)
version, I get the same behaviour if I press [ESC] then repaste
while entering data: the autonumber is not rolled back and when
I re-enter, I get the next autonumber.

I don't know why they are dynamically adding fields, but I would
guess that the new-table datasheet view is more attractive now
than it used to be. Am I right? The old version started out with 20
fields named field1 - field20, with no way to enter an auto-number
field.

(david)

 

[Elizabeth Swoope]

David,

A new blank table in datasheet view has an ID column and the next column is
labeled Add New Field. When you move out of that field on the same row, the
column heading becomes Field1 and the new blank column is labeled Add New
Field. That certainly is more attractive than the old way with extra fields
that didn't go away until you closed and reopened the table.

You may be right with the explanation of the behavior, but that's not a
"feature" as far as I'm concerned.

liz

 

[Tony Toews [MVP]]

Enjoy your evening out!

<hic>

Tony <smile>
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Probably more a 'feature' than a 'bug'. If it's dynamically adding
fields, it has to close and open the recordset. If it doesn't do a
save first, it has to request a new Autonumber.

Yes, you're explanation does make sense. And I can just see the folks
at Microsoft going "Oh sh**, we never thought to test for that."
<chuckle>

One of the best attributes a software tester can have for Microsoft is
to be a twisted thinker. While I'm definitely twisted I do *NOT*
think like a new user.

In my (older)
version, I get the same behaviour if I press [ESC] then repaste
while entering data: the autonumber is not rolled back and when
I re-enter, I get the next autonumber.
Interesting.

I don't know why they are dynamically adding fields, but I would
guess that the new-table datasheet view is more attractive now
than it used to be. Am I right? The old version started out with 20
fields named field1 - field20, with no way to enter an auto-number
field.

Correct. Furthermore Access 2007 does it's best to figure out what
field type you meant when you enter the data for that field.

It was quite interesting when I mentioned to MS that you could enter
the month and day without the year and with blanks and Access would
put in the year and the date separator character. Some junior
testers looked at me with astonishment while one of the PMs had a
looooong distance look while he figured out the logic in behind that.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Arvin Meyer [MVP]]

I can confirm this. It definitely appears to be a bug. I just checked and
Microsoft is now aware of it.

I never noticed it before and it shouldn't cause any serious problem because
we shouldn't be adding records in a table anyway (yeah I know, almost
everyone does) Actually, it is rare that my users even realize that there's
any such thing as a table or query. All of my users (except a few power
users) only see forms and reports.

We won't belabor the use of autonumbers as keys (I use them almost
exclusively) but since the actual key is, or shouldn't be, not seen, no one
but the obsessive will probably notice. Again, Microsoft is now aware of the
problem. Thanks for bringing it to their attention.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

David,

A new blank table in datasheet view has an ID column and the next column
is
labeled Add New Field. When you move out of that field on the same row,
the
column heading becomes Field1 and the new blank column is labeled Add New
Field. That certainly is more attractive than the old way with extra
fields
that didn't go away until you closed and reopened the table.

You may be right with the explanation of the behavior, but that's not a
"feature" as far as I'm concerned.

liz

Probably more a 'feature' than a 'bug'. If it's dynamically adding
fields, it has to close and open the recordset. If it doesn't do a
save first, it has to request a new Autonumber. In my (older)
version, I get the same behaviour if I press [ESC] then repaste
while entering data: the autonumber is not rolled back and when
I re-enter, I get the next autonumber.

I don't know why they are dynamically adding fields, but I would
guess that the new-table datasheet view is more attractive now
than it used to be. Am I right? The old version started out with 20
fields named field1 - field20, with no way to enter an auto-number
field.

(david)

 

[Elizabeth Swoope]

Arvin,

I'm teaching a very basic computer literacy class and we spend two weeks
(and two labs) on data management basics, then another couple of weeks on a
final project that includes a review of those basics. I'm doing good to get
them to comprehend creating the structure of a table, the difference between
field names and data, using the * to include all fields on a query, making
sure that reports fit on one page width (IOW, no spillover of dividing lines
or edges of fields), using queries rather than manual sorts and filters, etc.

I have to choose my battles and I'm just scratching the surface. Very few of
my students have ever even seen an Access database, much less used ones. The
handful that have used them have used something that someone else created.

Unfortunately, under these circumstances, there are differences between what
the students do and the "right" way to do it. In order for students to use a
form to enter data, they have to understand that there's an underlying table.

You're right about the key not being seen most of the time, and I'm aware of
keys being meaningless much of the time, so this bug isn't an issue for
practical purposes, but it's a little disconcerting for a beginner.

liz

 

[Elizabeth Swoope]

Tony,

Yes, you're explanation does make sense. And I can just see the folks
at Microsoft going "Oh sh**, we never thought to test for that."
<chuckle>

I was a beta tester for Office but got disgusted with Word and gave up (and
I had too much else to do) so I didn't touch the new version of Access until
it was released. I've beta tested for Borland and WordPerfect and Lotus
(among others) over the years and was usually pretty good at finding things.

One of the best attributes a software tester can have for Microsoft is
to be a twisted thinker. While I'm definitely twisted I do *NOT*
think like a new user.

I don't think like a new user, either, but since I'm writing materials for
new users, I often hit the beginner-level things that most beta testers don't
think about. I wouldn't dream of starting a table in datasheet view, but it
can be done and it's a good transition from spreadsheet to database for the
new user.

Correct. Furthermore Access 2007 does it's best to figure out what
field type you meant when you enter the data for that field.

Access 2003 correctly determined that one of my fields was a Yes/No field
with three records with Yes entries. Access 2007 thinks it's a text field. I
haven't tried it with a mix of Yes and No.

Liz

 

[Arvin Meyer [MVP]]

You're right about the key not being seen most of the time, and I'm aware
of
keys being meaningless much of the time, so this bug isn't an issue for
practical purposes, but it's a little disconcerting for a beginner.

I've been using Access for 15 years and developing professionally for 13 of
those years. It's disconcerting to me as well.

It's one of those bugs that creates a loss of confidence regardless of
whether it's meaningless or not. I'd think that Microsoft will be eager to
fix it.

 

[Elizabeth Swoope]

Arvin,

I've been using Access (dabbling only, with a smidgen of programming) for
less than five years, but have done data management with SAS on the mainframe
and PC, and did some development work in dBase II (under CP/M) and Paradox
for DOS and also used Lotus Approach a bit, among other things. I'm more a
knowledgable dabbler than developer, though, and that's the way I like it.

If this is the only idiosyncracy I turn up, I can live with it because it
doesn't affect the integrity of the system. I'm also relieved that you share
my concern and think that MS will likely fix the problem. I'm always wary of,
"That's not a bug, that's a feature," when it's anything but a feature to me.

I've certainly had to come up with my share of workarounds for quirks and
this doesn't even require that, just a brief explanation that it happens and
that it doesn't matter in the grand scheme of things.

BTW, this is directed to everyone who's been participating in this
discussion. I really appreciate all the input and how several people tested
and confirmed. Thank you all for the time you've spent on this. It's easy to
see why several of you are MVPs. (I tell my students about this community and
the MVPs as resources and how helpful I've found them over the years.)

liz

 

[Sylvain Lafontaine]

It was quite interesting when I mentioned to MS that you could enter

the month and day without the year and with blanks and Access would
put in the year and the date separator character. Some junior
testers looked at me with astonishment while one of the PMs had a
looooong distance look while he figured out the logic in behind that.

It's even more interesting when you enter something that is obviously not a
date and it becomes a date. For example, 100-2 get converted to 2/1/100 and
100 2 (with a blank space between 100 and 2) to 2/1/100. I would have
understood for things like 100/2 but 100-2 and 100 2 ? More funny is that
100,2 got converted to 1002 (I understand the logic here) but 100, 2 (with a
space) again to 2/1/100.

Clearly, too much is like not enough.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Probably more a 'feature' than a 'bug'. If it's dynamically adding
fields, it has to close and open the recordset. If it doesn't do a
save first, it has to request a new Autonumber.

Yes, you're explanation does make sense. And I can just see the folks
at Microsoft going "Oh sh**, we never thought to test for that."
<chuckle>

One of the best attributes a software tester can have for Microsoft is
to be a twisted thinker. While I'm definitely twisted I do *NOT*
think like a new user.

In my (older)
version, I get the same behaviour if I press [ESC] then repaste
while entering data: the autonumber is not rolled back and when
I re-enter, I get the next autonumber.
Interesting.

I don't know why they are dynamically adding fields, but I would
guess that the new-table datasheet view is more attractive now
than it used to be. Am I right? The old version started out with 20
fields named field1 - field20, with no way to enter an auto-number
field.

Correct. Furthermore Access 2007 does it's best to figure out what
field type you meant when you enter the data for that field.

It was quite interesting when I mentioned to MS that you could enter
the month and day without the year and with blanks and Access would
put in the year and the date separator character. Some junior
testers looked at me with astonishment while one of the PMs had a
looooong distance look while he figured out the logic in behind that.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tom Wickerath]

Hi Liz,

I wouldn't dream of starting a table in datasheet view, but it
can be done and it's a good transition from spreadsheet to database for
the new user.

As a former P/T instructor for a beginning Access course (I taught an
evening course at Bellevue Community College, in Bellevue, WA. for three
years), I'm glad to read that you wouldn't dream of creating tables in this
manner. I consider the new "Add a Field" feature in A2007, and the similar
"Create table by entering data" in earlier versions, as the denormalization
wizard! I'm a strong proponent of the advice that database design expert
Michael Hernandez gives in his database design papers: "Always design the
database on paper first". You can check out his two papers here:

http://www.accessmvp.com/JConrad/accessjunkie/DatabaseDesign101

The second paper, Database Design Tips, is only four pages printed. This is
a great primer for new students.

Also, if you find it helpful, you are welcome to share a link to my
"Accesslinks.zip" download. This file includes a Word document that includes
many useful links and other tips. The first three pages include important
information that anyone working with Access should be aware of. This includes
special characters to avoid, naming conventions, database design, reference
related problems, locking database file (*.ldb) info., causes of corruption,
etc. The rest of the document includes a collection of tips/links that I
think are useful, but maybe too much for a beginner to be too concerned with
understanding, until they've had a chance to work with Access for a while.
You can download a copy from here:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip

On the first page, you will see the following link. I would encourage your
students to follow three links within this KB article that show one how to
ensure that they have their operating system, version of Office, and the JET
database engine fully patched:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

This way, they avoid encountering any errors that have already been fixed
with a service pack.

Good Luck come Spring. I know how challenging teaching an Access course can
be (especially when you get some students who don't even know the basics of
computers, such as how to copy a file from a floppy or USB thumb drive to the
hard drive or back!).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________