March 16, 2015

How to Import Existing Access or SQL Database into Access

SQL Server and Access are undoubtedly two important database services. So import and export data between them are usual. In this passage, we will mainly talk about something about data import, for example, how to import access or sql database into Access.

Part 1: Import existing access database into new Access database

When you are using database in Microsoft Access 2000/2002/2003/2007/2010/2013 etc, you can import tables, queries, and macros from that database into a Microsoft Office Access 2007/2010/2013 database (.accdb). We also can say import data from MDB database file into ACCDB database file.

Step 1: Click the Microsoft Office button, and then click Open.

Step 2: In the Open dialog box, select and open the access database that you want to import the tables, queries, or macros into.

open access database in Access

Step 3: On the External Data tab, in the Import or Import & Link group, click Access.

select external data type access database

Step 4: Click Browse button to specify the source of access database in Get External Data window. Click OK.
specify imported access database location

Step 5: In Import Objects window, tab Tables, Queries, Forms, Reports, Macros and Modules. Select what you want to import into opening access database. Then click OK.

select objects from access database to import

To make additional settings, click Options to specify them.

Step 6: You will instantly get a message "All objects were imported successfully".

import objects from access database into access successfully

Click Close directly or check "Save import steps" to save the process of importing access database into Access database. So you can follow the import steps to import data more quickly when you want to import access database again.

After that, you can open and edit import datasheet now in current access database.

import access database into access successfully

Part 2: Import SQL database into Access

Besides access database, there is no doubt that SQL Server database data also can be imported into Access desktop database or Access apps. Now please see how to import sql database into Access desktop database.

Step 1: Open Access desktop database you want to import SQL database in Microsoft Access.

Step 2: On External Data tab, click ODBC Database.

choose imported database type odbc
Step 3: When you see "Get External Data" wizard, check "Import the source data into a new table in the current database". Click OK.

Step 4: In pop-up Select Data Source window, if you can find .dsn file, select the file in the list. If not, it will be necessary to create a new .dsn file.

select sql data source

Follow introductions show how to create a new DSN file in details.

1. Click New and go to Create New Data Source.  

2. Select "SQL Server" in the driver list and click Next.

create new sql data source

3. Type a name for .dsn file and click Browse to save the file in computer.

save new sql data source file

4. Click Next, review the data source information, then click Finish to complete creation wizard.

confirm sql data source information

Then you can see a new dialog "Create a New Data Source to SQL Server". The "Description" is optional. But we have to set the SQL Server we want to connect to.

create an odbc data source to connect to SQL Server

5. Type the SQL Server or select one from Server drop-down list. Click Next.
6. Confirm the authenticity of the SQL Server login ID.
7. If you want to connect to a specific database, ensure that the Change the default database to check box is selected. Then select the database that you want to work with, and then click Next.
8. Click Finish.
9. Review the summary information and click Test Data Source.
10. Review the test results, and then click OK to close the dialog box.

Step 5: Click OK to close the Select Data Source dialog box.

Step 6: In Import Objects dialog box, tab Tables, and click each table or view what you want to import into Access database. Click OK.

Step 7: If the Select Unique Record Identifier dialog box appears, select the field or combination of fields that is unique for each row, and then click OK. If you are not sure, contact to the administrator of SQL Server database.

Step 8: Click Close under Save Import Steps in the Get External Data - ODBC Database dialog box. Access finishes the import operation and displays the new table or tables in the Navigation Pane.

Or check "Save import steps". If you plan to repeat the import operation later, you can check it and easily rerun the same import steps later.

Related Articles:

No comments:

Post a Comment