Export SQL tables to Access db

Migrating Access DB to SQL Server is common for users who want to upgrade their database. So the opposite direction, that is, moving SQL data to Access might not be common needs. However, for whatever reason, if you need to export SQL tables to Access, here are simple steps.

1. Run SQL Server Management Studio (ssms.exe or sqlwb.exe depending on your SQL version)
2. Connect to SQL server
3. Go to the database you want to export (ex: MyDB)
4. Rightclick from the DB and choose Tasks and then Export Data.... menu

5. Click Next on Welcome page / Click Next on your Data Source once your confirm

6. In Choose a Destination page, select [Microsoft Access] in [Destination] combo box if you want to export data to .MDB file. You have to open existing .mdb file. If you don't have one, you need to create one. (For .accdb, goto step 7)

7. If you want to export SQL data to .accdb Access file, select [Microsoft Office 12.0 Access Database Engine OLE DB provider] and click [Properties] button. As said in step 6, you have to have .accdb file created on your disk. The wizard does not create new one for you.

If you click [Properties] button, you will see Data Link Properties dialog, which is common dialog for OLEDB provider data source. Specify your .accdb file path in Data Source textbo and click OK.

8. Next, select [Copy data from one or more tables or views] option in [Specify Table Copy or Query]

9. Select source tables and views

10. Click Next in [Save and Run Package]. Then the wizard will create Access tables and data from SQL tables you just selected.