Importing Data From Another Database

If you or someone in your office has been using Access, it is possible that the data you need is already in an Access database. It is a simple task to import a table with data from another Access database. The following section discusses how.

1. This is the database window of the Consulting database. In this Simulation, you are going to import the Invoices table from another database (rather than importing the data from a spreadsheet as you did in the last lesson).
2. To begin, choose File>>Get External Data>>Import from the menu.
3. The Import dialog box helps you find the file containing the data you want to import. The first step is to make sure you are looking at the write type of files. You want to import data from an Access database. Access is already selected in the Files of type setting, so you do not need to make any changes. The file that contains the Invoices table is the Invoices.mdb file. Double-click the filename to choose the object to import.
4. The Import Objects dialog box allows you to choose the object you want to import. Notice that for the tabs across the top, you can import any type of object. This can be very useful if you have already created an object similar to what you need in a new database. In this case, however, you just need to import a table. Click the table called Invoices to select it, then click the OK button.
5. The database window now shows the imported table called Invoices. The table has been imported in its entirety with data as well as all field properties.

Keep in mind that saving a file to a different file format is only a small part of the process. As already discussed, there may be issues involving code, references, macros, security, and integration with other applications. For the most part, moving to newer versions is easier than moving backward. When converting to a prior version, some newer features may be lost or have only part of their functionality, and custom features may not be recognized or implemented as expected. Despite those concerns, it is certainly handy to have the ability to save a file in an older format when you want to. But what about times when only some of objects are needed?
Instead of converting an entire database, there is also the option to import database objects into an Access 2010 file, whether you need an MDB or ACCDB format. Importing objects does not automatically import or set the necessary references. So if you import VBA objects that depend on specific references, you may need to manually add the same references to the new file. To convert a database, it must be closed, meaning that no users can be accessing the database, and you essentially need to have the equivalent of Administrator permissions for the database. Fortunately, the default mode for an unsecured database is for all users to have Admin permissions.

When converting a database that contains linked tables, it is a good practice to ensure that the linked tables are still in the location specified in the Connect property. Using the "Linked Table Manager" to relink to the current tables is a fast, easy way to refresh or update the links. After the database has been converted, the tables can be moved and the Linked Table Manager can be used to relink to the tables in their new location.

Importing the data you need is a great idea if it is static data that does not need to be updated. However, if the data you need is updated on a regular basis, you need to consider how the updating will be done in Access. If the old data source is being abandoned in favor of Access, then you do not have an issue, the data can simply be updated in Access. However, if there is a system in place for updating the data in an application other than Access, importing the data might not be the best thing to do. If you import this data, you will have to figure out a way to keep it up-to-date and chances are that will require quite a lot of time on your part. Instead you may want to consider linking to the data source. Linking means that every time Access needs the data it goes out to the other data source and retrieves it, and is therefore always using the most up-to-date data. Linking is not covered in this course, but the process is similar to importing. If you need to link to data, you might find the resource books or the Access Help system helpful.

Aside from being able to import data into Access from the SharePoint entry point, a developer can also import a SharePoint list programmatically using VBA code from within Access. The TransferSharePointList method allows a developer to either import or link to a SharePoint list programmatically. TransferSharePointList is a method of the DoCmd class, which is a member of the Application object. TransferSharePointList takes up to six arguments, three of which are required. The following table describes each of these arguments.

In this exercise, import a table from another database.
Importing Data From Another Database - Exercise