I'm still using Access 97 with a front end and back ends but I plan to moving everything to Access 2000 and SQL Server 2000 backend. Everyone here has office 97 loaded... My rough plan is to...
Load Access 97 on my machine and convert databases (leaving Access 97 in tact) and look at converting all the DAO to ADO.
Once everything works, load office 2000 on everyone's machine and roll out the 2000 files.
At this point if I haven't already done so convert to an MSDE back end for testing and when the time comes roll into SQL Server.
I agree this is roughly the approach to be taken. I haven't used ACC2000 yet but from what I've read this is the correct approach. Additionaly I have read quite a bit on using Access as a frontend to DB server. The catch is to ensure you optimize otherwise some things may get slower. The trick is to have access use SQL pass through queries to retrieve the data you need or to perform updates. Access's biggest weakness for data is that the client reads all the data (possibly just appropriate indexes and then related records) and then does the calculating. The network is the bottlneck in almost all cases. If access just send the appropriate query and gets just the appropriate results from the server you have improved network performance. You also need less robust clients to handle the processing. In this way the IS dept gets stuck with the tab of having the adequate processing (I got the impression that each department has its own budget and your dept is likely stuck buying its own PC's).
One thing to consider is that your IS staff is right in their statement about SQL server being better. Knowing this, they may be frustrated in being called about Access problems. Consider that they will have more time when they help with fewer Access problems. Also realize that they may know SQL Server and not know Access. They may be able to support SQL Server well (even though it is very low maintainance). If you do what they want, they have ownership of the database and may be more likely to support it. You will still own the data. As the developer you need to also come to an understanding about how changes to the backend will be done. Especially tables. You should have the ability to create views (basically like access queries that can be linked into access as tables). However modifications to tables becomes a lot trickier. Hopefully you will never have to modify a table but this is the real world.
That said, don't forget that you may have some boneheads in your IS dept and no matter what happens you won't get adequate support.
If your lucky, your IS dept has a DB administrator and a network administrator. Sounds like you've been dealing with a bad network administrator and the DB administrator may be o.k (or the network admin doesn't want to deal with DB stuff <Access>).
The organization I last worked for had an excellent network administrator and a moron for DB administrator. I interned there as a programmer and left sooner than later because the DB admin got in the way. In short know the IS dept and who you can count on. In the case above, if I had an SQL server problem, I would research it and get the network admin to fix it.
For better or worse I am the IS dept where I work (jack of all trades and master of none, except Access 97).
I hope the above is good political insight for you. After all, politics is the real problem here (if nothing else the IS manager needs to replace some people).
One thing I think I've learned here though is that enough can't be said for the time you should be taking take up front understanding the process before you even begin to touch your keyboard!