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
(
pk int identity (1,1),
data varchar(20)
)
create view ordinalA as
select
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