I think the best advice - although unhelpful - here is to offer a bit of hindsight: don't use Access. It's not an appropriate tool for serving data to a web application. It's a desktop application, like Word and Excel are.
So, if poss, migrate your data to a proper DB platform (even CF's built-in Derby is a better option). MySQL and SQL Server Express are also both free, easy to use, and are "proper" databases. The migration path to SQL Server Express would probably be the easiest, as I imagine it had an import routine for Access.
I had a look at you specific problem and tried to concoct a solution for you. However I have probably reached the same point you have: one cannot change an existing column to an autonumber; one cannot update the values in an autonumber field.
So as far as I can tell you are sunk as far as a quick fix goes.
My course of action would be:
1) migrate off Access.
2) if I couldn't do that:
* rename the column which used to be the autonumber to something else.
* create a new autonumber column with the original name. This'll automatically populate with new IDs
* update the foreign key values in any table with a foreign key to this table with the new value
* realise you're going to be faced with having to do this sort of thing because Access is rubbish. Consider migrating.
HTH.
--
Adam
1
Reply
AdChoices