Linking an Access Table to a new SQL Data Source

Linking an Access Table to a new SQL Data Source

Open Windows Run and paste C:\Windows\SysWOW64\odbcad32.exe
Then click System DSN tab and click add.

Step 1:
Select the SQL Server Native Client 11.0 driver and click OK.

Step 2:
Enter a Name and Description for the Connection.
Enter the address you Remote Connect to your server, click next.

Step 3:
Enter your login credentials you connect to your SQL Server with, click next.

Step 4:
Change the default database to your required database, click next.

Step 5:
Change your logging if required, click next.

Step 6:
Go to Microsoft Access and click on the External Data tab followed by the Linked Table Manager button.

Step 7:
Find the table you wish to change e.g(dbo_testTable (DSN=oldTable;DATABASE=oldTable).
Click the checkbox for Always prompt for new location and click OK.

Step 8:
Click on the Machine Data Source Tab, click on the new connection we made and click OK. This should now update the database for this table.