You will want the enterprise tools to allow you to work on SQL Server. Enterprise manager is a good interface to allow you to work with Tables and Views (like Table and Query objects in Access.) I think once you have this interface, the anxiety level will go down a lot. The View designer is a lot like Query Designer in Access.
Query Analyzer will allow you to do ad hoc queries of the data that you can then either package as a View on the server, or run in strings from Access.
Once you have your tables and views set up, you will need to use ADO to connect to them. I would suggest using a disconnected solution (no local tables) but this will require that you build provider statements and take snapshots of the data for your users to work with - unless you think they will be working with dat for long periods of time, in which case you will want linked tables. This will require installing the proper DSN(s) on each users' desktop. Provider statements do not require DSNs and so are much more portable.
has tons of information on these solutions, and is always available, so that is what I use mostly for figuring out the little bits and pieces.
You will need to get familiar with flow control using ADODB.Recordsets. How are you with that?