Access 2007 autonumber oddity

[Guest]

2007-12-2 looks like a date to me, but "evil date guessing"
is actually the main reason I use that format: If I use 2-12
Access is even more likely to get it wrong.

In the ? 12 years ? since "evil date guessing" was introduced,
I've never seen anyone have a kind word for it. Now, for the
first time, an explanation: MS junior and senior staff didn't even
know it existed.

(david)

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/

 

[David W. Fenton]

=?Utf-8?B?RWxpemFiZXRoIFN3b29wZQ==?=

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.

If I were teaching Access, I'd tell them to *never* use the
spreadsheet-type tools, as it will always be frustrating, since
Access is *not* a spreadsheet. Encouraging them to get away form the
spreadsheet concept as soon as possible seems to me to be a good
idea.

 

[David W. Fenton]

"Elizabeth Swoope" <[email protected]>
wrote in message


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.

Chances are that it wasn't caught because nobody at MS or any of
their testers ever create tables in datasheet view.

At least, I'd think that nobody with any sense would do so.

 

[Tony Toews [MVP]]

2007-12-2 looks like a date to me, but "evil date guessing"
is actually the main reason I use that format: If I use 2-12
Access is even more likely to get it wrong.

Sure, but as much of that is caused because you are very likely,
almost certainly, not running a ymd format. The other date formats
are very ambiguous for 12 days out of the month.

In the ? 12 years ? since "evil date guessing" was introduced,
I've never seen anyone have a kind word for it. Now, for the
first time, an explanation: MS junior and senior staff didn't even
know it existed.

FWIW it's the OLEAUT32.DLL which does the date guessing. This is an
operating system dll and not an Access or Office dll.

I reckon I'm the only one with kind words for it then as I quite like
the ability to enter 3 3 and have Access insert 2007-03-03.

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/

 

[Ed]

Just wondering Elizabeth, have you ever reconciled your issue with the
incrementing autonumber in a single record. I had been following all the
threads to your issue, and as the threads progressed, the answers seemed to
diverge from your issue. I also have EXACT problem and I was wondering if the
so called bug you have had ever been resolved and if you would have time to
share what the resolution was?

 

[PvdG42]

Just wondering Elizabeth, have you ever reconciled your issue with the
incrementing autonumber in a single record. I had been following all the
threads to your issue, and as the threads progressed, the answers seemed
to
diverge from your issue. I also have EXACT problem and I was wondering if
the
so called bug you have had ever been resolved and if you would have time
to
share what the resolution was?

I was very curious when I read the original from "liz", so I just spent the
last few minutes trying to replicate the scenario.
As you might expect, I had no luck at all. Access 2007 allows only one
Autonumber field per table, so that cannot be the issue. After creating
several tables with all available datatype fields, I have to ask if there
are additional details that might help others recreate your reported
scenario?

 

[John W. Vinson]

Just wondering Elizabeth, have you ever reconciled your issue with the
incrementing autonumber in a single record. I had been following all the
threads to your issue, and as the threads progressed, the answers seemed to
diverge from your issue. I also have EXACT problem and I was wondering if the
so called bug you have had ever been resolved and if you would have time to
share what the resolution was?

It sounds to me like Elizabeth is somehow forcing a record to be saved after
each field update. This would cause this accelerated incrementing.

What's the structure of the table? You say "the first record*S*" - is each
"field" in fact in a new record?

 

[Dirk Goldgar]

Just wondering Elizabeth, have you ever reconciled your issue with the
incrementing autonumber in a single record. I had been following all the
threads to your issue, and as the threads progressed, the answers seemed
to
diverge from your issue. I also have EXACT problem and I was wondering if
the
so called bug you have had ever been resolved and if you would have time
to
share what the resolution was?

If I recall correctly, this is a bug that occurs when you add fields
dynamically in Access 2007, with the table in datasheet view. Each time you
enter data in the the "Add new column" column, the autonumber field
increases. The answer is not to modify the table design in datasheet view,
but rather switch to design view to add new fields.

Most professional developers would never even see this bug, because it
wouldn't occur to them to modify a table's design by entering data in this
fashion.

 

[Gina Whipp]

And just to add... If you *care* about what the Autonumber is then perhaps
you should consider creating your own Autonumber, ie:
DMax("YourAutonumberField","YourTable")+1

Because Autonumber, while it will be incremental, it may not always be by
one. It's only guarantee is that is will be unique. Oh, and you can't
REuse *lost* numbers.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm