Main Considerations When Moving From MS Access Programming to SQL Server
As you learn more about SQL Server you will discover (to your delight) a number of things you can do in SQL Server at the database level that you previously had to do in Access at the application level. Some examples include:

Triggers: Procedures defined at the table-level to make stuff automatically happen whenever there is an INSERT/UPDATE/DELETE on the table.

Stored Procedures: Somewhat similar to Access macros and those little VBA procedures (Subs) you built in Access to do "useful stuff", except that in SQL Server they are part of the database itself. So, you can write a Stored Procedure (SP) once and use it (almost) anywhere, even across applications that share the same database.

Functions: These are somewhat analogous to the little VBA Functions you wrote in Access to incorporate into queries, except that SQL Server Functions, like SPs, are more tightly bound to the database. Also SQL Server Functions can be Scalar-valued (return a single scalar value) or Table-valued (return a rowset).

Fun SQL tricks: There are lots of SQL features available in SQL Server that are not supported in Access (Jet/ACE) SQL. "Common Table Expressions" (CTEs) and "RANK and PARTITION" are the ones that gave me "'aha' moments" as I was getting started. 

I could go on, but these are the things I remember discovering early on that got me "thinking in SQL Server".

Don't get me wrong, I still think Access is an excellent tool when used appropriately, and should be given serious consideration as a way to 


build (or just mock up) front-end applications against a SQL Server back-end, and
do data cleanup, create ad-hoc queries and reports, and perform similar tasks on your SQL Server data. 


Finally, even though you will be working with SQL Server 2005 I highly recommend that you get a copy of SQL Server 2008 (or 2012) Express for learning, mainly because of the IDE enhancements in SQL Server Management Studio (namely, auto-completion and interactive debugging). Just be aware of any newer (2008+) features you may encounter and don't rely on them for your production code. (Or, use them as bullet points for your pitch to upgrade from SQL Server 2005....)