At what point would anyone recommend moving a database onto a format other than Access? I've got a database that books and issues resources and one table now has 80,000 entries and other tables can have easily have about 20,00 each. Although the entire database is only about 50 meg is size.
As you'd expect this database is now suffering quite badly and constantly needs compacting and repairing.
This is an Access 97 database. Are other versions of Access (i.e. XP) any better? I know that as a rule of thumb access will manage up to 1 gig is size but I guess if a database was even half this size it would suffer.
Should I just take the plunge and change to SQL Server? I guess this would make it better?
I would definetely recommend moving to a new database. All newer versions of access use the jet engine version and quite frankly it sucks. Microsoft SQL would be a good choice if you have the money. For some of our facilities we use MySQL because its free and will run on any operating system. The ODBC drivers are also free. If you move to MySQL the current coding you may have will not have to be altered all that much, but if you move to Microsoft SQL you may run into some major changes due to using MDA files. The coding and SQL are quite different for Access Projects.