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:
- Right-click on your database, and choose Tasks –> Import Data..
- Choose “Microsoft Access (Microsoft Access Database Engine)” from the Data source dropdown menu in the SQL Server Import and Export Wizard.
- Find your database using the Browse button unless you really just like typing things in. Then hit “Next.”
- In the “Destination” field, choose “SQL Server Native Client 11.0.” Choose your Server Name, and fill in your Database name, and hit “Next.”
- 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.”
- Click on the box(es) for the table(s) you want. Then click “Edit Mappings.”
- 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.
- You can preview the data by clicking “Preview”
- I didn’t change anything on this page. Hit “Next.”
- Hit “Next” on this page too.
- If everything is kosher, the file should upload correctly.