You can use sql pass through queries. But using MS access wizards is a bit of a no no try using ADO to access the database directly and youll have full control over how the data is transfered and manipulated
I am working on the same thing and have found DTS to be very effective. To transfer the table structures and data in SQL, first create a new database. Then, use DTS to transfer the table structure and data simultaneously. Go into your enterprise manager and expand your new db. Expand tables and click on tools - DTS should be your first option. The wizzard will walk you through the rest.
Another option is to use an upsizing wizzard through Access. You will need to download this option from MS's website.