Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Tables > Import/Export/Link
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries:Tables: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 switch 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. For example, a company might have separate databases for sales and for staff info. The sales database could link to the staff database to get the name of salesperson for a sale.
  • 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 also be used as the front-end for a more powerful, but less user-friendly database.
  • To use data from other programs without storing it in Access: If you link to a data source that is not an Access database, for example, an Excel spreadsheet, Access can use the data to create reports and run queries. The downside is that you cannot change the data from inside Access. You must go back to the original source file in its own program to update the data.

Which format is best to use for importing?

Ribbon: External Data > ImportImporting 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?? It's hard to choose! There are buttons on the ribbon to import from Excel, Access, ODBC, Text, and XML. Apparently Access expects to do more importing with these formats. The More button drops a list of other formats: SharePoint List, Data Services, HTML, Outlook and (for Access 2007 and 2010) dBase.

Access has wizards to help you with importing files in the formats that it recognizes.

If the data you want to use is not in one of the formats that Access understands, you will need to export it from the original program into one of those formats. Excel and text file formats are usually available in various programs in their Save As and Export As dialogs. XML is a newer format that may not be an export option in older programs. You will use data from an XML file later in these lessons.

Excel formats are excellent if your data was in a spreadsheet or some kind of flat file database. Formulas do not get imported, just the current values.

About dBase: The program dBase was a successful early database program. Many programs have used this format over the years and many more can save their tables in one of several dbf formats. Importing such a file will bring the indexes along with the tables. But Office 2013 and 2016 programs will not import or export files in dbf formats.

How to import data:

 Ribbon tab: External Data > Click the button for the data type > Navigate to the file

 WarningPick 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 your 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 used to be a fairly good choice since many programs can import files in those formats. But Microsoft dropped support for importing and exporting in dBase formats with Office 2013.

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

Icon: TroubleProblems with Import/Export/Linking

Many issues can cause an import into Access or a link to fail. Some are not obvious when looking at the data. For example, are those numbers being treated as numbers or as text?

  • Access cannot read the original file: Import/Export filters may need to be installed from the installation CD of the original program.
  • Access cannot read the original file: First export the data 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 text format like CSV and then import it into your Access database.
  • Error when appending to existing table: Field names and data types must match exactly. Field size must accept all the data.
  • Field names: Access expects the first row of imported text or spreadsheet data contains the field names.
  • Spreadsheet records into Access: Each row in a spreadsheet must be one record. No blank rows. Formulas will not import into Access tables, only the current value in the cell. Error codes in any cell will cause the cell to show as Null in the Access table. All cells in a column must have the same type of data.
  • Access subreports will export with the report.
  • Access subforms will not export with the form.

  • Truncated data: If an Excel spreadsheet has a cell with more than 255 characters and is linked to an Access database, Access considers the field to be a Memo or Long Text data type but shows only the first 255 characters. You can create a table first with the field size set large enough to hold all the characters and then append the spreadsheet data.

    Icon: Warning Warning: The Import Spreadsheet Wizard looks at the first 25 rows to decide how large fields should be. If later the contents of rows are larger, the data will be cut short (i.e. truncated).

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