Data transfer From access to MSSQL server
Solution 1:
You can use Data Import from SSMS.
- Right click on DB
- select Tasks
- Import data..
- Select your Access DB as source
- Set the mappings etc from your source to destination
You can save this as an SSIS package to either re-run at a later date or to develop in BIDS for a more advanced import toolset.
Solution 2:
You can link your SQL Server tables into your Access database - use the External Data menu. Then you can write standard Access append queries to move data between them.