Any advantage to me shifting from Access 2000 to Access 2007

[ZZX]

I currently have an application that I developed about 4 years ago using
Access 2000/VB6 on Windows 98. I now use Windows XP.
I am a beginning programmer.
I have a need to Modify the application to (I think) use subdatasheets.
The application previously used simple linked (2 dimensional) tables.
I have not used 'subdatasheets' before and will need to learn and
understand whatever it is that I end up using.

Of the 6 tables currently being used; I now have a need to link one of
the tables containing 20+ entries (years)to 100+ entries (unit
locations) each containing about 12 variables.
I want to maintain these tables via a single form.

Suggestions & recommendations appreciated.

Bob

 

[John W. Vinson]

I currently have an application that I developed about 4 years ago using
Access 2000/VB6 on Windows 98. I now use Windows XP.
I am a beginning programmer.
I have a need to Modify the application to (I think) use subdatasheets.
The application previously used simple linked (2 dimensional) tables.
I have not used 'subdatasheets' before and will need to learn and
understand whatever it is that I end up using.

Of the 6 tables currently being used; I now have a need to link one of
the tables containing 20+ entries (years)to 100+ entries (unit
locations) each containing about 12 variables.
I want to maintain these tables via a single form.

Suggestions & recommendations appreciated.

Bob

Subdatasheets are available in 2002 and newer versions... but a) most serious
Access developers dislike them intensely and b) they are not necessary to do
what you propose. Access2007 is a MAJOR change in user interface; the new
"ribbon" interface is very different from the toolbars and menus familiar from
earlier versions.

I suspect you can and should do what you're describing using a Form with a
Subform; this can be done in any version of Access, and it is not necessary to
have subdatasheets enabled in order to do so.

John W. Vinson [MVP]

 

[ZZX]

I very much appreciate your response! You have answered questions for
me in the past (on my initial development several years back) and I
respect your opinion. Aside from my potentially using Subforms to
accomplish what I want to do ...

You mentioned a MAJOR change in the user interface ...
The current application is being utilized by a secretary that might
appreciate a different interface? The current design uses a Main form
to tie everything together ... a lot of reports and forms to update the
database files, etc.

I guess I would like to know:
1) If it would be a major redesign or effort for me to switch to Access
2007.
2) Would the current, rather complex & nested queries or the 12 or so
reports be a issue?
3) Would creating and using Subforms (or the equivalent) be easier with
2007.
4) The application will likely be around for another 5-10 years. Will
it still run under Access 2007 if they want to upgrade to newer software?

Thanks,
Bob

 

[ZZX]

I was looking into subforms ...
It does not appear that subforms will do all that I need???
The application now uses 11 queries; some of which have rather involved
calculations (IIF's, etc.) The queries also need to access the data in
the same way in order to calculate and print the 16 +/- reports.

I'll try to be more specific in what I want to do:
The current application contains 5 tables of data tied with various
relationships.
One of the table (called 'RETEN') that contains 26 rows and 9 columns.
The first column is key (indexed) and contains Years (1995-2020).
The remaining 8 columns contains various data associated with that year.
Up until now, the same data for a particular year was used for each of
100+ locations (called 'HMName')
There is now a need to have each year contain different data in 2 of the
8 columns of data for the 100+ HmName's.
What used to work with a 2-dimensional table is no longer the case.

I am looking to see what my options are? Since Access 2000 will be
rather old in a few more years; I was looking at changing to Access 2007
and maybe making the table issue somewhat simpler?

Thanks for your time and expert advice! ... much appreciated!
Bob

 

[Allen Browne]

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

Replies to your specific quesitons in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

1) If it would be a major redesign or effort for me to switch to
Access 2007.

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

2) Would the current, rather complex & nested queries or the 12
or so reports be a issue?

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

3) Would creating and using Subforms (or the equivalent) be easier
with 2007.

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

4) The application will likely be around for another 5-10 years. Will it
still run under Access 2007 if they want to upgrade to newer software?

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.

 

[John W. Vinson]

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.

I very much appreciate your response! You have answered questions for
me in the past (on my initial development several years back) and I
respect your opinion. Aside from my potentially using Subforms to
accomplish what I want to do ...

You mentioned a MAJOR change in the user interface ...
The current application is being utilized by a secretary that might
appreciate a different interface? The current design uses a Main form
to tie everything together ... a lot of reports and forms to update the
database files, etc.

I guess I would like to know:
1) If it would be a major redesign or effort for me to switch to Access
2007.
2) Would the current, rather complex & nested queries or the 12 or so
reports be a issue?
3) Would creating and using Subforms (or the equivalent) be easier with
2007.
4) The application will likely be around for another 5-10 years. Will
it still run under Access 2007 if they want to upgrade to newer software?

John W. Vinson [MVP]

 

[Allen Browne]

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

 

[ZZX]

Thanks for pointing me in the right direction.
I obviously did not do a very good job of explaining the existing
structure ... however, that being said ...
I believe the existing table structure is 'normalized' ok.
When I came upon this added 'new issue', I seemed to have gotten a bit
off track and confused.
I'm going to study the article on 'Understanding Normalization' and see
if I can remove the confusion.
THANKS!