Six Important Points When Importing Data Into An Access Database
By Ben S Beitler
Asking general users to import data into an Access database without knowing too much
about how Microsoft Access works and they will become frustrated within minutes.
The obvious method is to use the Copy [CTRL] + [C] and Paste [CTRL] + [V] commands but
if you have ever tried to use this method, in most cases it fails to import due to:
1. Inconsistent data
2. Too much data (volume)
3. Cannot convert to the matching fields
4. Field mappings are wrong
The most desirable way of using external data is by importing or linking data to an
Access table object. Once the data has been imported or linked, you treat it like any
other data table and build your queries, forms and reports utilising the powerful features of Access.
The main question that needs to be answered before importing or linking data is
Which method to use - Link or Import Data?
You need to know the pros and cons of linking and importing data from an external
database source.
Use the importing method to physically store data in a table which is a far more
efficient way of copying data across applications rather than using the Copy⁄Paste approach.
Use the linking method to create a connection to an external database/data source
without physically bringing data across. This creates a pointer to the external file and
can be viewed as if it were a table.
In Access, there are various import or link formats available some of which are listed below:
1. Any database created with Microsoft Jet engine (Access database)
2. ODBC (Object Database Connectivity) databases such as Microsoft SQL Server, Oracle and bespoke database applications.
3. Excel spreadsheets (any version).
4. Lotus spreadsheets (most versions).
5. Delimited and fixed length text files (in a tabular format).
6. Tabular data in HTML (Hypertext-Markup Language) file including XML.
There are other formats Microsoft Access also supports but the above should satisfy the majority.
Follow the six basic steps and rules below to help prepare to import data as smoothly as possible:
1. Where applicable, decide if you want to have any field (column) headings and if so,
allocate the top row (normally row 1) for your field names. Do not have two or more rows
as a representation for field headings; keep it in one row only.
2. For each field name, make sure it is unique and that you do not have any duplicates. Also,
make sure that you do head a field with a name (as text) and not leave it blank.
3. From row 2 onwards, each row represents a single record and you must ensure that a record sits
entirely in one row and not across two or more rows.
4. Each field (column) must be of the same data type (excluding the field name itself).
If you have a field which is to hold Date data types, then do not mix the values with text or
any other data type. Keep to one data type only. Blank values can be left in and will be transposed
as either an Empty or Null value.
5. Optionally, you could format the field headings slightly differently to the rest of the rows
(which are the records) so that the system uses the format attribute to clearly distinguish
the top row from the rest. A simple bold will do but this is optional (more for the user's benefit)
and it will handle top rows if there are different from the rest!
6. Make sure all the records are as compact as possible and in one region. This means having
no entire blank rows or columns in between the records and fields.
Follow the above rules and importing data will run very smoothly indeed into an Access table.
Another tip for you! If you're not sure how your data will look and want to check and
clean your records as described in this article, first import the into Microsoft Excel and
then manually clean your data. Then save the file as an Excel spreadsheet before using
this file to import or link to Microsoft Access.
I invite you to keep up to date with my articles and eBooks which covers a lot of
details. How about downloading my FREE eBook on "How to Import Data into an Access
Database" application?
Free eBook Offer
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
