With the ever-increasing advancements in the internet world, it is imperative to know the process to link a Microsoft Access database to a Microsoft SQL server database. As the two databases are very important for your website’s performance, you must make sure that both databases, the MS Access database and the SQL server database, are well connected to each other. As a proper link between the two databases keeps up with the performance of your website, in the end, it is treated as an inevitable technical part of every corporate website.
We have worked out tutorials to teach people how to use WebSitePanel to manage SQL Server database and use SQL Server Management Studio to connect SQL Server and optimize SQL Server. In this tutorial, we will illustrate the process to link a Microsoft Access database to a Microsoft SQL Server database. As the process in quite technical, it should only be executed by the people having sufficient knowledge about the two databases.
Steps of linking a Microsoft Access database to a Microsoft SQL Server database
The first step of the procedure is to make sure that you have an Access database. If you don’t have an existing MS Access database, you must create a fresh one before proceeding to the next step. Once you launch the Access database, you should find the External Data option from the Quick Access toolbar and choose the ODBC Database from the available list. Clicking on the ODBC database opens a new wizard.
In the first screen of this wizard, you will have to set the source, as well as the destination of the Access data. There are two available options in the list, where you need to choose the “Link to the data source by creating a linked table” tab. After selecting this option, you should click on OK to proceed to the next step.
In the new screen, select Data Source and you will see a window appears, and then you need to insert the DSN name. In our example, we have entered SQL Connection in the DSN name section. After filling this field appropriately, you must click on “New” tab.
Hitting on the “New” button opens another window, named “Create New Data Source”. On this screen, you need to guide the cursor to the SQL Server for choosing it, before clicking the Next button.
On the next page, you have to insert a file name and click on the Next option. Now, you should verify the file data source details that involve the filename and driver. In our example, the filename has been set as SQL Connection and the driver is SQL Server. Clicking on “Finish” redirects you to the next screen.
Upon the completion of the last step, a new window named “Create a New Data Source to SQL Server” appears on the screen where you need to insert the database name. You must make sure that the database name is accurately typed in the Description box. In addition to this, you must enter the server’s domain name.
In the next step, you will have to check the “With SQL Server authentication using a login ID and password entered by the user”. You also need to insert the authenticated login id and password before clicking the Next button.
In the next two windows, you do not necessarily need to bring any modifications. Similarly, you can simply click the OK button in the ODBC MS SQL Server setup and the Select Data Source screens. When the SQL Server Login screen appears, you need to insert your login information to access the Link Tables windows. Here, you should choose the desirable tables and click on “OK” for linking to the respective tables.
Now you can select the fields for identifying the records in your database. Clicking the OK button will mark the completion of this process and will link a Microsoft Access database to a Microsoft SQL Server database successfully.
