Conversion from Access 2000 to Access 2007

[Tigerchat]

I am currently using Access 2000. Some of my databases are so big that they
are running very slowly, even though I am compacting them regularly. Will
switching to Access 2007 help with the problem? How significant will the
improvement be?

How easy is it to go from Access 2000 to Access 2007?

 

[Douglas J. Steele]

There's no difference in the maximum size of a database in Access 2000 and
Access 2007 (it's 2 Gb), so it's unlikely you'll see any difference in
performance.

How big is your database? Have you set appropriate indexes on the tables?

 

[Allen Browne]

Doug's right. In general, each new version adds new functionality, but
things take longer to load and execute as the software gets bigger and
bigger. I seriously doubt A2007 will give you better performance than A2000
SP3.

You may want to upgrade for other reasons.
More info about what you need to adapt to:
http://allenbrowne.com/Access2007.html

 

[tigerchat]

I am working with a series of inter-connected Access databases, the biggest
of which is approaching 1.5 Gb.

I don't think they have been indexed. How can I tell? If they haven't been
indexed, is it easy to index them? How much improvement in runtime can I
expect?

 

[EagleOne]

Allen,

Just caught this thread 6/19/2008.

Thanks for the link http://allenbrowne.com/Access2007.html.
It is about the best I have seen!

EagleOne

 

[Douglas J. Steele]

One way is to go into the databases that actually contain the tables, open
each tables in Design view and look to see whether there are indexes (it's a
choice on the View menu).

Another is to choose the Database Documenter. Afraid I don't have Access
2000 on this machine, and I can't remember where you find it in that
version. See if if't under Tools | Analyze | Documenter. Select all the
tables, click on Options and select that you want the names and fields for
the indexes.

That's the easy part. The hard part is making sure you've got the correct
indexes. For that, you need to look at your queries and see what fields
they're using in WHERE clauses and in the ON clauses of the joins.

Afraid I can't give you any formula for how much improvement you'll get.

 

[Allen Browne]

Thanks for the link http://allenbrowne.com/Access2007.html.
It is about the best I have seen!

Feedback appreciated.

 

[David W. Fenton]

I am working with a series of inter-connected Access databases,
the biggest of which is approaching 1.5 Gb.

I don't think they have been indexed.

Doug explained how to tell, but if they aren't indexed, likely
you'll get *very* close to the 2GB limit (assuming you mean 1.5GBs
in one MDB). It's time to consider upsizing to a server back end
when you have that much data (I'd say it's time when you exceed 1GB,
actually).