Site hosting news, tutorials, tips, How Tos and more

The Missing Link, Creating a Bridge Between Access and SQL

howto

Here’s a short tutorial on how to link your Microsoft Access database to your Microsoft SQL Server database.  (Note: Screenshots from Microsoft Access 2010)

1. First, you will need to create an Access database or open up an existing one.

CreateAccessDatabase

2. Next, you’ll want to click on the External Data tab and then ODBC Database icon which will start a wizard.

ExternalData

3. Select Link to the data source by creating a linked table and click on OK.

ODBCWizardPage1

4. In the Select Data Source window, type in the DSN Name and click on the New button.

SelectDataSource

5. In the Create New Data Source window, select SQL Server and click on the Next button.

CreateNewDataSource

6. In the Create New Data Source window, type in the file name and click on the Next button.

CreateNewDataSource2

7. In the Create New Data Source window, click on the Finish button.

CreateNewDataSourceFinish

8. In the Create a New Data Source to SQL Server window, type in the name of the database for the Description and enter the fully qualified domain name for the Server.

CreateNewDataSourceToSQLServer

9. In the Create a New Data Source to SQL Server window, check With SQL authentication, type in your Login ID and Password and click on the Next button.

CreateNewDataSourceToSQLServer2

10. In the Create a New Data Source to SQL Server window, you can leave the options as is or change them.  Click on the Next button.

CreateNewDataSourceToSQLServer3

11. In the Create a New Data Source to SQL Server window, you can leave the options as is or change them.  Click on the Finish button.

CreateNewDataSourceToSQLServer4

12. In the ODBC Microsoft SQL Server Setup window, click on the OK button.

ODBCMicrosoftSQLServerSetup

13. In the Select Data Source window, click on the OK button.

SelectDataSource

14. In the SQL Server Login window, enter the Login ID and Password.

SQLServerLogin

15. In the Link Tables window, select the table(s) you want to link and click on the OK button.

LinkTables

16. In the Select Unique Record Identifier window, choose the fields that will uniquely identify the record and click on the OK button.

SelectUniqueRecordIdentifier

17. You have now created a linked table in Microsoft Access!

LinkingTableFinished


3 Responses
  • Rei Tu Reply

    Just wanted to add that you can follow the same steps to connect your Access database to a MySQL database, but you need to download and install the ODBC connector from Oracle here:

    http://dev.mysql.com/downloads/connector/odbc/

  • Mike Reply

    How do you do this for several users without having to create ODBC entry for each one?

    • Rei Tu Reply

      Launch the OBDC Data Source Administrator (should be available if you perform a search) and create the connection under System DSN, not User DSN. That should allow access to all the users of a particular Windows machine. I hope that’s what you’re looking for.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.