Project 4: Forms & Reports
Import/Export/Link

There are many reasons for importing and exporting data and database objects. Most of them amount to trying to save time and effort! In addition, copying data removes the chance of introducing new data-entry errors when re-entering data.

Linking to data means you don't copy it at all. There is one source for the data. Multiple databases or other programs can use it. Everyone sees the most up-to-date values. (In earlier versions of Access this feature was called attaching.)

Why Import/Export?

• To change programs: Data has already been recorded in a another way, for example in a spreadsheet, and you are upgrading to a database. You want to use the existing data without having to re-enter it.
• To reuse an object: You have a useful/attractive table, query, form, or report design that you want to use in or adapt for another database. You can import it and make whatever changes you want. Tables can be imported with or without their data.
• To freeze data: You want to keep a copy of the data as it was at certain point in time.
• To send data to another program: For example, you can export names and addresses to a word processor for a mail merge document.
• For speed: Access works faster with its own tables than with linked tables.
• For a limited purpose: You are creating a database for a special purpose and want to duplicate parts of the original database.
• To protect sensitive data: You need to share part of a database that includes some sensitive data. You can export just the non-sensitive parts.

Why Link?

• To use current values from another Access database:
  Especially useful for networked databases
• To have separate front-end and back-end databases:
  The back-end database contains the tables. The front-end database contains all of the other objects (queries, forms, reports, etc.) and links to the tables. Different people or different departments can have their own front-end with their own forms and reports without cluttering up the database for others who need different forms and different reports. An Access database can be used as the front-end for a more powerful, but less user-friendly database.
• To be able to update data with another program and also with Access: Note that from inside Access you cannot update or delete linked data in an Excel 2003 worksheet.

Which format is best to use for importing?

Importing into an Access database from another Access database is best, of course! You have the most choices of what to import.

What is next best??

A file in dbf format (with the extension dbf) is very easy to import into Access and importing such a file will bring the indexes along with the table. So, when offered a choice of formats, dbf is a good choice.

How to import dbf file:
  File | Get External Data | Import  - Change file type to one of the dBase formats, select the file, and click Import. You are done!

The dbf format was developed for the program dBase, which was a successful early database program. Many programs use this format and many more can save their tables in one of several dbf formats. It is an excellent choice for exporting/importing data.

Access has wizards to help you with spreadsheets and text database files so importing those formats is not really difficult either.

 Find a home for your data: Importing and exporting the same table back and forth between programs can cause serious problems with the data. Decide where the data is going to live and stick to it!

What format is best to use for exporting?

What program will be importing the data? A format that matches that program would be best. For example, if the data will be opened in Excel, export in that an Excel format. If you don't know which version of Excel will be used, choose the earliest version and try it out with a much later version to see if you lose anything important.

If you don't know what program will be using the data, then a text format like CSV might be the safest format. One of the dBase formats is also a fairly good choice since many programs can import files in those formats.

Be sure you export the field names along with the records!

Problems with Import/Export/Linking

• Desired format not available: Import/Export filters may need to be installed from your installation CD.
• Desired format not allowed: If your data is in a format that is not in the list of file types, first export it to a format that Access recognizes. For example, MS Works data cannot be imported directly into Access. You can export the data from MS Works to a DBF format or to a text format like CSV and then import it into your Access database. You will not be able to link to this kind of source.
• Appending to existing table: Field names and data types must match exactly.
• Field names: First row of imported text or spreadsheet data should be the field names. Be sure that each row in a spreadsheet is one record.
• Subreports will export with the report.
• Subforms will not export with the form.
• Truncated data: The Import Spreadsheet Wizard looks at the first 25 rows to decide how large fields should be. If later rows have fields that are larger, the data will be cut short (i.e. truncated).

No "Save As" for whole database

Sometimes you might like to save the whole database with a new name or location or as a different type of database. There is no Save As command inside Access to do this. Sometimes importing, exporting, and linking can get you what you want, or else you can...

• Access database: Copy and paste the database file in an Explorer/My Computer window. Then you can change its name or location and from inside the new database do any deleting or editing that you wish. Access has no Save As command for the whole database.
• Another type of database: Create a new blank database of the type that you want and then import the data. Access cannot save a database as another kind of database and other programs cannot save their files as an Access database.

We won't try to import/export/link with every format that Access can manage! In the next lessons you will use the most commonly used file formats to get the tables you need to do the lessons on forms and reports.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012