Import a table from Microsoft Access into SQL Server

Preliminary: Save your Access Database in the 2002-2003 file format. That means it must be a .mdb.

These are the steps for using the wizard to import the table:

  1. Right-click on your database, and choose Tasks –> Import Data..20160621 Import Step1 Tasks
  2. Choose “Microsoft Access (Microsoft Access Database Engine)” from the Data source dropdown menu in the SQL Server Import and Export Wizard.20160621 Import Step2 ImportExportWizard1
  3. Find your database using the Browse button unless you really just like typing things in. Then hit “Next.”
    20160621 Import Step3 ImportExportWizard2
  4. In the “Destination” field, choose “SQL Server Native Client 11.0.” Choose your Server Name, and fill in your Database name, and hit “Next.”
    20160621 Import Step4 ImportExportWizard3
  5. On the next page, choose “Copy data from one or more tables or views.” Unless you’re more fancy than me and want to try that second option. Then click “Next.”
    20160621 Import Step5 ImportExportWizard4
  6. Click on the box(es) for the table(s) you want. Then click “Edit Mappings.”
    20160621 Import Step6 ImportExportWizard5
  7. The default for these values was nvarchar which is a Unicode datatype, and not compatible with the data I hope to connect it to. Changed values to varchar. Click “OK” to go back to the previous window. You can also edit SQL if you’d like.
    20160621 Import Step7 ImportExportWizard6
  8. You can preview the data by clicking “Preview”
    20160621 Import Step8 ImportExportWizard7
  9. I didn’t change anything on this page. Hit “Next.”
    20160621 Import Step9 ImportExportWizard8
  10. Hit “Next” on this page too.
    20160621 Import Step10 ImportExportWizard9
  11. If everything is kosher, the file should upload correctly.
    20160621 Import Step11 ImportExportWizard10
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s