Today, Microsoft’s office suite provides several tools with which its users have the ability to perform different tasks professionally. One of these tools is based on the recognized program of databases, called “Microsoft Access” which enables search, order and present any type of information technically.
In addition to it, provides a remarkable level of customization so that each user acquires great productivity when managing their databases. Also, this software offers various elements in order to enrich the files through tables, queries, forms and reports. Therefore, it works as a tool for create databases in an optimal and efficient way.
Among other characteristics, it should be noted that, Access admits the fact link and import data from two different databases, so that users can have a better experience. Therefore, for you to benefit from this excellent peculiarity, below, we will let you know the process to be carried out to use information from one database in another in such a way.
In general, users who want to include information from a database of Access to another, choose to copy and paste the data in question. Since, without a doubt, this is the simplest method for it. However, to gain more control over the data that will be used that way, it is recommended to import and link them. But, before carrying out such a procedure, several aspects must be taken into consideration in order to make use of this method in a correct way.
As a consequence, here we name the parameters of greatest interest to take into account when it is necessary to link the information from two different databases:
- While importing from another database, the program takes care of create a copy of the data in the target database. But, without altering the origin.
- Each user has the power to select the objects you want to copy, as well as controlling how tables and queries are imported, and specifying whether to import relationships between tables, etc.
- Unlike the copy and paste operation, importing the data into Access allows you to translate the relationships between the tables together with the tables themselves. Which is considered a great advantage.
- During an import operation in this software, cannot append records to an existing table.
- Linking basically offers the possibility to connect data from different files without having to import them or without creating two copies of the same data.
- If you decide to link data from a document Access in another, you can keep the table in a single database and link to it from other files. In other words, it will not be necessary to duplicate the table in each database.
- In Microsoft Access, cannot link to queries, reports, forms, modules, or macros. Therefore, you can only create links to tables in another database.
As you will see, procedures for importing and linking data from two different Microsoft Access documents, are of great help for users who normally choose to copy and paste the information without benefiting from these other solutions.
However, with the procedures detailed below, you can use the data from one database in another in a more technical way:
Generally speaking, the import process requires three precise steps which are: Prepare for import, run the Import Wizard Y save the import settings as a specification for future use (the latter is optional).
Let’s see each of them:
To prepare for import:
- First of all, find the source database and identify the information you want to import into another file.
- Then, verifies what type of file the source database has to know what kind of information it accepts. If it’s an .mde or .accde file, you can only import tables. But, if it is a .mdb or .accdb file, you can import tables, queries, reports, forms, modules and macros.
- Now, proceed to shutting down the source database, making sure that no user has it open in exclusive mode. Well, this will prevent the import process from being carried out.
- Then enter the destination database and check that you have the required permissions to add data or objects. Also, you should avoid making the database read-only.
- In case the source database is protected, it is necessary that enter the password when using it as the source of an import in Access.
To import the data:
- Initially, you have to locate the import wizard. For this, go to the options bar and click on the tab “External data” to go to the Import and Link group.
- After that, click on “New data source” and select the option “From a database” for so click on “Access”.
- Once the wizard to import external data opens, in the “File name” text box, Press the button “Examine” to open the corresponding file. Failing that, enter the name of the source database.
- Later, in “Specify how and where you want to store the data in the current database”, you have to choose the first option (“Import tables, queries, forms, reports, macros and modules in the current database”).
- Done the above, click the “Accept” button and the Import Objects dialog box will automatically open. There, in the Tables tab, you must choose the tables you want to import. Further, in the Queries tab, select the queries to import.
- If you want to specify additional settings, you just have to press the “Options >>” button. Whether to import all custom menus and toolbars, relationships between selected tables, all custom navigation pane groups, only fields from selected tables, etc.
- Finally, to finish the operation, click on “OK” and you’re done.
Without importing the information from one database to another, you can connect data from one to another just by linking them. In this way, you will have the power to view and modify the most recent data in both databases. without keeping two copies of the same. Now the process of linking data from a file Access to another from scratch, is based on two general steps which are: Prepare for the link operation and start the link wizard.
Let’s see each one of them:
To prepare for the bind operation:
- As in the previous case, you have to start by looking for the source database. If it is password protected, you will have to enter it to be able to execute the linking operation.
- Second, you need to decide which are the tables you want to link to. Considering that, you cannot create them for forms, reports, queries, macros or modules.
- Subsequently, it checks that no other user has the database open exclusively and proceed to close the source Access document.
- Then you have to open the target database to verify that you have the necessary permissions to add data or objects to it and verify that it is not read-only.
- Directly from the software ribbon, click on the tab “External data” and in the group Import and link, select the option “New data source”.
- Now, in the menu that appears below, click on the option “From a database” and click on “Access”.
- Next, the corresponding wizard will open to import external data and there, you have to proceed to choose the source database file, through the “Browse” button or by typing the name of said file in the text box “File name”.
- After that, select the option “Link to the data source by creating a linked table” in “Specify how and where you want to store the data in the current database”. So, click OK.
- Done the above, the Link Tables dialog will be launched and at that point, it is necessary that choose the tables you need to link to this Access file.
- To conclude the operation, click OK. If you want to make sure the linked tables have the correct data, you can open them in Datasheet view.
If you have any questions, leave them in the comments, we will answer you as soon as possible, and it will surely be of great help to more members of the community. Thank you! 😉