Wants to import complete of your SQL server database or part of it? Here is an easiest way to perform this hectic task. When you import database into Access, keep a copy of the database objects and its data. So, that any modification done in access is not reflected in the original SQL Server database. You can update the data and objects and it won't show any impact on the source database.
In order to show all the changes done by you in the source database, you just need to create linked table to SQL server instead. As by doing this you update the data in the source database. However, you won't be able to change the table definition from Access. If you need to do any structural changes then it is to be done on the SQL Server end.
Here is a step by step guide to import a SQL server database into Access 2016.
On the ribbon go to the External Data tab, click ODBC Database in the Import And Link group.
In the open window select Import the source data into a new table in the current database, after then click OK.
Now it's time to select the data source. So you can either create a new data source or provide the details of an existing one.
If you have data source already then skip upto step 12. Otherwise you just need to create data source.
To create data source click new.
Select Sql Server from the list of drivers then click to the Next option.
Assign name to the Data Source you would like to save the connection to after then click Next option.
Take Review of the information and then click Finish.
Provide complete details of our connection to SQL Server. Give a description for your data source, select the SQL Server that you like to connect, after then click Next.
Select Windows NT authentication or SQL Server authentication, and then click to the Next option.
You need to change the default database to actual database that contains the tables/views you want to link to. Also make other changes if required. At last click Next.
Modify settings as needed and click the finish option.
Take review of the ODBC Data Source Settings and then click ok..
Now get back to the original dialog box that asks you to select a Data source.
Now you have a DSN file, so you can continue on and connect to SQL Server.
Ensure that the DSN file's folder is selected next to Look In and the DSN file is entered next to DSN Name, then click OK.
The wizard displays the tables and views from SQL Server so that you can select the ones you require.
Select all table or view that you want to be imported into Access then click ok.
Screen is only displayed if access can't find a unique identifier for a table. If Access is unable to fetch which fields or fields uniquely identify a table or view, you will be prompted to set the unique identifier for that table.
Select fields that uniquely identify each record then click ok. This is important to perform, for each table where the unique identifier can't be identified.
Import process has now been completed. You can either close the wizard or save the import steps so that you can run the same import later without needing to go through all the steps in the wizard.
Close the wizard and click close option. To save the import steps tap to the Save import steps, enter name and description and then click Save Import.
Now the imported tables appear in the left Navigation Pane. The table names are prefixed with the schema name from the Sql Server Database.
Database has now been imported from SQL Server. Now you can do any changes to the database in access without impacting on the original database in SQL sever.
You can now make any change to the database in Access without impacting on the original database in SQL Server.
