AJD - We feel your pain but we do this quite often in our practice.
First off, yes, you should move up (upsize) to SQL Server 2000. Good decision. Secondly, you need to broaden your understanding a bit in order to take advantage of SQL Server 2000. What you want to do is to "off load" the preparation of data for a form or a report onto the server as opposed to the client.
Creating a VIEW in SQL Server (which you can see if you create an ADP in Access 2000 or 2002) is very much like aquery in Access. The differences is you can't sort, filter, etc. Very rigid but not very flexibly.
You want to move to understanding how to use Stored Procedures in SQL Server 2000. If this is new territory for you I would suggest you mapping out the business rules and then working with an experience SQL Server person to create one stored procedure at a time.
Once created, you can bind a stored procedure to be the RecordSource for a Form or Report in the Access front end. This is very powerful stuff!!
To echo the other respondent, you will have to do some serious thinking of your application and rework accordingly. Just do it methodically and with LOTS of planning and specs.
Good luck.
