Microsoft Access Database – 6 Important Points When Importing Data Into An Access Database
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:
- Inconsistent data
- Too much data (volume)
- Cannot convert to the matching fields
- 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:
- Any database created with Microsoft Jet engine (Access database)
- ODBC (Object Database Connectivity) databases such as Microsoft SQL Server, Oracle and bespoke database applications.
- Excel spreadsheets (any version).
- Lotus spreadsheets (most versions).
- Delimited and fixed length text files (in a tabular format).
- 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 6 basic steps and rules below to help prepare to import data as smoothly as possible:
- 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.
- 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.
- 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.
- 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.
- 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!
- 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.
sql
#Microsoft #Access #Database #Important #Points #Importing #Data #Access #Database
Will be pleased to have you visit my pages on social networking .
Facebook page here.
Twitter account is here.
Linkedin account here
Post byBedewy for info askme VISIT GAHZLY
