Access migration from 2000 to 2007

[mls via AccessMonster.com]

Can some one give me the list of things I need to keep in mind while
upgrading my database from 2000 to 2007. I have lot of tables, macros, code
modules, reports in my database, (one database for each year from 2005 to
2009). What are the areas that I need to more cautious on.

 

[mls via AccessMonster.com]

I mean where should I start from? open exisitng 2000 databse in ACCESS 2007
and fix as I found issues?

 

[KARL DEWEY]

one database for each year from 2005 to 2009
Do you mean databases or tables in a database?

If you have a database or a table per year you have set up the database wrong.

 

[mls via AccessMonster.com]

Just curious. How should the historical data stored? In a single database
with different tables per year? or multiple databases one per each year. We
will have at the maximum 3- 5 table per each year.
Tables like, demographic data, test details, test results.

What is the best data model for this type of situation.
Thanks

 

[Douglas J. Steele]

Historical data should be stored in a single table in a single database.

 

[mls via AccessMonster.com]

Then we will have performance issues when querying\retrieving the data from
single big table

Historical data should be stored in a single table in a single database.

Just curious. How should the historical data stored? In a single database
with different tables per year? or multiple databases one per each year.

[quoted text clipped - 22 lines]

 

[Douglas J. Steele]

Why do you think that?

With proper indexes, it might actually be faster!

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Then we will have performance issues when querying\retrieving the data
from
single big table

Historical data should be stored in a single table in a single database.

Just curious. How should the historical data stored? In a single
database
with different tables per year? or multiple databases one per each year.

[quoted text clipped - 22 lines]

to
2009). What are the areas that I need to more cautious on.

 

[Paul Shapiro]

In addition to Douglas' answer, as soon as you get requests for things like
statistics by year, or for the last x years, you will have an ugly mess of
sql with separate tables. An Access table with hundreds of thousands of rows
can be very fast with proper indexes, as Douglas said. If the only
difference in the data is the year, I would add a statusYear column and
store the data in a single table. If your form and/or report record source
is something like 'Select * From MyTable', you might want to change that to
let the user specify the year and then load the data for the selected year.
That would be equivalent to your separate tables, but much easier to manage.

If your data size grows too fast for a single Access db (unlikely I would
guess), you can setup an archive database where you keep everything more
than 5 or 10 years old, or however old it takes to be no longer of current
interest.

Why do you think that?

With proper indexes, it might actually be faster!

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Then we will have performance issues when querying\retrieving the data
from
single big table

Historical data should be stored in a single table in a single database.

Just curious. How should the historical data stored? In a single
database
with different tables per year? or multiple databases one per each
year.
[quoted text clipped - 22 lines]
to
2009). What are the areas that I need to more cautious on.

 

[mls via AccessMonster.com]

This is a very good suggestion. Thank you. Security wise I am not comfortable
to store all the data in one big table/ database. I believe in Access there
is no table level privilages; who ever has access to the database can
retrieve information. I am just thinking of moving to SQL server in near
future so for now I will concentrate on migrating all my previous data to
Access 2007 ( mdb only). Not even ACCDB. So far I don't have any problems
with tables, queries.

I am guessing I will have some issues with 2007 FORMS, MDI Vs SDI. Any
thoughts?

Thank you

In addition to Douglas' answer, as soon as you get requests for things like
statistics by year, or for the last x years, you will have an ugly mess of
sql with separate tables. An Access table with hundreds of thousands of rows
can be very fast with proper indexes, as Douglas said. If the only
difference in the data is the year, I would add a statusYear column and
store the data in a single table. If your form and/or report record source
is something like 'Select * From MyTable', you might want to change that to
let the user specify the year and then load the data for the selected year.
That would be equivalent to your separate tables, but much easier to manage.

If your data size grows too fast for a single Access db (unlikely I would
guess), you can setup an archive database where you keep everything more
than 5 or 10 years old, or however old it takes to be no longer of current
interest.

Why do you think that?

[quoted text clipped - 12 lines]