Upgrading from Access

Question

Upgrading from Access

AlanB

SSC Eights!

Points: 845
More actions
December 7, 2005 at 2:45 am

#76457

Hi
I'm working for a customer who has some business critical databases still in MS Access 7 (aka 95). He's looking for advice on the best upgrade path bearing in mind that this version of Access is no longer supported.
So far I've managed to identify some options, but I'd really like some help from you guys with much more knowledge/experience than me.
Options
1. Leave things as they are (not desirable)
2. Upgrade to latest version of Access
3. Upgrade to SQL Server/Access or SQL Server/Web
4. Upgrade to other db (e.g. Oracle HTML DB)
Also, how do you decide when to upgrade from Access to SQL Server? What are the critical factors?
Any help greatly appreciated.
Alan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jfmccabe SSCrazy Points: 2547 More actions · Answer 1

I would try and do it in easy stages i.e. step 2 and then 3. SQL Server/ Access and then consider SQL Server/Web.

It all depends on the amount of code in the databases, Access 7 as far as I can remember would still be on DAO so you would be doing some work to get it onto ADO. It is possible to simply bring it over (and pray) but a good rework might help you to understand the setup without going too far from what he has got.

If there isn't too much code, then it should convert up to Access 11 fairly simply.

Once you've got it into Access 11, you can look at migrating to SQL Server and then consider web based approaches.

The basic thing about moving up is to look at data volumes, numbers of connected users and the need for security / backups.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

SQL Server/Access would be the cheapest/easiest route. Migrate the data to SQL Server 2005 Express (which is free), and either keep the existing Access front end or migrate to a newer Access. The data should be safer, and you're free to rewrite the application any way you like. If you're feeling lucky you could use MySQL instead, also free. The point is the first step is to get the data out of Access.

After you have SQL/Access working, you can work on duplicating the Access forms in web pages. Both front ends could be used against the database for as long as necessary, then retire Access when the rewrite is finished.

AlanB SSC Eights! Points: 845 More actions · Answer 3

Thanks for the advice, guys.

I think I understand the functionality/security issues now.

Is there a limit as to how big an Access database should be (number of records/tables/Mbs etc.)

Thanks

Alan

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 4

Access 97 has a database limit of 2 gb, Access 2000 and higher 1 gb (not sure, check help)

Is the data already located in another database than the forms/queries/code?

Size doesn't matter that much if there is proper design, proper indexing and well written statements. Access 97 does dare to stop working at 1.8 gb because of deleted elements still taking place (compact).