MSSQL Database Management using Microsoft Access
- Start Microsoft Access
- Create a new empty database
- Go to File ⇒ Get External Data ⇒ Link Tables
- Change the Files of type drop down to ODBC(). (try Exchange() when you want to try something fun)
- Select the Machine data source tab, then click New
- Select User Data Source then click next
- Select SQL Server from the bottom of the list of databases, and click next
- Click Finish
- Enter a text name and description for your connection
- Enter the SQL server name, and click Next
- The default should be to use Windows NT authentication, and click Next
- 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
- Click Finish
- Click OK on the test connection dialog
- Make sure your new data source is selected, and click OK in the Select Data Source
- Select the tables within the database instance that you want to work with in MS-Access, and click OK
- 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.
