Here's an article explaining what's involved in converting to A2007:
   http://allenbrowne.com/Access2007.html

Replies to your specific quesitons in-line.

No. Your existing Access 2000 application will work in Access 2007. You may
not need any modifications at all.

As with any upgrade, if you have done anything non-standard, it may break
when switching versions. Or there may be some specific issues such as:
   http://allenbrowne.com/Access2007.html#Compatibility

As above: if standard, the nested queries will run the same.

It won't be easier or harder to create forms and subforms, unless you were
trying to code things that are automatically given to you in the new
version, such as:
   http://allenbrowne.com/Access2007.html#Good

I don't have a crystal ball for the future, but the last 15 years of Access
attests that Microsoft takes their existing Access users/applications
seriously when designing future versions, and works very hard at ensuring
the new versions run (or can easily convert) the old applications.

I haven't actually installed or used 2007 so I really don't want to bias your
judgement. I'll ask other volunteers who have done so to jump into this
thread.

By all I understand, the differences are more unsettling to database
developers than to end users. If your secretary will tolerate relearning some
things, it may indeed be better. It would NOT be a major redesign effort;
forms and queries and tables still work very much the same, though there's a
fair bit more (sometimes intrusive) security checking.

            John W. Vinson [MVP]

Bob, the crucial issue here is to come up with a normalized data structure.
That will be exactly the same in Access 2007 as it would be in Access 2000
(or any other database for that matter.) Once that's done, the next step
will be to interface it. The Access 2007 interface is somewhat different
than the 2000 one.

Your quick summary of all that you are doing gives a hint at what's
involved, but obviously it's too big to detail here. Nevertheless, the hints
you have given doesn't sound normalized.

For example, your Years column has 2 pieces of data in it: the starting
year, and the ending year. That violates the principle that all data should
be atomic (i.e. only one thing in any field.) At very least, this should be
2 fields, such as StartYear and EndYear.

A further argument could be put that the EndYear would be derived from the
StartYear in the next record, so you don't have overlapping records.

I'm not clear what the other 8 columns are, but if they are different values
associated the year range, it might be better to create a related table with
a field to tell what type of reading this is, and a value field that
contains the reading.

Somehow you are also trying to handle locations for some of those readings,
so the related table might have fields like this:
   YearStart        Number (first year this applies to.)
   ReadingType    whatever type of value this is
   HMName        the location this value applies to. Blank if n/a.
   TheValue        whatever the value is you need to store.

That won't be the end for you, but it is an example of the process you need
to implement to derive a correctly normalized data structure, regardless of
what database product you use to store your data. Normalization is the most
crucial aspect of designing any database, so here's some more resources to
read up on this:
   http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101