MS Access including VBA Code with SQL Server back-end

I have a MS Access front end that has VBA code for such things as importing flat files, excel files, etc. Access also includes pass-through queries for data iporting from SQL/Oracle db's. I am running out of space with Access so need to incorporate SQL Server for upsizing.
I have tried the SSMA tool but this only creates a linked table in the Access db which causes the VBA code to fail.
I am trying to find a solution that will enable me to have the functionality that exists in Access (rather than re-developing in another language) and the storage capability in SQL Server, with minimal need to import in SQL or use the Linked Table Manager in Access.
Any Ideas?

You can move tables to sql with Access' upsizing tool, and modify code in Access to use sql tables.

My MS Access is 2002 with a file format of Access 2000, SQL Server db is SQL Server 2008. Access returns a message that the upsizing wizard only works with SQL Server v6.50 SP5 or higher?

Possible to upgrade access first?