>I have inherited an Access based program which is a mess. The primary database contains links to at least 7 other mdb files using direct paths i.e. n:\ucs\data\*.mdb. There are over 400 different tables between all of the db's; as well as forms, queries, reports, and macros that number in the multiple hundreds as well. The program is used heavily by around 75 people a day, 25 at any given time. The database in general has a very poor design and has been a work in progress which has been patched and added to more times than worlds largest quilt. Needless to say it is becoming more and more unreliable every day.
>
>The decision has been made to upgrade the Access database to use SQL Server. I believe that a new database needs to be created and the old data imported in so as to not lose any data while improving performance. I also believe that the program itself should be rewritten using VFP as a front-end. I realize this would be a big job but I think it is the right way to do it. The powers that be think that we should upgrade one table or section at a time and continue to use Access databases for the front-end and simply link to the SQL server.
>
>Correct me if I'm wrong but client-server programming is a different paradigm than that of fileserver programming which requires a completely different kind of thinking. What I need is arguments either way. This program will probably be distributed to other locations so I am also looking for portability in this product.
>
>Your ideas will be very much appreciated.
>Kris H.

You can't just "link" to SQL Server (in the Access sense of the word), the queries need to be changed to access SQL Server through a connection. Either DAO or ADO will work, but ADO is the MS recommended technology. And you're right, switching to SQL Server takes you to a client/server scenario in which the best approach is to bring only the required sub-set of the data. That means rewriting any forms with the perennial First/Next/Previous/Last data control buttons.

Furthermore, most probably you need to look at all the MDB's and their tables to design the proper single SQL Server database. If you go one by one as suggested by the client you will end up with the same mess as the MDB's. Even if you don't implement the whole database at once, I would recommend you analyze the way the linked MDB's work together to come up with an complete and efficient data model.

Just my .02