Update Autonumber field after DELETE query

If you only need the ordinal field for reports/display would a query with a calculated field be a possible solution?

I haven't got a copy of Access to hand, but the T-SQL for this approach would be as shown below. Where I've created a view you'd create your Access query and use that instead of the table as the basis for reports/display. With luck the conversion of the T-SQL view to Access query shouldn't be impossible.

create table a
(
  -- T-SQL equivalent of autonumber
  pk int identity (1,1),
  data varchar(20)
)

create view ordinalA as
select
  -- Our calculated index field
  count(prev.pk) + 1 as [index],
  main.pk, 
  main.data
from a as main
left join a as prev on prev.pk < main.pk
group by main.pk, main.data 
go

Some data to play around with.

insert into a (data) values ('alpha')
insert into a (data) values ('beta')
insert into a (data) values ('gamma')
insert into a (data) values ('delta')
insert into a (data) values ('epsilon')
insert into a (data) values ('zeta')

Are the table and view/query in agreement?

select * from a
select * from ordinala

pk          data
----------- --------------------
1           alpha
2           beta
3           gamma
4           delta
5           epsilon
6           zeta

index       pk          data
----------- ----------- --------------------
1           1           alpha
2           2           beta
4           4           delta
5           5           epsilon
3           3           gamma
6           6           zeta

Now throw away a row.

delete a where pk = 3

select * from a

pk          data
----------- --------------------
1           alpha
2           beta
4           delta
5           epsilon
6           zeta

And with luck the view/query should be correctly indexed.

select * from ordinala

index       pk          data
----------- ----------- --------------------
1           1           alpha
2           2           beta
3           4           delta
4           5           epsilon
5           6           zeta