Hi Kris,

If the database is in bad shape, then you're right in thinking that you're going to have to fix it first. If the database isn't normalized, then moving it to SQL Server is not going to help, and may make the problem worse or not work at all. SQL Server can help if you are reaching technical limits with Access, but unfortunately there is no quick cure for bad design. You may be able to move sections of tables over at a time, but only as it makes sense logically to do so. You wouldn't want to have one table in SQL and another in Access that were heavily dependent on each other.

You're also correct that client-server programming is a different paradigm, primarily because you want to limit the amount of data that is returned from the server. Where you could simply open a table in Access, trying to do the same with SQL Server will perform a query of all records and then send them over the network. That kind of performance would not be acceptable.

For the size application you are talking about, I would recommend VFP over Access as the front end. For reporting purposes, you may want to keep using Access, since this is one of its strengths and especially if your users like to create custom reports with it. If you go with VFP, definitely check out some of the available frameworks. They will make your life much easier. Some of them also integrate with database design tools like xCase (www.xcase.com), which will help you redesign the database.

It sounds like the "powers that be" want a quick fix, but simply moving the tables to SQL Server is not the answer. Hopefully, they will listen to you. If they won't, import your customer file (or other large file) to SQL, link to it, and show them how long it takes to retrieve all of the records. There are definite trade-offs when moving a file-based application to client-server, and they need to understand that up front.

Hope this helps.