Tuesday, January 29, 2013

Bit of a laugh on a rainy day

If you have the time, here is a show I would have loved to have been at. The writer sees the funny side of the fancy dress.  Link here

Tuesday, January 15, 2013

Learn how to switch primary keys in Access

When I showed a programming friend about indexes in Access the other day, I realised that this may be a topic that many programmers know very little about. Then after changing about 30 indexes in a database becuase of the Autonumber bug, I thought I had better write up some notes

See all the steps to change indexes in Access here

Friday, January 04, 2013

The AutoNumber "goes crazy" fix

There are posts everywhere about Autonumbers misbehaving in Access 2007 and Access 2010. Most post end up pointing to a solution on Allen Brownes great website

Unfortunately there are problems with that solution (and if I am wrong about that sorry).

1) It doesn't work in Access 2010 but seems to work.
2) It fixes the random number seed but eventually the problem returns.

There is only one solution and its a manual fix.

Open the table in design view. Find the auto-number field.  Make it the primary key. Look at the sort on the primary key. It must be Ascending. 

Do not remove the Primary key from the autonumber key, no matter how you want that table to behave.

What I think is going on

At some stage, someone in Microsoft decided that when a database is compacted, they could look at the very last record in the table and then look at the autonumber field and save that number as the seed.  Then when the next record is added, it looks at the seed, adds one and then updates the seed.  All fine if the table is sorted correctly.  I think the bug crept in because Microsoft templates all have autonumbers as the primary key in each table. Therefore they wouldn't have noticed the bug.  For people who are getting an error like "Autonumber already exists", its because the seed is lower than the highest autonumber in the database and eventually it hits a record that already exists.

I hope I am wrong, this is totally unnecessary bug that should be fixed in the next service pack. It has caused problems in at least 3 of the databases we are maintaining.

We have this bug in Access 2000 databases but we are running them with Access 2010 now.

I do not want anyone to lecture me about primary key design or autonumbers but if you know anything about house paint colours and kitchen design, happy to chat.

Here is an article that shows you how to change (primary key) indexes

Garry Robinson
Access MVP 2006-2013

Wednesday, January 02, 2013

Happy New Year

Hello vb123.com followers

This year promises us a great comet display, and probably for many of us, our first taste of Windows 8 and Office 15. 

Should be interesting

Have a great year

Garry Robinson