MENU

Web Database

MSSQL Database Management using Microsoft Access

The general instructions for accessing an SQL server from Access is as follows:
  1. Start Microsoft Access
  2. Create a new empty database
  3. Go to File ⇒ Get External Data ⇒ Link Tables
  4. Change the Files of type drop down to ODBC(). (try Exchange() when you want to try something fun)
  5. Select the Machine data source tab, then click New
  6. Select User Data Source then click next
  7. Select SQL Server from the bottom of the list of databases, and click next
  8. Click Finish
  9. Enter a text name and description for your connection
  10. Enter the SQL server name, and click Next
  11. The default should be to use Windows NT authentication, and click Next
  12. Select the check-box for Change the default database to and select the name of your database instance on the SQL server. Then click Next
  13. Click Finish
  14. Click OK on the test connection dialog
  15. Make sure your new data source is selected, and click OK in the Select Data Source
  16. Select the tables within the database instance that you want to work with in MS-Access, and click OK
  17. Select the primary key fields for any tables that you are prompted for, and hit OK.
    NOTE: If the primary key was set on the server, this shouldn't be asked, otherwise select enough fields that indicate a unique record. Some tables may need multiple fields to differentiate unique records. (sort of like a last name may identify many people, but a first middle and last name more uniquely and accurately identifies most people in a given group)

Once this data is linked, you can close and open Access on the same computer, and the database you created locally will still have the working links to SQL.