My group is discussing a migration from Access 97 to SQL Server. Both the front-end application and back-end db is in Access 97. Our database is growing in size, now exceeding 20,000 records in some tables and potentially we'll end up with about 50,000 records. We've also experienced quite a few db corruptions and other various strange problems. This makes us just not trust that our data is safe in the Access db.

We want to keep the Access front-end and make as few modifications to it as possible, since it's quite complicated and large. There are also time and budget limitations that hider us from re-writing the front-end thouroghly. Our first thought is to stick to DAO and replace the current links to the Access db with ODBC links to a SQL Server db.

My main concerns are performance (since we'd not use SQL in an optimal way, staying with DAO) and stability (would our data really be more safe with such a solution?). If anybody has some experience of doing this or similair, any input would be very appreciated!