Geez, that's a big leap there. I am not the best person to answer this because I started on Access 2003 (though I used 2000 file format for long while since it was the default) and I know that Access 97 is on Jet 3.5 whereas 2000-2003 are Jet 4.0 while 2007 is ACE 12.0 and 2010 is ACE 14.0. In theory, DAO hasn't changed much and you shouldn't see your code breaking since the changes were mainly enhancements. But once again, I'm not the most qualified person to answer that question.
At least, I can provide some reasons to go to 2010 directly (even if it means breaking some stuff):
1) Ability to use data macros (think of it as triggers attached directly to the table. Instead of doing validation in form's BeforeUpdate event, you can validate in your table's BeforeChange event and be assured this will always runs no matter what form your user use.
2) Trusted Documents makes everything sane again WRT trusting the VBA & unsafe actions. No more wrangling with the annoying security dialogs or tinkering with macro security / digital signatures / registry. Click Yes once and it always run. Done.
3) Shared Images gallery means you can have a richer form without the bloat by storing the image once and reference the same copy in several place all over your objects.
4) Native Webbrowser control. On the surface, it sounds meh but when you consider that you can now use it to manipulate some of web services, it suddenly get much more interesting.
5) Navigation Control which is basically a tab control + subform control rolled into a single control.
6) Ability to easily customize Ribbon. That was sorely missed in 2007 and though I am not a big fan of Ribbon the ability to easily customize Ribbon just as we could with menus in 2003 & prior makes it much simpler and you can export the XML, which makes customization quicker to build and re-use.
There may be few more I missed but I think that's the most interesting features. 2010 also introduces ability to create web database but I don't feel it's sufficiently mature and requires too much (you have to have most expensive version of SharePoint to do any kind of web publishing). Now, to be fair, here's some downers:
1) Ribbon. 2007 was awful. 2010 has made it bearable and though Microsoft claims it has improved productivity, I'm not really convinced it is in fact better than menus. At best, it's just a lateral move, swapping one UI for other UI without major gain overall. At worst, it's slow & takes too much space.
2) 2007 File Format. Microsoft decided that instead of creating a new file format they'd continue to use same 2007 file format but disable any objects that uses 2010 features. In theory, it'd allow you to continue to use a file created in 2010 in 2007 as long you didn't use any 2010-only feature. The trouble, however, was that too many users were finding that it wouldn't be usable in 2007 and that there were 2010 stuff added anyway even if they tried their best to not do this. This makes it very frustrating to develop for 2007 if you're using 2010 so for that reason I tend to think that if you have to develop for 2007, use 2007 only. Do not try to do it from 2010. Assume any file touched by 2010 is corrupted and thus 2010-only. Note, this analysis doesn't apply to 2003 & prior formats so it's not that a big of deal if we're talking 2003 / 2010. (Note: 2010 should be able to read 97 file format but you can't do anything much with it and would have to convert it to 2003 to get full editing functionality).
3) No User-level security with ACCDB file format. You can still have ULS if you continue to use MDB file format but of course you can't have any 2010 features. Many people were understandably upset over this major omission even though most acknowledged that ULS was utterly weak; it did serve an useful permission as a corraling system that did operate at engine level. There's alternatives, at least such as Tom van Stiphout's example of using Active Directory at accesssecurityblog.com.
4) No replication for ACCDB file format. However, the substitution is that you would have replication with SharePoint (note that in this specific case, you need not have the expensive edition; the free edition that comes with any Windows server can satisfy this requirement) and in a way it's better than the old Jet replication because it requires no client setup.
5) Multi-Valued fields and Attachment fields. They were added primarily to be compatible with SharePoint lists, but are totally not normalized and not developer-friendly. Avoid.
I hope this helps illustrate.
